Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to constraint sequence number in generated identity columns without missing value

    Hi,
    using DB2 v9.5 fixpack 2a on Linux I have created a table with "generated always as identity" column.

    Code:
    CREATE TABLE ADMIN.TAB
    (
    ID INT NOT NULL GENERATED ALWAYS AS IDENTITY
       (START WITH 1,
       INCREMENT BY 1,
       NO CACHE),
    COL2 INT NOT NULL UNIQUE
    );
    Above table also has a unique value in col2, to easily demonstrate the problem.
    Code:
    INSERT INTO ADMIN.TAB (COL2) VALUES (1);
    INSERT INTO ADMIN.TAB (COL2) VALUES (1);
    INSERT INTO ADMIN.TAB (COL2) VALUES (2);
    Second insert fails because of unique constraint.

    Looking at the table with select:
    Code:
    SELECT * FROM ADMIN.TAB;
    and I see in first ID column the following numbers: 1 and 3.
    Code:
    ID          COL2
    ----------- -----------
              1           1
              3           2
    I have expected to see 1 and 2 value, but 2 is missing and 3 is inserted. How to prevent missing values (like 2) in ID column?
    Why does DB2 increases sequence number if SQL fails?

    Thanks
    Last edited by grofaty; 11-09-11 at 09:51.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 does not rollback increments to sequences or identity columns (which are implemented with sequences behind the scenes) when an associated SQL fails. This is for performance reasons, since DB2 does not want to hold a lock on that sequence for the duration of your transaction. You would have to implement your own "next_available_number" table to have it fall under transaction control.
    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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    As Marcus mentioned, rolled back transactions may leave gaps. It can also occur that identity values are cached in bigger chunks and that not all numbers in a chunk are used. That could also cause gaps.

    What's the problem with the gap? If you can leave with it, it would be the best
    approach. If you have a hard requirement (i.e. legal requirements) that no gaps must exist, you can't use sequences and identity columns.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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