MVSFORUMS.com

videlord · Beginner Joined: 09 Dec 2004 Posts: 147 Topics: 19

One column (for example COL1) in a table defined as GENERATED BY DEFAULT AS IDENTIY.

If using DB2 Load Utility load some data (COL1 with value, not generated by DB2) into the table, then intert record to the table, DB2 will generate a wrong value of COL1.

We have to select the max (COL1), and then ALTER table, change COL1 start with max(COL1) +n.
Is there an easy way to solve it?
Is it possible do it after LOAD without coding some program (only SQL is OK, no host language)?

Thanks

kolusu · Posted: Thu Jan 13, 2005 6:14 am Post subject:

videlord,

Can you please explain clearly as to what you are trying to do ? I can't relate a LOAD and an ALTER in your requirement.

A sample input and desired output will help.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

videlord · Beginner Joined: 09 Dec 2004 Posts: 147 Topics: 19

kolusu,

for example,
Table DDL:
Create table TA
(ID integer gererated as default start with 1 increment by 1,
name char(10)
);

create unique index ixa on table TA (ID);

There is a data file:
0001abcdefghij
0002abcdefghij

after loaded, u can't insert any record with SQL INSERT statement:
INSERT into table ta (DEFAULT,'aaa');

that's the problem.

kolusu · Posted: Fri Jan 14, 2005 10:12 am Post subject:

videlord,

Try this

coolman · Posted: Tue Jan 18, 2005 12:50 pm Post subject:

Kolusu ,

Shouldn't your query be modified as follows?

kolusu · Posted: Tue Jan 18, 2005 1:04 pm Post subject:

Coolman,

You are right. I failed to read the table declaration. Sorry about that !

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

videlord · Beginner Joined: 09 Dec 2004 Posts: 147 Topics: 19

Yes, this can insert.

But our objective is to let the value generated by DB2. We hope the SQL didn't changed (INSERT into table xxx (DEFAULT, 'xxx').