How to get the last generated value of an identity column
IDENTITY_VAL_LOCAL()
How to use the function IDENTITY_VAL_LOCAL()
Example:
Creating a table
db2 "CREATE TABLE TABLE_X( COD INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, NAME CHAR(30))"
Inserting data
db2 "insert into table_x (name) values ('Rose')"
db2 'insert into table_x (name) values ('Mary')"
db2 "insert into table_x (name) values ('Alyson')"
getting of last generated value of COD
db2 "select IDENTITY_VAL_LOCAL() as last_cod from sysibm.sysdummy1"
OUTPUT
LAST_COD
---------------------------------
3.
1 record(s) selected.
The result of the function is not affected by the following:
- A single row INSERT statement with a VALUES clause for a table without an identity column
- A multiple row INSERT statement with a VALUES clause
- An INSERT statement with a fullselect
- A ROLLBACK TO SAVEPOINT statement