How to Determine the Last Value for an Identity Column after an Insert Statement

Resolving The Problem

There is a way to retrieve the last value for an identity column using the IDENTITY_VAL_LOCAL function.  It is documented at:

https://www.ibm.com/docs/en/i/7.5?topic=functions-identity-val-local

IDENTITY_VAL_LOCAL is a non-deterministic function that returns the most recently assigned value for an identity column.
After executing an insert statement, you can run the following statement from the same session to determine what the last value is for the identity column:

SELECT IDENTITY_VAL_LOCAL() AS id

FROM SYSIBM/SYSDUMMY1

Note: Reguardless of your file name with the identicy column, for this function use SYSIBM/SYSDUMMY1

In addition to the above, you could also run a query similar to the following for a rough estimate of the next value.

select NEXT_IDENTITY_VALUE
from QSYS2/SYSPSTAT
where TABSCHEMA = 'LIBRARY'
and TABNAME ='FILE'

Note: This won't work if your library is in QTEMP.  You also need to list your LIBRARY and FILE in all upper case.

This is the next value after the cache is consumed. In certain situations, such as system failure, all cached identity column values that have not been used in committed statements are lost,
and thus, will never be used. The value specified for the CACHE option is the maximum number of identity column values that could be lost in these situations.

Note that DSPFFD of the file will also show detail for the identity column:

  Identity column information:                                                  
   GENERATED . . . . . . . . . . . . . . . :  ALWAYS                            
   Original START WITH . . . . . . . . . . :                                1  
   Current START WITH  . . . . . . . . . . :                                1  
   INCREMENT BY  . . . . . . . . . . . . . :                                1  
   MINVALUE  . . . . . . . . . . . . . . . :                                1  
   MAXVALUE  . . . . . . . . . . . . . . . :                       2147483647  



This is the another way to know what the current maximum value is in the identity column.

SELECT MAX(IDENTITY_VAL_LOCAL()) AS id FROM USERLIB/ID

Then by adding the increment value, you will know the next value that will be inserted.  Assuming the numbers are tracking correction.

You can reset the value by using ALTER TABLE

ALTER TABLE ORDER         
  ALTER COLUMN ORDERNO         
  RESTART WITH 1

This often needs done if you are using HA solutons, or your applications may be over-riding the generated value.