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

Did you managed to solve this? If yes please post your answer. — Zo Has, Jun 25 '12 at 07:22
@DamienJoe I'm no longer using iSeries and do not have access to any system that I could use for testing these answers. If you find that one of them works, please comment here and I will mark the answer as accepted. — AJ., Jun 25 '12 at 13:07

Rashmi Pandit · Answer 1 · 2009-07-15T07:03:01.493

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

Yes, I found this on the public site as well and can't get it to work with iSeries. Thanks much for your response. — AJ., Jul 15 '09 at 11:12
FINAL TABLE is supported starting with version 6.1. It is the recommended approach to get determine the identity value used by an insert statement. — Andy Wilson, Nov 11 '11 at 16:51

Vinay Sajip · Answer 2 · 2009-07-15T17:56:44.697

7

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

edited Jul 15 '09 at 17:56

answered Jul 10 '09 at 14:00

Vinay Sajip

95,872
14
179
191

1

In the C# program, I would probably add the following as a second SQL command: "select identity_val_local() from sysibm.sysdummy1" instead of the VALUES INTO statement in the example above. – Tracy Probst Jul 15 '09 at 12:53
That would return the identity field inserted by the latest operation performed on any table & not that specific table. Imagine an environment with many users inserting in many identity column tables ? – Zo Has Nov 15 '10 at 09:17
@Popo: I think the internal implementation would use the database connection as context. Otherwise the function is pretty much useless, since DB2 is not a single-user system! – Vinay Sajip Nov 17 '10 at 16:02
2

The IDENTITY_VAL_LOCAL() is scoped to a "level". Levels are created whenever a stored proc, trigger, or function are invoked, so you don't need to worry about other inserts by other users. – Andy Wilson Nov 11 '11 at 16:48

score 0 · Answer 3 · edited Dec 16 '13 at 13:58

0

Here's an example :

CREATE TABLE AUTOINC (                                       
   AUTO91 INTEGER       GENERATED ALWAYS AS IDENTITY,          
   SCDS91 CHAR(35)      NOT NULL DEFAULT '',                   
   MCLD91 DECIMAL(3,0)  NOT NULL DEFAULT 0,                    
   CONSTRAINT PK_AUTOINC PRIMARY KEY(AUTO91));

// Notice the default keyword where the auto increment field is.

insert into AUTOINC Values( default ,'SYSC' , 0 )

// And using the function to return the last identity column value.

// Note: fetch first row only.

select **IDENTITY_VAL_LOCAL**() from AUTOINC **fetch first row only**

edited Dec 16 '13 at 13:58

Benny Hill

6,191
4
39
59

answered Dec 16 '13 at 13:38

Mark Plumridge

59
4

... This is essentially a duplicate of an answer that was posted here **2 years ago**. What differentiates your answer from that? – Clockwork-Muse Dec 17 '13 at 13:11

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

3 Answers3