DB2 equivelant of MySQL's mysql_insert_id() C function

Fumigator · #1 (**permalink**) 07-03-07, 11:56

When a key is defined auto-increment in MySQL, you can grab the newly inserted ID just after an insert query via the mysql_insert_id() C function.

With DB2's "GENERATED ALWAYS" keyword on a column, is there a way to grab a newly inserted ID other than a select query on the row I just inserted?

(DB2 V.8 I believe, on zO/S)

stolze · #2 (**permalink**) 07-03-07, 14:42

You can use the IDENTITY_VAL_LOCAL function or just select from the insert:

Code:

SELECT ...
FROM NEW TABLE ( INSERT ... ) AS t

p.s: You should be sure about the platform your are working on. The correct answer may depend on that because not all features are available no all DB2 flavors yet. Running "db2 connect" on the command line tells you the server's platform.

Fumigator · #3 (**permalink**) 07-03-07, 18:10

It's V.8 for sure... CICS/COBOL environment. Thanks for the answer. A search of that function in the DB2 docs I found this, which worked a treat:

Code:

EXEC SQL
    VALUES IDENTITY_VAL_LOCAL() INTO :IVAR;
END-EXEC

Internet Services

Web Development

Digital Marketing

Consumer Tech