Error- A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.

[desanip]

Hello,

I have a job where i am trying to extract 10000 records from oracle and load into teradata. I have enabled the "Use Batch Size=10000" option in Tteradataoutput component. Below is the error i encounter:

java.sql.BatchUpdateException: [Teradata JDBC Driver] [TeraJDBC 15.10.00.14] [Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(ErrorFactory.java:148)
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(ErrorFactory.java:137)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:272)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatch(TDPreparedStatement.java:2584)
at gfc_emm_banalytx_ff.test_0_1.test.tOracleInput_1Process(test.java:4297)
at gfc_emm_banalytx_ff.test_0_1.test.tTeradataConnection_1Process(test.java:1043)
at gfc_emm_banalytx_ff.test_0_1.test.tOracleConnection_1Process(test.java:824)
at gfc_emm_banalytx_ff.test_0_1.test.runJobInTOS(test.java:5063)
at gfc_emm_banalytx_ff.test_0_1.test.main(test.java:4645)
[Message]: [Teradata JDBC Driver] [TeraJDBC 15.10.00.14] [Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.
[ERROR]: gfc_emm_banalytx_ff.test_0_1.test - tTeradataOutput_1 - [Teradata JDBC Driver] [TeraJDBC 15.10.00.14] [Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.
[Message]: [Teradata Database] [TeraJDBC 15.10.00.14] [Error 7454] [SQLState HY000] DateTime field overflow.
[ERROR]: gfc_emm_banalytx_ff.test_0_1.test - tTeradataOutput_1 - [Teradata Database] [TeraJDBC 15.10.00.14] [Error 7454] [SQLState HY000] DateTime field overflow.
[ERROR]: gfc_emm_banalytx_ff.test_0_1.test - tTeradataOutput_1 - [Teradata JDBC Driver] [TeraJDBC 15.10.00.14] [Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.
Caused by: java.sql.SQLException: [Teradata JDBC Driver] [TeraJDBC 15.10.00.14] [Error 1339] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. The parameter set was not executed and should be resubmitted individually using the PreparedStatement executeUpdate method.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:94)
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:64)
at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.handleRunException(PreparedBatchStatementController.java:96)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:145)
at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:58)
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:387)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:252)
... 6 more
java.sql.SQLException: [Teradata Database] [TeraJDBC 15.10.00.14] [Error 7454] [SQLState HY000] DateTime field overflow.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.cloneJDBCException(ErrorFactory.java:170)
at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.handleRunException(PreparedBatchStatementController.java:95)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:145)
at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:58)
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:387)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:252)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatch(TDPreparedStatement.java:2584)
at gfc_emm_banalytx_ff.test_0_1.test.tOracleInput_1Process(test.java:4297)
at gfc_emm_banalytx_ff.test_0_1.test.tTeradataConnection_1Process(test.java:1043)
at gfc_emm_banalytx_ff.test_0_1.test.tOracleConnection_1Process(test.java:824)
at gfc_emm_banalytx_ff.test_0_1.test.runJobInTOS(test.java:5063)
at gfc_emm_banalytx_ff.test_0_1.test.main(test.java:4645)
[Teradata JDBC Driver] [TeraJDBC 15.10.00.14] [Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.
[statistics] disconnected
Job test ended at 14:22 02/10/2018. [exit code=0]

Can someone help me understand how to troubleshoot this error? 

Thank you.

[Anonymous]

Hello,

This issue is due to huge data which is being processed in batch mode. The batch request fails intermittently.

Please decrease the batch size of tTeradataOutput component to lesser value. For ex: the records were being rejected when the 'batch size' is set to '10000', please change it to '5000' to see if the records will be loaded to Teradata correctly?

Best regards

Sabrina

[Anonymous]

It worked for me. Thanks !

[Anonymous]

Hi,

    Try to use the FastLoad, MultiLoad and TPT components available in Talend to increase the data loading efficiency to the target Teradata Tables. But you have to discuss first with Teradata DBAs to make sure that necessary underlying DB resources are allocated for your user profile.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved