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