Results 1 to 3 of 3

Thread: Identity Column

  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: Identity Column

    Hi,
    I have a table with identity column (generated always by default) and a primary key on it. I insert some values in this with default clause and some values after that with manul values.
    But if i try inserting again with default, it starts at the last values generated by default.
    Is there any way to avoid this behaviour apart from alter table option ?
    The scenario with example and comments is mentioned below

    Thanks,
    Raj

    Scenario:
    CREATE TABLE TABB
    (
    USER_VIEW_ID INTEGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1, INCREMENT BY 1, NO CYCLE, MINVALUE 1, MAXVALUE 2147483647, NO CACHE , ORDER ),
    LABEL VARCHAR(256) NOT NULL,
    CONSTRAINT XPKUSER_VIEW
    PRIMARY KEY (USER_VIEW_ID)
    )


    INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(DEFAULT,'TEST1');
    VALUES IDENTITY_VAL_LOCAL();-->1

    INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(3,'TEST1');
    INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(4,'TEST1');
    INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(5,'TEST1');
    INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(6,'TEST1');
    INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(7,'TEST1');
    VALUES IDENTITY_VAL_LOCAL();--7

    INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(DEFAULT,'TEST1');
    VALUES IDENTITY_VAL_LOCAL();--2 ==> I would like to have this start at 8
    INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(DEFAULT,'TEST1');== >FAILS
    VALUES IDENTITY_VAL_LOCAL();--3

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No. If you decide to put your own values in the column, then it is your responsibility to ensure that they do not interfere with any generated values if you let DB2 assign the value.

    I have written a stored procedure that reads the maximum value of the identity column and then resets the value to one value higher. You could do something like that, but DB2 will not automatically do it for you.
    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
    Nov 2007
    Posts
    27
    Thanks for the quick reply

Posting Permissions

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