DB2: SQL Error: -803, SQLState: 23505, Unique Constraint Violation

By | December 8, 2019

Yet another thread of DB2 errors from an EJB application that connects to a DB2 instance. The reported thread of errors:

[7/13/16 12:46:38:670 IST] 00000098 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions [jcc][t4][102][10040][4.19.26] Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null

[7/13/16 12:46:38:669 IST] 00000098 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions SQL Error: -4229, SQLState: null

[7/13/16 12:46:38:670 IST] 00000098 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions SQL Error: -803, SQLState: 23505 …

[7/13/16 12:46:38:673 IST] 00000098 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions Error for batch element #1: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by “1” constrains table “ACHINST1.MESSAGEKEY” from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=4.19.26

We get several DB2 error codes there that may make it more clear. Let me take you through them :

1. SQL Error: -4229, SQLState: null
Translated: “Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. ”

Note: This is the top error at batch level so we have to go deeper to find the cause.

2. SQL Error: -803, SQLState: 23505
Translated: “Error for batch element #1: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by “1” constrains table “INST1.MESSAGEKEY” from having duplicate values for the index key..”

Note: This informs us that an element of the transaction batch failed. The cause is clearly indicated as a “unique constraint or unique index” violation on the table “INST1.MESSAGEKEY”.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.