Identity Column in DB2

In order to maintain uniqueness in the values entered in db2, we use the UNIQUE constraint for one of the column in db 2. Creation of  unique index, primary key are also there in the list by which we can maintain data uniqueness. However there are methods to generate unique values in the columns.

1. Creating IDENTITY column while defining the table (introduced in DB2  version 7.1 and later)
2. SEQUENCE (from DB2 version  7.2 onward)

Lets check about IDENTITY column in this post.
Identity columns offer us the possibility to guarantee uniqueness of a column and to be able to automatically generate unique value. An identity column is a Numeric column, either SMALLINT, INTEGER, or DECIMAL with a scale of zero, or a user defined distinct type based on any of these data types, which is UNIQUE and NOT NULL by definition.
As such there are no rules when to use identity columns. Still we can consider the below scenario when we can use IDENTITY column.

When can we use IDENTITY COLUMN
Suppose we have  a multi-column primary key(composite key) and the table has several dependent tables, you have to ‘copy’ many columns to the dependent tables to build the foreign keys. This makes the keys very long. Having many columns in the index also makes the index grow quite large. Instead of using the long key as the primary and foreign key, you can use an artificial unique identifier for the parent table and use that generated value as a primary key and foreign key for the dependent table.
Another use for identity columns is when you just need a generated unique column for a table. If we do not need to reference the rows by this column, then there is no need to even create an index for it, and uniqueness is guaranteed by the system for generated values

The Identity Columns can be used in table in two ways :
    1. The value for identity column is always generated by the DB 2.(GENERATED ALWAYS keyword in the column declaration ; see below for syntax)
    2. The value is inserted explicitly by user. And if used don't specify any value then the value is generated by the DB2.(GENERATED BY DEFAULT)

Syntax :

CREATE TABLE <table_name>

(

    <column1> datatype

    GENERATED ALWAYS/GENERATED BY DEFAULT

    AS IDENTITY

    (

    START WITH <numeric constant>,

    INCREMENT BY <numeric constant>,

    NOMINVALUE / MINVALUE <integer constant>,

    NOMAXVALUE / MAXVALUE <integer constant> 

    NOCYCLE / CYCLE,

    NOCACHE / CACHE <integer constant>,

    NOORDER / ORDER

    ),

    <column2> datatype,

    <column3> datatype,

    ...............................

    ...............................

)

Running a query to create Identity column:

CREATE TABLE PROD_DEAL

( ROW_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY 

                         (START WITH 1, INCREMENT BY 1, NO CACHE), 

 ORDER_NO CHAR(6), 

 INV_COUNT INT WITH DEFAULT 0 

);

Suppose we have run the INSERT QUERY 5 times for five different ORDER_NO.

INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00001',11);
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00002',22);
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00003',33);
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00004',44);
INSERT INTO PROD_DEAL(ORDER_NO,INV_COUNT) VALUES('A00005',55);

Output:
ROW_ID   ORDER_NO  INV_COUNT
------------------------------------------------
11000001  A00001         11
11000002  A00002         22
11000003  A00003         33
11000004  A00004         44
11000005  A00005         55

We can see the ROW_Id generating unique values each time the insert query ran.