Filed Under: DB2

Using MAXASSIGNEDVAL from SYSIBM.SYSSEQUENCES (IBM DB2 for Z OS)

SYSIBM.SYSSEQUENCES has a column called MAXASSIGNEDVAL, that gets assigned whenever the values for IDENTITY/SEQUENCE columns are getting generated (at least for the cache).

SELECT MAXASSIGNEDVAL
FROM SYSIBM.SYSSEQUENCES
WHERE SEQUENCEID =
(SELECT BSEQUENCEID FROM SYSIBM.SYSSEQUENCESDEP
WHERE DCREATOR = 'USER1'
AND DNAME = 'EMP_TABLE'
AND DCOLNAME = 'EMP_ID');

Related Posts

There's 2 entries So Far

  • harvey puckett
    August 31st, 2010 at 10:05 am

    ok, so the column is only populated if you CACHE(n) where n>0?

    what use is the column? unlike RESTARTWITH which must be user maintained since the LOAD utility does not update it?

    seems like IBM is just not customer friendly, or production ready on IDENTITY columns … comments?

  • Karthik
    August 31st, 2010 at 1:18 pm

    @harvey, yes, I think this is populated only for CACHEd identity columns. I couldn’t find any other way to get the last generated value. (did not want to use MAX() ).

Leave your Feedback / Question here. Thanks!