i have a table which primary key is an integer column with generated by default property.

i now want to move data from the table to a new one, but the problem is if i just perform insert and select data from the old one, the primary key of the new table would different from the old table.

can i create the new table with primary key is integer at first, and then change its type from integer to identity column after inserting data from the old table?

my db2 version is LUW v9.5 express-c, thanks in advance!

better you can load data into the new table using the LOAD command with an IDENTITYOVERRIDE option.... faster and easier...

Check this link out -
DB2 Universal Database

i would but i couldn't, as i'm not the dba of the old database, all i can do is just export each table to an ixf file and then load into the new dabase one by one.

i've tried to use the statement stated in ibm's manual as below,

alter table ppm.table1 alter column col1 set GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE );

but it failed and returned database not support, but i get this statement from ibm's sql manual for v9.5.

any idea?

If you have a GENERATED BY DEFAULT clause, you can insert whichever values you like - DB2 won't block you (as long as the data type is valid and check constraints are not violated). Things are different with GENERATED ALWAYS, of course.

Regarding the "not supported", it would be extremely helpful if you would post the complete error message. But my guess is that your edition just doesn't have this feature available. The documentation describes all features for the full DB2 edition (enterprise server edition) but you don't have that.

Thanks for your kind advise, and I have got an idea from your answer.

Regarding to the "not supported" problem, please find the SQL I executed below,

create table ppm.twrl(col1 integer, col2 char(50));
insert into ppm.twrl values(11, 'string1'),(12, 'string2'),(13, 'string3'),(14, 'string4'),(15, 'string5');
alter table ppm.twrl alter column col1 set GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE );

then I got the error return as,

SQL0270N Function not supported (Reason code = "72"). SQLSTATE=42997

0 Row(s) affected

CLI0125E Function sequence error. SQLSTATE=S1010

but please be reminded that the testing machine is DB2 Enterprise V9.1.5

Database server = DB2/AIX64 9.1.5

Your problem is totally different

Reason code 72 is
so tweak your first statement to
that is bcoz generated by identity columns can never be null bcoz they are defined to be so..... also since your column col1 can hold null values, the consistency check fails for existing rows on issuing the alter table command....

sorry about that, i haven't traced the reason after got the error code.

thanks you all for investigation!