Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2925
  • Last Modified:

How can I retrieve current identity value in DB2 ?

There is Built-in function for each DBMS
which is for retrieving current identity value of some table.
for example...

in oracle...

select table_name.curval from dual

in ms-sql ...

select IDENT_CURRENT('table_name')

how can I do this in DB2 ?
Is there anyway?
  • 2
1 Solution
To obtain the last value generated, use the SYSIBM.IDENTITY_VAL_LOCAL funtion:


This won't yield the expected value in multi-threaded applications where multiple threads perform Inserts. Address this situation by serializing threads.

IDENTITY_VAL_LOCAL returns NULL if either a COMMIT or other queries were performed after the Insert (even if those queries did not reference the Identity column).
ignungAuthor Commented:
Yes, I already know that function ...
but as you see...  It's result is not guarantee...
and my application is extremely multi-threaded ;-)
I should find some other way...
Anyway... I really appreciate your answer.
Since the identity column will be incremented for each row inserted, maybe you can try using max() function on the identity column to get the latest value.

SELECT max(identity_column) from table_name

Not sure if that will work.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now