I have a field a field "GENERATED ALWAYS AS IDENTITY", after INSERT a record on the table, the IDENTITY field gets assigned to internal sequence number for the table properly.
During the same session, I'd like to recover the last IDENTITY generated during the same session, how do I do it?
I'm using ILE RPG, i5 V7R1 for the INSERT.
The reason I'd like to recover the last IDENTITY field, is because I have two tables, Header and Detail, first, I insert the record into the header table, next thing, I'll insert the record into a detail table, but referencing the header id table.
It is possible that the same program will be called multiple times at once, that is why I want to retrieve the last ID from the last insert in the session.
Pinned topic Recover last IDENTITY after an INSERT in DB2 for i.
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-04-09T18:58:38Z at 2012-04-09T18:58:38Z by krmilligan
B.Hauser 1000007U1D299 Posts
Re: Recover last IDENTITY after an INSERT in DB2 for i.2012-04-06T16:45:09ZThis is the accepted answer. This is the accepted answer.The scalar function Identity_Val_Local will return the last identity inserted within the same unit of work:
Exec SQL Insert into MyFile
Exec SQL Set :MyVar = Identity_Val_Local();
jmerinoh 270001MKKU2 Posts
Re: Recover last IDENTITY after an INSERT in DB2 for i.2012-04-06T16:49:13ZThis is the accepted answer. This is the accepted answer.
- B.Hauser 1000007U1D
I also found that a quick way to test it within STRSQL after the INSERT is:
SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM/SYSDUMMY1
SELECT max(IDENTITY_VAL_LOCAL()) FROM TBL_BATCHPROCESSLOGHEADER
krmilligan 120000MDWP450 Posts
Re: Recover last IDENTITY after an INSERT in DB2 for i.2012-04-09T18:58:38ZThis is the accepted answer. This is the accepted answer.
- jmerinoh 270001MKKU
SELECT order_id FROM FINAL TABLE (
INSERT INTO orders VALUES(DEFAULT,'11/03/2007',50,'JM12'));