Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    1,636
    Provided Answers: 1

    Unanswered: How to prevent gaps in "generated always as identy" columns if error is returned?

    Hi,
    using DB2 v10.1 fixpack 1 on Linux/Intel.

    I created table:
    Code:
    CREATE TABLE ADMIN.TAB (
    ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
    UNIQ INT NOT NULL UNIQUE
    );
    Inserting value into table:
    Code:
    INSERT INTO ADMIN.TAB (UNIQ) VALUES (1);
    Inserting the same value to simulate error - duplicate in unique defined column
    Code:
    INSERT INTO ADMIN.TAB (UNIQ) VALUES (1);
    Inserting another value with success:
    Code:
    INSERT INTO ADMIN.TAB (UNIQ) VALUES (2);
    Selecting the result of table:
    Code:
    SELECT * FROM ADMIN.TAB;
    What I see is the result:
    Code:
    ID          UNIQ
    ----------- -----------
              1           1
              3           2
    The result displays that sequence in ID column just continues to the next value.
    I have specified "NO CACHE", I thought this will not produce gaps between IDs.

    Is there a way to specify if error appears sequence should not get increased like after above inserts ID producing no gaps in numbers.

    Code:
    ID          UNIQ
    ----------- -----------
              1           1
              2           2
    Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    The answer is no. I think that is a FAQ.

  3. #3
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Often wondered on questions like this. Why does it matter???

  4. #4
    Join Date
    Jan 2003
    Posts
    1,636
    Provided Answers: 1
    @DNance, in many cases it is law restrictions. In a lot of cases I am not allowed to have gaps in numbering, because it is difficult to prove something has not been deleted because of gaps.

    As it looks like to solve this problem the are two solutions (both requiring to delete 'generated always' definitions):
    1. Create separate table to have counter saved in table and then create a program to manage the numbering.
    2. Using SQL, but make sure isolation is properly implemented to avoid gaps (until first transaction is finished second should not get the counter at all - it may affect performance).

    For second solution:
    Code:
    CREATE TABLE ADMIN.TAB (
    ID INTEGER NOT NULL PRIMARY KEY,
    UNIQ INT NOT NULL UNIQUE
    );
    
    INSERT INTO ADMIN.TAB SELECT (SELECT COALESCE(MAX(ID + 1),1) FROM ADMIN.TAB), 1 FROM SYSIBM.SYSDUMMY1;
    -- following SQL produces error because of duplicate unique value in UNIQ column
    INSERT INTO ADMIN.TAB SELECT (SELECT COALESCE(MAX(ID + 1),1) FROM ADMIN.TAB), 1 FROM SYSIBM.SYSDUMMY1;
    INSERT INTO ADMIN.TAB SELECT (SELECT COALESCE(MAX(ID + 1),1) FROM ADMIN.TAB), 2 FROM SYSIBM.SYSDUMMY1;
    
    SELECT * FROM ADMIN.TAB;
    And correct result:
    Code:
    ID          UNIQ
    ----------- -----------
              1           1
              2           2

  5. #5
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    The ability to enforce uniqueness on a column with the help of database-manager tooling (such as identity columns) is a different requirement from gapless sequential values in a column. Different applications use different techniques to satisfy the latter requirement of gapless sequences of column values , as there's often a need to cater for application-layer rollbacks in addition to DB2 level transaction rollback.

Posting Permissions

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