How to resolve com.ibm.websphere.ce.cm.DuplicateKeyException on an identity column

caused by: com.ibm.websphere.ce.cm.DuplicateKeyException: 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 "USER.VLDN_CHK" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.65.110

Is there a way to find what is the current sequence value, and how do I set it to a different value? I am using IBM DB2.

The table was created as:

CREATE TABLE "USER"."VLDN_CHK" ( 
  "VLDN_CHK_ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY 
     ( START WITH 180000 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 
       NO CYCLE CACHE 20 NO ORDER ), 
 "TYPE_NM" VARCHAR(100) NOT NULL, 
 "RSLT_CD" SMALLINT);

Solution

To check the value that will be assigned next:

SELECT NEXTCACHEFIRSTVALUE
FROM SYSCAT.SEQUENCES
WHERE SEQSCHEMA = 'YOURSCHEMA' AND SEQNAME = 'YOURSEQUENCE'

To set the new value

ALTER SEQUENCE YOURSCHEMA.YOURSEQUENCE RESTART WITH <new value>

Identity columns are also backed by sequences. To determine the next identity value

SELECT NEXTCACHEFIRSTVALUE
FROM SYSCAT.COLIDENTATTRIBUTES 
WHERE TABSCHEMA = 'YOURSCHEMA' AND TABNAME = 'YOURTABLE'

(since there can only be one identity column in a table). To set the new value

ALTER TABLE YOURSCHEMA.YOURTABLE 
  ALTER COLUMN YOURIDENTITYCOL RESTART WITH <new value>