Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    16

    Red face Unanswered: altter table add column .... identity

    I need to create a table which has an identity column and some columns from another table.
    I tried many ways, I searched the forums and of course I read the documentation, but I didn't succeed.

    I tried something like:

    create table t as (select 0 as id generated by default as identity, col1,..coln from table) definition only

    it didn't work!

    I tried to create first the new table from the old table, and after theat to alter the table an add the identity column, like this:

    alter table t add column id int generated always as identity

    but it didn't worlk also !

    I don't know what to do anymore.
    Please help!

    Thanks in advance.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Couldn't you just db2look the original table def, save it to a file, add your column/change the name and run?
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Aug 2003
    Posts
    16
    No, I can't use db2look, because I'm using Java, so I must create the table using JDBC.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by b_moni
    No, I can't use db2look, because I'm using Java, so I must create the table using JDBC.
    I'm not sure there's an easy way to do this without the original table definition. As you said, ALTER won't work with IDENTITY, and you can't add columns when you're using the "AS" clause.

    You might get away with using the MetaData classes JDBC provides, but that's fairly ugly... and won't give you the whole definition, probably just the data types.

    ie. DatabaseMetaData's getColumns() method...

    Anyone else have any ideas?
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by J Petruk

    You might get away with using the MetaData classes JDBC provides, but that's fairly ugly... and won't give you the whole definition, probably just the data types.
    As it turns out, getColumns() gives a LOT of information beyond the data types...

    Even so, not sure it'll make it easy to build a CREATE TABLE from.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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