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

grofaty · #1 (**permalink**) 11-09-11, 08:40

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

Marcus_A · #2 (**permalink**) 11-09-11, 09:08

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.

stolze · #3 (**permalink**) 11-09-11, 12:34

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech