Db2: How to create IDENTITY columns

Db2 supports the IDENTITY keyword to create autoincremental columns. Several options are available.

Defining a basic IDENTITY column

Db2 IDENTITY columns are integer columns. Their types can be:

  • SMALLINT (2 bytes)
  • INT (4 bytes)
  • BIGINT (8 bytes)

Autoincremental values can be:

  • GENERATED ALWAYS AS IDENTITY: The user cannot force a value.
  • GENERATED BY DEFAULT AS IDENTITY: A value is only generated when the user doesn’t specify one.

An example:

CREATE TABLE user (
    id INT GENERATED ALWAYS AS IDENTITY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
)

Specifying sequence options

It is possible to specify options that affect the generated values. These are almost the same options that can be specified for a sequence:

  • START WITH: The first value that will be generated (default: 1).
  • INCREMENT BY: The increment to be added to generate the next value (default: 1).
  • MINVALUE: Values may cycle after reaching the maximum value. In this case, the MINVALUE determines the value to be generated after reaching the maximum. NO MINVALUE to disable.
  • MAXVALUE: The maximum allowed value. NO MAXVALUE to disable.
  • CYCLE or NO CYCLE: Whether the values may cycle or not when MAXVALUE is reached.
  • CACHE: How many next values should be kept into the cache to speed up insertions. To disable, specify NO CACHE. Default: CACHE 20.
    • NOTE: If insertions can be made concurrently, using a cache may cause values to be created in a non-chronological order.
  • ORDER or NO ORDER: Whether values should be generated in order. Default: ORDER.

The spaces after NO are optional. You can specify NOMINVALUE, NOMAXVALUE, NOCACHE, and NOORDER.

An example:

CREATE TABLE user (
    id INT GENERATED ALWAYS AS IDENTITY (
        START WITH 10000
        MAXVALUE 99999
        NO CYCLE
    ),
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
)

Ascending IDENTITY columns

It is possible to specify a negative INCREMENT. In this case the first generated value will is defined by START WITH or MAXVALUE. If a MINVALUE is specified, no lower values can be generated. If the values may cycle, the next generated value is determined by MAXVALUE.

Example:

id INT GENERATED ALWAYS AS IDENTITY (
    START WITH 50
    INCREMENT -2
    MAXVALUE 100
    MINVALUE 1
    CYCLE
),

In this case, the first generated value is 50. The next is 48, then 46, etc. The lowest allowed value is 1, but in practice the lowest possible value is 2. The next generated value will be 100.

Holes in IDENTITY columns

It is entirely possible to have missing numbers, or “holes”, in a IDENTITY column. This most likely happens because some transactions failed, or were explicitly rolled back.

IDENTITY column close to its MAXVALUE: what to do

IDENTITY columns are normally used as primary keys. If a IDENTITY column is close to its limit, we need to think how to generate new values.

However, because of the nature of IDENTITY columns (they are sequences associated to a column), it is not possible to tell them to generate new values to fill the holes when needed.

If a IDENTITY column has the CYCLE property, we can periodically delete the oldest values, so that they will be re-generated when needed.

If we cannot delete oldest values, we can change the data type of the IDENTITY column. It’s very uncommon to need more values than those allowed by a BIGINT column.

If BIGINT is not big enough, we can use two IDENTITY columns and build the primary key on both the columns:

id1 INT GENERATED ALWAYS AS IDENTITY,
id2 INT GENERATED ALWAYS AS IDENTITY,
PRIMARY KEY (id1, id2)

If the table is periodically completely emptied, we should use TRUNCATE TABLE to empty it. In this way the table will be dropped and recreated from scratch, so its IDENTITY columns will be reset.

Reference

More articles:

Db2 documentation: