By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,651 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

Help me on DB2 INSERT question

P: 1
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.
Oct 5 '07 #1
Share this Question
Share on Google+
1 Reply


docdiesel
Expert 100+
P: 297
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
Oct 7 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.