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



Answers

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
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




Related



Tags