Hi,

I have a table that has one id column that is unique, primary key, identifier... And counts 1 every time data is inserted into it!

Now I want to create a procedure that takes care of inserting data into the table and some other tables that are related to this table.
Of course, then I want to retreive the ID from the first column from the INSERT.. how do I do it ?

Use the parameter IDENTITY_VAL_LOCAL.

What OS and DB2 version are you using?

If UDB V7.2 or greater, then look into Identity_Val_Local().

Andy

Hi guys,

thanks for your quick response!

I use Windows XP and the latest version of DB2 (8,2?!) which I downloaded from the partner site at ibm.com a couple of days ago.

I will look into IDENTITY_VAL_LOCAL()

Can this variable retreive the value of the row that I just inserted?

That is its sole purpose in life. Just be sure to use it right after the insert and before any commit.

Andy

Thanks man, you're the hero of the day!

Another question: Is the IDENTITY_VAL_LOCAL() always returning the id of the row that i inserted in my query? Or is it possible that I insert a row, and another person inserts a row a millisecond after and when I use the IDENTITY_VAL_LOCAL() I get the id that was created for the user that created the 2:nd row?

It works only within the session it was invoked in. Only the value created in the session it is called in will be returned. Read the manual, it explains all this and more.

Andy