Re: Getting the value of an Identity field after record creation

On 2/23/06, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:
> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstscale.htm#scaidentity
>  CREATE TABLE EMPLOYEE
>    (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
>     NAME CHAR(30),
>     SALARY DECIMAL(5,2),
>     DEPT SMALLINT)
>
>  INSERT INTO EMPLOYEE
>    (NAME, SALARY, DEPT)      // Who is going to report the typo to IBM?
> They put DEPTNO in the manual.
>    VALUES('Rupert', 989.99, 50)
>
>  VALUES IDENTITY_VAL_LOCAL() INTO  :IVAR
>
> Now, maybe this is your question; I tried this simple program and I am
> having a tough time.
>     Femployeex UF A E             DISK    rename(employeex:employeer)
>      /include routines/qrpglesrc,psds
>      /free
>       name='test';
>       salary=1;
>       dept=5;
>       write employeer;
>       *inlr=*on;
>      /end-free
> None of my current fields in the psds show it.  Anyone have a suggestion?
>
> However, this program works fine, and shows the right value:
>     Femployeex UF A E             DISK    rename(employeex:employeer)
>     D ivar            s             10i 0
>      /free
>       name='test';
>       salary=1;
>       dept=5;
>       write employeer;
>       *inlr=*on;
>       exec sql VALUES IDENTITY_VAL_LOCAL() INTO  :IVAR;
>       dsply ivar;
>      /end-free

I like that. thanks.

not sure if identity_val_local( ) will return the most recent identity
system wide for the table or just your job.  My guess is it is system
wide, so you are not assured the identity_val_local( ) will actually
be the row your code just inserted.

here is an example that uses "begin atomic" to maybe syncronize the
two operations:
   CREATE TRIGGER NEW_HIRE
     AFTER INSERT ON EMPLOYEE
     REFERENCING NEW AS NEW_EMP
     FOR EACH ROW
     BEGIN ATOMIC
       INSERT INTO EMP_ACT (EMPNO)
       VALUES (NEW_EMP.EMPNO);
       INSERT INTO ACCT_LOG (ACNT_NUM EMPNO)
       VALUES (IDENTITY_VAL_LOCAL(), NEW_EMP.EMPNO);
     END

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0004231.htm