iSeries DB2 - Is there any way to select the identity value from an insert statement?

Question

I know we're rare, us poor folk that are using iSeries for DB2/AS400, but I'm hoping someone can answer this simple question. Is there any way to return the identity value from an insert statement without using two lines of SQL? I'm being forced to use inline SQL in C# to perform an insert, and then I need to use the identity generated for the insert for something later on. Simply put, I need the iSeries DB2 equivalent of Oracle's "RETURNING." I.e.,

INSERT INTO AwesomeTable (column1, column2, etc.)
    VALUES (value1, value2, etc.)
    RETURNING something;

Anyone? Thanks in advance.

EDIT: Unless someone knows of a way I can execute two lines of SQL in one IBM.Data.DB2.iSeries.iDB2Command (not a stored proc), I would like to do this all in one line of SQL

Answer 1

You need to use the IDENTITY_VAL_LOCAL scalar function. From the IBM documentation:

IDENTITY_VAL_LOCAL is a non-deterministic function that returns the most recently assigned value for an identity column.

Example:

CREATE TABLE EMPLOYEE
    (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
     NAME CHAR(30),
     SALARY DECIMAL(5,2),
     DEPT SMALLINT)

INSERT INTO EMPLOYEE
    (NAME, SALARY, DEPTNO)
    VALUES('Rupert', 989.99, 50)

SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1

Answer 2

I am not sure of iSeries, but the following worked on db2v8.1:

Consider 'ID' is the name of your identity column. The following stmt will return the newly generated id (the same one that gets inserted by the insert stmt):

SELECT ID FROM FINAL TABLE (
    INSERT INTO AwesomeTable (column1, column2, etc.)
         VALUES (value1, value2, etc.)    
    )

Some explanation I found on the publib site: (I used it for reference to test my query above)

     /* The following SELECT statement references an INSERT statement in its
           FROM clause.  It inserts an employee record from host variables into
           table company_b.  The current employee ID from the cursor is selected
           into the host variable new_id.  The keywords FROM FINAL TABLE
           determine that the value in new_id is the value of ID after the
           INSERT statement is complete.

           Note that the ID column in table company_b is generated and without
           the SELECT statement an additional query would have to be made in
           order to retreive the employee's ID number.
        */
        EXEC SQL SELECT ID INTO :new_id
                 FROM FINAL TABLE(INSERT INTO company_b
                 VALUES(default, :name, :department, :job, :years, :salary, 
                        :benefits, :id));

Hope this helps :)