02 October 2009
5 DB2 Tutorial - Making the primary key auto incremental in DB2
DB2 Tutorial - DB2 Tip - Making the primary key auto incremental in db2
How to make a field auto incremental in DB2?
The following query example will show you how to set a field as Auto-increment field in DB2 create table WCSADM.itemlist
(
ITEMLIST_INDEX Integer GENERATED ALWAYS AS IDENTITY,
RESULTCODE VARCHAR(254),
ADDLINFO VARCHAR (254)
)
(
ITEMLIST_INDEX Integer GENERATED ALWAYS AS IDENTITY,
RESULTCODE VARCHAR(254),
ADDLINFO VARCHAR (254)
)
Subscribe to:
Post Comments (Atom)
5 comments:
how to insert values for GENERATED ALWAYS AS IDENTITY
pls tell me quickly i need this
try it-
insert into WCSADM.itemlist (resultcode,addlinfo) values('dscsq','vfdw');
When inserting into a GENERATED ALWAYS identity column, DB2 will always generate a value for the column, and users must not specify a value at insertion time. If a GENERATED ALWAYS identity column is listed in the column-list of the INSERT statement, with a non-DEFAULT value in the VALUES clause, an error occurs (SQLSTATE 428C9).
For example, assuming that EMPID is defined as an identity column that is GENERATED ALWAYS, then the command:
INSERT INTO T2 (EMPID, EMPNAME, EMPADDR)
VALUES (:hv_valid_emp_id, :hv_name, :hv_addr)
will result in an error.
When inserting into a GENERATED BY DEFAULT column, DB2 will allow an actual value for the column to be specified within the VALUES clause, or from a subselect. However, when a value is specified in the VALUES clause, DB2 does not perform any verification of the value. In order to guarantee uniqueness of the values, a unique index on the identity column must be created.
When inserting into a table with a GENERATED BY DEFAULT identity column, without specifying a column list, the VALUES clause can specify the DEFAULT keyword to represent the value for the identity column. DB2 will generate the value for the identity column.
INSERT INTO T2 (EMPID, EMPNAME, EMPADDR)
VALUES (DEFAULT, :hv_name, :hv_addr)
In this example, EMPID is defined as an identity column, and thus the value inserted into this column is generated by DB2.
urther Reference: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000970.htm
Please give your valuable comments.