Here are ideas on how to identity-column on a table. Identity column on a new table An identity column provides a way for DB2 to automatically generate a unique numeric value for each row that is added to the table.
When creating a table where you know that you need to uniquely identify each row that will be added to the table, you can add an identity column to the table. To guarantee a unique numeric value for each row that is added to a table, you should define a unique index on the identity column or declare it a primary key.
Restrictions
- Once created, you cannot alter the table description to include an identity column.
- If rows are inserted into a table with explicit identity column values specified, the next internally generated value is not updated, and may conflict with existing values in the table. Duplicate values will generate an error message if the uniqueness of the values in the identity column is being enforced by a primary-key or a unique index that has been defined on the identity column.
Procedure
It is the AS IDENTITY clause on the CREATE TABLE statement that allows for the specification of the identity column.
The following is an example of defining an identity column on the CREATE TABLE statement:
CREATE TABLE table (col1 INT,
col2 DOUBLE,
col3 INT NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 100, INCREMENT BY 5))
In this example the third column is the identity column. You can also specify the value used in the column to uniquely identify each row when added. Here the first row entered has the value of “100” placed in the column; every subsequent row added to the table has the associated value increased by five.- Some additional example uses of an identity column are an order number, an employee number, a stock number, or an incident number. The values for an identity column can be generated by DB2: ALWAYS or BY DEFAULT.
- An identity column defined as GENERATED ALWAYS is given values that are always generated by DB2. Applications are not allowed to provide an explicit value. An identity column defined as GENERATED BY DEFAULT gives applications a way to explicitly provide a value for the identity column. If the application does not provide a value, then DB2 will generate one. Since the application controls the value, DB2 cannot guarantee the uniqueness of the value. The GENERATED BY DEFAULT clause is meant for use for data propagation where the intent is to copy the contents of an existing table; or, for the unload and reloading of a table
Srinimf 