A procedure I have in my notes ... This is not my own ... I found it in a newsgroup posting ...
You cannot create an identity column per-se, however you can use a trigger to
achieve the desired behavior.
First: Create the "identity" column with a DEFAULT and best a unique constraint.
Second: Create a counter function using a scratch pad. You'll find this function
Third: Create a before insert for each row trigger on the table that fetches the
current maximum of the identity column and adds an offset using the counter
If you have any other tables where you want to use the generated value, you can
do this in
the trigger body (BEGIN ATOMIC SET new.identity = .....; <do_more_stuff>; END.
Fourth: For isolation level you can experiment with cursor stability and higher.
E.g. you can handle conflicts in your application and reapply the insert or you
a higher isolation level up to shared table lock if you want DB2 to handle it.
You should commit ASAP after the insert to free up the locks.
Note: If you only do single row inserts you don't need the scratch pad function.
Example (found in this newsgroup):
CREATE FUNCTION NULLID.COUNTER() RETURNS INT EXTERNAL NAME
'DB2Udf!ctr' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NO SQL NOT
DETERMINISTIC NOT FENCED SCRATCHPAD NO EXTERNAL ACTION;
CREATE TRIGGER NULLID.ADDRESSAUTOINC NO CASCADE BEFORE INSERT ON
NULLID.ADDRESSES REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
SET (n.ADDR_UID ) = (SELECT value(MAX(ADDR_UID),0) +NULLID.COUNTER() FROM
Originally posted by Neiva
Yes, I know , but how can I make it? With a trigger ? Cretaing a sequence in the programs?