Topic
  • 3 replies
  • Latest Post - ‏2012-04-09T18:58:38Z by krmilligan
jmerinoh
jmerinoh
2 Posts

Pinned topic Recover last IDENTITY after an INSERT in DB2 for i.

‏2012-04-06T16:16:32Z |
Hello all,

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.

Any ideas?
Thanks.

Jorge Merino
Updated on 2012-04-09T18:58:38Z at 2012-04-09T18:58:38Z by krmilligan
  • B.Hauser
    B.Hauser
    296 Posts

    Re: Recover last IDENTITY after an INSERT in DB2 for i.

    ‏2012-04-06T16:45:09Z  
    The scalar function Identity_Val_Local will return the last identity inserted within the same unit of work:

    Exec SQL Insert into MyFile
    Values(....);

    Exec SQL Set :MyVar = Identity_Val_Local();

    Birgitta
  • jmerinoh
    jmerinoh
    2 Posts

    Re: Recover last IDENTITY after an INSERT in DB2 for i.

    ‏2012-04-06T16:49:13Z  
    • B.Hauser
    • ‏2012-04-06T16:45:09Z
    The scalar function Identity_Val_Local will return the last identity inserted within the same unit of work:

    Exec SQL Insert into MyFile
    Values(....);

    Exec SQL Set :MyVar = Identity_Val_Local();

    Birgitta
    Thanks Birgitta.

    I also found that a quick way to test it within STRSQL after the INSERT is:

    SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM/SYSDUMMY1
    Or
    SELECT max(IDENTITY_VAL_LOCAL()) FROM TBL_BATCHPROCESSLOGHEADER

    Thanks.
  • krmilligan
    krmilligan
    450 Posts

    Re: Recover last IDENTITY after an INSERT in DB2 for i.

    ‏2012-04-09T18:58:38Z  
    • jmerinoh
    • ‏2012-04-06T16:49:13Z
    Thanks Birgitta.

    I also found that a quick way to test it within STRSQL after the INSERT is:

    SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM/SYSDUMMY1
    Or
    SELECT max(IDENTITY_VAL_LOCAL()) FROM TBL_BATCHPROCESSLOGHEADER

    Thanks.
    You can use the SELECT FROM INSERT support that was added in the IBM i 6.1 release:

    SELECT order_id FROM FINAL TABLE (
    INSERT INTO orders VALUES(DEFAULT,'11/03/2007',50,'JM12'));