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.
