Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Red face Unanswered: can db2 luw v9.5 express-c support alter column from integer to identity?

    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!

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    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
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Apr 2009
    Posts
    4

    Unhappy

    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?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2009
    Posts
    4

    Lightbulb

    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

  6. #6
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Your problem is totally different

    Reason code 72 is
    A nullable column cannot be changed to become an identity column.
    so tweak your first statement to
    create table ppm.twrl(col1 integer not null, col2 char(50));
    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....
    IBM Certified Database Associate, DB2 9 for LUW

  7. #7
    Join Date
    Apr 2009
    Posts
    4
    sorry about that, i haven't traced the reason after got the error code.

    thanks you all for investigation!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •