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');

2 thoughts on “Using MAXASSIGNEDVAL from SYSIBM.SYSSEQUENCES (IBM DB2 for Z OS)”

  1. 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?

  2. @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 a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.