SQL0803N SQLSTATE=23505 Error on table with IDENTITY column

SQL0803N SQLSTATE=23505 or SQLCODE=-803 SQLSTATE=23505 error in DB2 LUW indicates you are trying to Insert, Update row which violates Primary Key, Unique Constraint or Unique index on a table. This error on a table for which Primary Key column is IDENTIY column with GENERATED BY DEFAULT or GENERATED ALWAYS seems strange, since DB2 itself take care of generating unique value for IDENTITY column. You might get SQL0803N error when running insert from CLI and in application error log you might get see error SQLCODE=-803 SQLSTATE=23505.

This issue happens when IDENTITY column generate value which is already present in table. You can observer this anomaly in below scenarios: 

Scenario 1: After performing Load with IDENTITYOVERRIDE modifier.

When you load a table with IDENTITYOVERRIDE modifier and max value for IDENTITY column in load file is more than max value for IDENTITY column in target table, you might face this issue when new inserts will be performed after load. This happens as DB2 generates IDENTITY value which is already present in table since internal sequence for IDENITY was not updated after load.  

Scenario 2: Table altered to add IDENTITY clause which already has data.

If you alter a table to add IDENTITY clause which already has data with default option, after load during insert you might face this issue. In this case also  DB2 generates IDENTITY value which is already present in table since internal sequence for IDENTITY will generate value from 1 by default which might already be present in table.

Solution:

To fix this issue you need to restart the internal sequence of IDENTITY column with value greater then sum of max value and CACHE value of IDENTITY column.

In this example I am referring to EMP table with below structure

CREATE TABLE "DB2INST2"."EMP"  ( "ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +9223372036854775807 NO CYCLE  CACHE 20 NO ORDER ) ,  "NAME" VARCHAR(20 OCTETS) );

Check max value for IDENTITY column

db2 "SELECT MAX(ID) FROM EMP"

Calculate restart value for internal sequence, in this case since 125357 is max value for ID column and CACHE value is 20, we will use restart value as 125380.

ALTER TABLE EMP ALTER COLUMN  ID  RESTART WITH 125380

After restart of internal sequence, insert will complete.

If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.