Identity_val_local()

Hi,
I have a strange problem with IDENTITY_VAL_LOCAL().I am using DB2 UDB Express 8.2 on Windows 2003. Sometimes when I want to insert a values in a table give me the error 'You are attempting to add an entry that already exists. ' - seem that IDENTITY is not incremented by 1.

Which can be the cause ? the column is declared GENERATED BY DEFAULT AS IDENTITY( START WITH 1, INCREMENT BY 1, NO CYCLE,MINVALUE 1,MAXVALUE 2147483647, CACHE 20 )

Thanks

Not sure, but here is example DDL from one of my tables:

(ITEM_NBR BIGINT NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE, MINVALUE 0, MAXVALUE 9223372036854775807, NO CYCLE, NO ORDER),

You aren't trying to actually supply a value when you insert, are you? DB2 should automatically provide that for you.

identity_val_local() is used to get the ID of the row you JUST inserted.

fv

I have the same syntax like you. Can be the problem that I am importing (moving) the database from another. I heard that in this situation it is possible that IDENTITY_VAL_LOCAL() to lose the order and is possible to give you a value which exist. Is this true?