Restart value of an identity column

yvanroy · #1 (**permalink**) 09-14-09, 22:37

I am running DB2 Version 9.1 fp5 on windows. I would like to know if there is a catalog table containing the restart value of an identity column just following an alter/restart statement and before a subsequent insert.

I usually retrieve this value from NEXTCACHEFIRSTVALUE column of SYSCAT.COLIDENTATTRIBUTE but it looks like it is not available following the alter/restart. DB2LOOK does not generate the alter statement to restart the column either. Following an insert, the value becomes available again in NEXTCACHEFIRSTVALUE and DB2LOOK also generates it.

Thanks for your help in advance

Initial setup:

Code:

CREATE TABLE YVAN.TEST ( ID INTEGER  NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) ,TS TIMESTAMP  NOT NULL  WITH DEFAULT )
DB20000I  The SQL command completed successfully.

INSERT INTO YVAN.TEST (TS) VALUES('2009-01-01-00.00.00.000000')
DB20000I  The SQL command completed successfully.
INSERT INTO YVAN.TEST (TS) VALUES('2009-01-02-00.00.00.000000')
DB20000I  The SQL command completed successfully.
INSERT INTO YVAN.TEST (TS) VALUES('2009-01-03-00.00.00.000000')
DB20000I  The SQL command completed successfully.

SELECT * FROM YVAN.TEST
ID             TS                        
----------- --------------------------
              1 2009-01-01-00.00.00.000000 
              2 2009-01-02-00.00.00.000000          
              3 2009-01-03-00.00.00.000000  
3 record(s) selected.

SELECT NEXTCACHEFIRSTVALUE FROM SYSCAT.COLIDENTATTRIBUTES WHERE TABSCHEMA = 'YVAN' AND TABNAME = 'TEST'
NEXTCACHEFIRSTVALUE
---------------------------------
                               4. 
 1 record(s) selected.

Alter:

Code:

ALTER TABLE YVAN.TEST ALTER COLUMN ID RESTART WITH 1000
DB20000I  The SQL command completed successfully.

SELECT NEXTCACHEFIRSTVALUE FROM SYSCAT.COLIDENTATTRIBUTES WHERE TABSCHEMA = 'YVAN' AND TABNAME = 'TEST'
NEXTCACHEFIRSTVALUE
---------------------------------
                               1.
  1 record(s) selected.

INSERT INTO YVAN.TEST (TS) VALUES('2009-01-03-00.00.00.000000')
DB20000I  The SQL command completed successfully.

SELECT * FROM YVAN.TEST
ID             TS                        
----------- --------------------------
              1 2009-01-01-00.00.00.000000
              2 2009-01-02-00.00.00.000000
              3 2009-01-03-00.00.00.000000
           1000 2009-01-03-00.00.00.000000 
 4 record(s) selected.

SELECT NEXTCACHEFIRSTVALUE FROM SYSCAT.COLIDENTATTRIBUTES WHERE TABSCHEMA = 'YVAN' AND TABNAME = 'TEST'
NEXTCACHEFIRSTVALUE              
---------------------------------
                            1001.
  1 record(s) selected.

Marcus_A · #2 (**permalink**) 09-15-09, 03:43

Try Fixpack 4 (or 4a). I think I saw an APAR that has been fixed on indentity columns using db2look.

yvanroy · #3 (**permalink**) 09-20-09, 09:27

You are correct Marcus_A. DB2LOOK has been modified to fix this behavior in version 9.5 fp4 (according to the release notes). Unfortunately, I can't upgrade just yet.

Thanks for your help.

Internet Services

Web Development

Digital Marketing

Consumer Tech