errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

Database
Fan

errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

when i use jdbc connection Teradata(12.0), i get errors "[TeraJDBC 13.00.00.16] [Error 5966] [SQLState HY000]"

my Teradata is 12.0 and jdbc version is 13.00.00.16.

in jdbc i ues addBatch() and executeBatch().

my record counts about 60000.

i use 3 or 2 or 1 thread insert these records into the same table,everying is ok.

but i use 4 thread insert these records into the same table(15000/thread) . i get flow exception:
aused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [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.

thanks.

19 REPLIES
Teradata Employee

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

I answered your question about Teradata Database error 5966 in the earlier thread. The answer is that you need to reduce the number of rows of data that your application binds per batch, and *** use more batches ***.

I did not expect you to use multiple threads. Attempting to insert rows into the same table with multiple threads is often counter-productive.

If you use multiple threads with a single connection, the inserts will be serialized, and you won't have gained anything.

If you use a separate connection for each thread (multiple connections, in other words) to insert into the same table, you are very likely to encounter rowhash lock contention, and possibly deadlock.

You application should look like the following:

for ( /* the number of batches */ )
{
for ( /* the number of rows per batch */ )
{
pstmt.setX(1, ...);
pstmt.setX(2, ...);
pstmt.setX(...);
pstmt.addBatch();
}
pstmt.executeBatch();
}
Enthusiast

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

Hello,

Tomnolan,

I have the same problem. In my case I used 1000 records per batch and 15 threads.

After 25 million records was migrated - this exception began to appear more and more frequently.

I tried to reduce number of batch to 100 or increase number of batch to 10000 and reduce number of thread to 1 – it helped me for a short time.

But now I got this exception even for 50 records per batch.

So, the question – could it be some restrictions of scheme, session or connection (which was reinforced by DBA)?

Or because this table has 28 million records already.

and point me out on these settings/parameters.

Thanks,

Evgeny

Enthusiast

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

So, I am not sure that pstmt.addBatch(); will help me, especially if I will have to add batch just with 1 records :)

Enthusiast

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

Forgot mention exception:

Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.10.00.17] [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:93) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:63) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.handleRunException(PreparedBatchStatementController.java:95) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:129) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:57) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:381) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:233) ~[terajdbc4.jar:14.10.00.17]

                ... 60 common frames omitted

Also I’ve found explanation of this exception at http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html

Table 19: BatchUpdateException Handling

“When the SQLException getErrorCode method returns a non-negative error code, the parameter set must be resubmitted individually using the PreparedStatement executeUpdate method.”

But there is not any explanation  a reason of this exception.

I'll very appreciate any help.

Teradata Employee

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

>>> this exception began to appear more and more frequently

Which exception exactly?

Enthusiast

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.10.00.17] [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:93) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:63) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.handleRunException(PreparedBatchStatementController.java:95) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:129) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:57) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:381) ~[terajdbc4.jar:14.10.00.17]

                at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:233) ~[terajdbc4.jar:14.10.00.17]

                ... 60 common frames omitted

Teradata Employee

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

A SQL error such as uniqueness violation can get worse over the insert of more rows if there are duplicates in the source data. The first rows will all go in fine but as you get further along, more uniqueness violations will occur and need to be dealt with. Look to see what the underlying SQL error is that is causing the batch to be rejected.

Teradata Employee

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

The Error 1339 is a generic error code that indicates a problem occured. Your application needs to walk the SQLException chain to obtain the root-cause exception. (This is what Todd Walter was suggesting when he said "Look to see what the underlying SQL error is".)

Change your application and add code similar to the following:

catch (BatchUpdateException exCaught)
{
    // walk the exception chain and print all chained exceptions
    for (SQLException ex = exCaught ; ex != null ; ex = ex.getNextException())
        ex.printStackTrace () ;

    // do whatever other exception handling your application needs
    // ...
Enthusiast

Re: errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

Thanks, it is interesting explanation but I've created new empty table in ordetr to upload records to this temporary table and use "insert from select" after but anyway have the same exception for even first batch (1000 records). :(