DB2 duplicate key error when inserting, BUT working after select count(*)

Question

I have a - for me unknown - issue and I don't know what's the logic/cause behind it. When I try to insert a record in a table I get a DB2 error saying:

[SQL0803] Duplicate key value specified: A unique index or unique constraint *N in *N
exists over one or more columns of table TABLEXXX in SCHEMAYYY. The operation cannot 
be performed because one or more values would have produced a duplicate key in 
the unique index or constraint.

Which is a quite clear message to me. But actually there would be no duplicate key if I inserted my new record seeing what records are already in there. When I do a SELECT COUNT(*) from SCHEMAYYY.TABLEXXX and then try to insert the record it works flawlessly.

How can it be that when performing the SELECT COUNT(*) I can suddenly insert the records? Is there some sort of index associated with it which might give issues because it is out of sync? I didn't design the data model, so I don't have deep knowledge of the system yet.

The original DB2 SQL is:

--  Generate SQL 
--  Version:                    V6R1M0 080215 
--  Generated on:               19/12/12 10:28:39 
--  Relational Database:        S656C89D 
--  Standards Option:           DB2 for i 
CREATE TABLE TZVDB.PRODUCTCOSTS ( 
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY ( 
START WITH 1 INCREMENT BY 1 
MINVALUE 1 MAXVALUE 2147483647 
NO CYCLE NO ORDER 
CACHE 20 ) 
, 
PRODUCT_ID INTEGER DEFAULT NULL , 
STARTPRICE DECIMAL(7, 2) DEFAULT NULL , 
FROMDATE TIMESTAMP DEFAULT NULL , 
TILLDATE TIMESTAMP DEFAULT NULL , 
CONSTRAINT TZVDB.PRODUCTCOSTS_PK PRIMARY KEY( ID ) ) ; 

ALTER TABLE TZVDB.PRODUCTCOSTS 
ADD CONSTRAINT TZVDB.PRODCSTS_PRDCT_FK 
FOREIGN KEY( PRODUCT_ID ) 
REFERENCES TZVDB.PRODUCT ( ID ) 
ON DELETE RESTRICT 
ON UPDATE NO ACTION;

Answer 1

I'd like to see the statements...but since this question is a year old...I won't old my breath.

I'm thinking the problem may be the GENERATED BY DEFAULT

And instead of passing NULL for the identity column, you're accidentally passing zero or some other duplicate value the first time around.

Either always pass NULL, pass a non-duplicate value or switch to GENERATED ALWAYS