Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    15

    Question Unanswered: Restart value of an identity column

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try Fixpack 4 (or 4a). I think I saw an APAR that has been fixed on indentity columns using db2look.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2002
    Posts
    15
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •