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 Comments
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?
@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() ).