View previous topic :: View next topic |
Author |
Message |
videlord Beginner
Joined: 09 Dec 2004 Posts: 147 Topics: 19
|
Posted: Wed Jan 12, 2005 8:59 pm Post subject: Gereated by default as identify columns after load |
|
|
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 |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
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 |
|
Back to top |
|
|
videlord Beginner
Joined: 09 Dec 2004 Posts: 147 Topics: 19
|
Posted: Fri Jan 14, 2005 12:56 am Post subject: |
|
|
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. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Fri Jan 14, 2005 10:12 am Post subject: |
|
|
videlord,
Try this
Code: |
INSERT INTO TA(SELECT CHAR('AAA'),MAX(ID) + 1 FROM TA);
|
Hope this helps...
Cheers
kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
coolman Intermediate
Joined: 03 Jan 2003 Posts: 283 Topics: 27 Location: US
|
Posted: Tue Jan 18, 2005 12:50 pm Post subject: |
|
|
Kolusu ,
Shouldn't your query be modified as follows?
Code: |
INSERT INTO TA(SELECT MAX(ID) + 1, CHAR('AAA') FROM TA);
|
________
Ford Model U specifications
Last edited by coolman on Sat Feb 05, 2011 1:39 am; edited 1 time in total |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
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 |
|
Back to top |
|
|
videlord Beginner
Joined: 09 Dec 2004 Posts: 147 Topics: 19
|
Posted: Thu Jan 20, 2005 3:18 am Post subject: |
|
|
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'). |
|
Back to top |
|
|
|
|