There are 3ways to know the current value of the identity column generated value.
1. db2 "select identity_val_local() from sysibm.sysdummy1" or db2 "VALUES identity_val_local() "
the above assumes you are in the same session that incremented the identity value of the table.
2.
db2 "Select NEXTCACHEFIRSTVALUE from SYSCAT.COLIDENTATTRIBUTES
where TABSCHEMA='ORDB' and TABNAME='XINGTEST' "
( this gives you the next value available in memory cache that will be used for next identity value).
3– Extract the ddl of the table using db2look /
db2look -d NT018007 -e -nofed -t XINGTEST
CREATE TABLE "ORDB "."XINGTEST" (
"ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
STARTWITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
"NAME" VARCHAR(50) )
IN "STAGING" ;
ALTER TABLE "ORDB
"."XINGTEST" ALTER COLUMN "ID"RESTART WITH 5;
Identity values are always incremented , they don’t get decremented. There might be unused value or gaps in between if a value was available in cache but not used and database got recycled in between.
