DB2 Add hidden Identity columns

An identity column contains a unique numeric value for each row in the table. DB2® can automatically generate sequential numeric values for this column as rows are inserted into the table. Thus, identity columns are ideal for primary key values, such as employee numbers or product numbers.

 

You can define an 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 supply 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 be unique, you must define the identity column with GENERATED ALWAYS and NO CYCLE and define a unique index on that column.

Suppose that table T1 is defined with GENERATED ALWAYS and CYCLE:

CREATE TABLE T1
  (CHARCOL1 CHAR(1),
   IDENTCOL1 SMALLINT GENERATED ALWAYS AS IDENTITY
     (START WITH -1,
      INCREMENT BY 1,
      CYCLE,
      MINVALUE -3,
      MAXVALUE 3));

INSERT INTO T1 (CHARCOL1) VALUES ('A');

CHARCOL1  IDENTCOL1
========  =========
A                -1
A                 0
A                 1
A                 2
A                 3
A                -3
A                -2
A                -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 its data type is CHAR (4).

ALTER TABLE DSN8910.DEPT
  ADD LOCATION_CODE CHAR (4);

Add hidden column 需要加关键字，参考这里：

http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_altertable.dita?lang=en