We are trying to find a way using DB2 7.1 to create a unique sequence column in a table. One of the criteria is that this sequence column will be the key of one table and a foreign key of another. DB2 has an identity column feature which allows you to create a table with a column that automatically generates the next sequence for an inserted row.
CREATE TABLE JUNK
( ROW_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE),
JUNK_DSC CHAR(6)
);
Upon inserting into this table, the ROW_ID column would automatically be populated with the next sequence number. Our processing requirements then involves immediately retrieving that ROW_ID back to populate another table (foreign key insert). We are going to perform both table inserts in a stored procedure that is called by a DB2 trigger on another table.
My question involves immediately retrieving that ROW_ID back to populate the second table (foreign key insert). DB2 has an IDENTITY_VAL_LOCAL function that allows you to retrieve the "latest" value inserted into the table. Does anybody know if this is specific to a given transaction? Or if two inserts into the JUNK table occurred nearly simultaneously, could the IDENTITY_VAL_LOCAL function return an erroneous result for one of them?
CREATE TABLE JUNK
( ROW_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE),
JUNK_DSC CHAR(6)
);
Upon inserting into this table, the ROW_ID column would automatically be populated with the next sequence number. Our processing requirements then involves immediately retrieving that ROW_ID back to populate another table (foreign key insert). We are going to perform both table inserts in a stored procedure that is called by a DB2 trigger on another table.
My question involves immediately retrieving that ROW_ID back to populate the second table (foreign key insert). DB2 has an IDENTITY_VAL_LOCAL function that allows you to retrieve the "latest" value inserted into the table. Does anybody know if this is specific to a given transaction? Or if two inserts into the JUNK table occurred nearly simultaneously, could the IDENTITY_VAL_LOCAL function return an erroneous result for one of them?
