An identity column contains a unique numeric value of each row in the table. Db2®can automatically generate sequential numeric values for this column as rows is inserted into the table. Thus, identity columns is ideal for primary key values, such as employee numbers or product numbers.
You can define a identity column as either GENERATED by DEFAULT or GENERATED always:
- If you define the column as GENERATED by DEFAULT, you can insert a value, and DB2 provides a DEFAULT value if you do not s Upply one.
- If you define the column as GENERATED always, DB2 always generates a value for the column, and you cannot insert data into That column. If you want the values to being unique, you must define the identity column with GENERATED always and NO CYCLE and define a U Nique index on the that column.
Suppose that table T1 are defined with GENERATED always and CYCLE:
CREATE TABLET1 (CHARCOL1CHAR(1), IDENTCOL1SMALLINTGENERATED always as IDENTITY(START with -1, INCREMENT by 1, CYCLE, MINVALUE-3, MAXVALUE3));
INSERT into VALUES ('A');
CHARCOL1 IDENTCOL1======== =========A-1A0A1A2A3A-3A-2A-1
The following example adds a column to the table DSN8910. DEPT, which contains a location code for the department. The column name is Location_code, and it data type is CHAR (4).
ALTER TABLE DSN8910. DEPT ADDCHAR (4);
Add hidden column needs to add keywords, refer to here:
Http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_ altertable.dita?lang=en
DB2 Add Hidden Identity columns