When designing DB2 databases, a frequent user requirement is for a column that contains sequentially generated numbers. Every new row added to the table requires a new value—one greater than the previous value—to be generated. These numbers might be used as a key or simply to differentiate data rows.
DB2 provides identity columns (V6 refresh) and sequence objects (V8) to meet this need. Without such features, an application program can implement similar functionality, but usually not in a manner that can perform and scale properly.
An identity column is defined using the IDENTITY parameter and will cause DB2 to automatically generate a unique, sequential value for that column when a row is added to the table. When inserting data into a table that uses an identity column, DB2 automatically generates the appropriate value to be inserted.
Only one identity column can be defined per DB2 table, and the data type of the column must be SMALLINT, INTEGER, or DECIMAL with a zero scale (or a user-defined type based on one of those data types). You have control over the starting point for the generated sequential values and the number by which the count is incremented:
CREATE TABLE EXAMPLE
(ID_COL INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
START WITH 100 INCREMENT BY 10 ...
Here the identity column is named ID_COL. The first value stored in the column will be 100 and subsequent INSERTs will add 10 to the last value. Therefore, the identity column values generated will be 100, 110, 120, 130, and so on.
To retrieve the value of an identity column immediately after it is inserted, use the IDENTITY_VAL_LOCAL() function. However, this will work only after a singleton INSERT. You cannot use INSERT INTO SELECT FROM or LOAD, if you need to rely on this function.