Help me on DB2 INSERT question

[PrakashRS]

Given the table T1, created by:
CREATE TABLE t1
(id INTEGER GENERATED BY DEFAULT AS IDENTITY,
c1 CHAR(3)
)
The following SQL statements are issued:
INSERT INTO t1 VALUES (1, 'ABC')
INSERT INTO t1 VALUES (5, 'DEF')

Can anyone tell what is the value that is being inserted into the ID column by the following statement?
INSERT INTO t1(c1) VALUES ('XYZ')?

I knew the answer for this question. It is 1. But can any one explain this.

[docdiesel]

Hi,

it starts with 1 because you gave no "START WITH" clause. Besides, for GENERATED columns you have two options:

• GENERATED ALWAYS AS IDENTITY - advantage: DB2 will always generate a (correct) value. Disadvantage: You may not insert an id on your own.
• GENERATED BY DEFAULT AS IDENTITY - advantage: You may insert a value yourself. Disadvantage: DB2 and you will never know exactly what the other one is doing and what value to use next.

DB2 won't share the responsibility for checking/inserting the IDENTITY columns: It's all or nothing. But if you need to insert id's separately, perhaps this is a workaround which may help you:

GENERATED BY DEFAULT AS IDENTITY (START WITH 2, INCREMENT BY 2)

Let DB2 insert rows with even ids only, while you concentrate on the odd ones.

Regards, Bernd