0

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);
mustaccio
  • 18,234
  • 16
  • 48
  • 57
panaroma
  • 1
  • 1
  • 3

1 Answers1

0

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>
mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • i should have mentioned this but there is no sequence name for the particular table . the table was created as follow 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); – panaroma Apr 20 '16 at 14:32
  • Just reminds you of the benefit of asking a question properly, doesn't it. – mustaccio Apr 20 '16 at 15:01