This identifier is a number that is updated sequentially as new records are added. In DB2, this feature will automatically generate the values. The values for an identity column can be generated by the Db2 database manager in two ways using the keywords ALWAYS or BY DEFAULT. When using GENRATED ALWAYS keyword, applications are not allowed to specify any value. GENERATED BY DEFAULT gives the application to specify a value; if not provided DB2 will generate the value. The following example shows a table definition that causes the EMPNO field to be generated Automatically as a sequence.
CREATE TABLE EMP
(EMPNO INT GENERATED ALWAYS AS IDENTITY,
NAME CHAR(10));
By default, the numbering will start at 1 and be incremented by 1. We can change the starting and increment values as part of the column definition. Example:
CREATE TABLE EMP
(EMPNO INT GENERATED ALWAYS AS
IDENTITY(START WITH 100, INCREMENT BY 10)),
NAME CHAR(10));
CYCLE or NO CYCLE option for Identify column: CYCLE Option restarts the counting when the value specified between the STARTWITH and MAXVALUE (if ascending) or MINVALUE (If descending) has reached.
When to go for identity column: There is a need for unique column in a table but no need to reference the rows of the table by this column, identity column can be an option. As a result there is no need to create an index & uniqueness is guaranteed by the system for generated values.
Also when there is a table with composite key and it has many dependencies, you have to ‘copy’ multiple columns in the dependent tables to keep the parent child relationship. This can make the index space grow unnecessarily. Index key can be an option to handle such scenarios efficiently.
Define unique index on the identity column or declare it a primary key.
Leave a comment