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
The @@Identity function will return the last identity value inserted in the current session, in any table and in any scope. The Scope_Identity() function will return the last identity value inserted in the current scope (and session), in any table.
An identity column contains a unique numeric value for each row in the table. Whether you can insert data into an identity column and how that data gets inserted depends on how the column is defined.
By default, it's not possible to manually insert a value directly into an identity column value, but identity values can be manually entered if you turn on a session option.
We use the IDENT_CURRENT function to return the last IDENTITY value generated for a specified table under any connection. It does not consider the scope of the SQL query that generates identity value. We need to specify the table for which we want to check the identity value.
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 :)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With