Identity column is defined as
generated always prevents the
overriding of values in an SQL statement database container. an identity
column is a numeric column which automatically generates a unique
numeric value for each inserted row. we can only be one identity coloumn
per table. Identity columns are ideally suited to the task of generating
unique primary key values. Applications can use identity columns to
avoid the concurrency and performance problems that can result when an
application generates its own unique counter outside of the database
Two way to generate value for an
identity column depending on how it was defined:
Generated always:
Values are always generated by DB2 application are not allowed to
provide an explicit value.
Generated by default:
Values can be explicitly provided by an application if no value is given
then db2 generate one, this option is indented for data propagation and
for the unloading of a table.
Example:
CREATE TABLE subscriber(
subscriberID INTEGER GENERATED ALWAYS AS
IDENTITY (START WITH 1000 INCREMENT BY 1000),
firstname VARCHAR(40),
lastname VARCHAR(40) )
In the example, the column subscriberID
is an INTEGER defined as an identity column
that is always generated. The value generated will start from 1000, and
it will be
incremented by 1000.
Sequence object
Sequence is user defined object that
generate a sequence of numeric values is accordingly to the
specification with which the sequence was created. the sequence of
numeric values is generated in a ascending and descending order
Applications refer to a sequence object to get its current value or the
next value. The relationship between sequences and tables is controlled
by the application not by DB2
Though sequence objects are independent
of tables, they are mentioned in this section because they work in a
similar way as identity columns. The difference is that sequence objects
generate unique number s across the database, while identity columns
generate unique numbers within a table.
Example
CREATE TABLE t1 (salary int)
CREATE SEQUENCE myseq
START WITH 10
INCREMENT BY 1
NO CYCLE
INSERT INTO t1 VALUES (nextval for myseq)
INSERT INTO t1 VALUES (nextval for myseq)
INSERT INTO t1 VALUES (nextval for myseq)
SELECT * FROM t1
SALARY
-----------
10
11
12
3 record(s) selected.
SELECT prevval for myseq FROM sysibm.sysdummy1
1
-----------
12
1 record(s) selected