February 22nd, 2005, 11:04 AM
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 )
February 22nd, 2005, 04:18 PM
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.
...because that is the way we have always done it. We've been doing it like that for 80 Years! (How do we change that mindset?)
February 23rd, 2005, 01:54 PM
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?