Db2_ automatically generated values

Two ways to generate a sequence of numbers

In DB2, you can generate a sequence of numbers automatically in two ways:

• Defines a column with an IDENTITY attribute.
• Creates a SEQUENCE object.

IDENTITY column

When you define a table's column with the IDENTITY property, a value is automatically generated for that column whenever a row is inserted into the table.

SEQUENCE Object

The second way to let DB2 automatically generate a sequence of numbers is to create a SEQUENCE object. You can use sequence expressions (sequence expression) to refer to a Sequence object. A sequence expression can appear in most places where an expression can occur. A sequence expression can specify whether the value to be returned is a newly generated value or a previously generated value.

Make a selection between identities and sequences

Although both the identity and the sequence are used to generate values, you may want to use one instead of the other depending on the particular case.

The identity column is useful in the following situations:

• Only one column in the table requires auto-generated values
• Each row requires a separate value
• Using an auto generator to generate a table's primary key
• The process that generates the new value is closely linked to the insert operation on the table, regardless of how the insert operation occurred

A sequence object is useful in the following situations:

• To store values generated from a sequence into multiple tables
• Each table has multiple columns that need to be automatically generated (you may generate multiple values for each row by using the same sequence or multiple sequences)
• The process that generated the new value is not related to any reference to the table

Unlike a sequence object, an identity column is defined on a table and therefore requires some restrictions. There can be at most one identity column per table. When you create a column as an identity column, you must use the exact numeric data type for that column. Because the IDENTITY property is row livings to a value, which is similar to the DEFAULT clause, you cannot specify a clause when defining an identity column DEFAULT . An identity column is implicitly defined NOT NULL .

1. Create an order table using the IDENTITY column

CREATE TABLE customer_orders_t (   order_id           INT not nullgenerated       always as IDENTITY 1                  1                1                no MAXVALUE no        CYCLE       no CACHE       ORDER),   order_date         DATE is not NULL,   cust_id int is not            null,   product_id int is not         null,   quantity           int is not NULL,   Price              DECIMAL (10,2)         is not NULL,   status             CHAR (9) is not         null,primary KEY (order_date, order_id)); INSERT into customer_orders_t         VALUES    (        DEFAULT, current DATE,    ' PENDING ')

2. SEQUENCE objects

CREATE SEQUENCE orders_seq         As INT           1            1            1            no MAXVALUE no    CYCLE   no CACHE   ORDER;= = = ==============NEXT value for orders_seq=================PREVIOUS value for Orders_seq

Http://www.ibm.com/developerworks/cn/data/library/techarticles/0302fielding/0302fielding.html

Db2_ automatically generated values