How can I retrieve current identity value in DB2 ?

Posted on 2003-03-27
Medium Priority
Last Modified: 2012-06-21
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?
Question by:ignung
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 8221893
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).

Author Comment

ID: 8223490
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.

Accepted Solution

yongsing earned 200 total points
ID: 8223514
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question