Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2008
    Posts
    35

    Unanswered: Adding identity column to existing table

    I want to add a new column which should have identity properties, i tried this and never worked

    Code:
    alter table test2 add column id integer not null with default 0;
    alter table test2 alter column id set generated always as identity;
    The increment is not happening.

  2. #2
    Join Date
    Oct 2007
    Posts
    246
    its better to recreate the table with new identity column

    regds
    Paul

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    alter table test2 add column id integer not null with default 0;
    alter table test2 alter column id drop default;
    alter table test2 alter column id set generated always as identity;

    What platform and DB2 version/release are you using?

    Here is a result on my DB2 9.7 for Windows:
    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.0
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    DROP TABLE test2;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test2
    (pk   INTEGER NOT NULL PRIMARY KEY
    ,col1 INTEGER
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test2
    VALUES
     (1, 1), (2, NULL), (3, 3);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    alter table test2 add column id integer not null with default 0;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    alter table test2 alter column id drop default;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    alter table test2 alter column id set generated always as identity;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test2(pk, col1)
    VALUES
     (5, 3), (6, NULL), (7, 4);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test2;
    ------------------------------------------------------------------------------
    
    PK          COL1        ID         
    ----------- ----------- -----------
              1           1           0
              2           -           0
              3           3           0
              5           3           1
              6           -           2
              7           4           3
    
      6 record(s) selected.
     
     
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test2(pk, col1)
    VALUES
     (8, 5), (9, 6);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test2;
    ------------------------------------------------------------------------------
    
    PK          COL1        ID         
    ----------- ----------- -----------
              1           1           0
              2           -           0
              3           3           0
              5           3           1
              6           -           2
              7           4           3
              8           5           4
              9           6           5
    
      8 record(s) selected.
    Last edited by tonkuma; 11-16-09 at 09:41.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dinjo_jo View Post
    I want to add a new column which should have identity properties, i tried this and never worked

    Code:
    alter table test2 add column id integer not null with default 0;
    alter table test2 alter column id set generated always as identity;
    The increment is not happening.
    Try doing a reorg of the table. Some changes prior to 9.7 require reorgs.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question Is this not good

    Is this not good ?

    Code:
    CREATE TABLE test2
    (pk   INTEGER NOT NULL PRIMARY KEY
    ,col1 INTEGER);
    
    Create unique index ix_pk on test2 (pk asc);
    
    alter table test2 add column col2 integer 
           generated always as identity (start with 1);
    
    INSERT INTO test2(pk, col1) VALUES  (1, 1); 
    INSERT INTO test2(pk, col1) VALUES  (2, NULL); 
    INSERT INTO test2(pk, col1) VALUES  (3, 3);
    
    select * from test2;
    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Lenny,
    you can try it yourself.

    I got error messages on my DB2 9.7 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    alter table test2 add column col2 integer 
           generated always as identity (start with 1);
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "IDENTITY" was found following "AS".  Expected 
    tokens may include:  "<left_paren>".  SQLSTATE=42601
    
    SQL0104N  An unexpected token "IDENTITY" was found following "AS".  Expected tokens may include:  "<left_paren>

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma View Post
    Lenny,
    you can try it yourself.

    I got error messages on my DB2 9.7 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    alter table test2 add column col2 integer 
           generated always as identity (start with 1);
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "IDENTITY" was found following "AS".  Expected 
    tokens may include:  "<left_paren>".  SQLSTATE=42601
    
    SQL0104N  An unexpected token "IDENTITY" was found following "AS".  Expected tokens may include:  "<left_paren>
    I tried it before I posted. I did not have any exeptions.

    Lenny

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried it before I posted. I did not have any exeptions.
    On what platform and DB2 version/release?

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma View Post
    On what platform and DB2 version/release?
    DB2 V8 z/os

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Marcus and Lenny,
    I thought that reorg is required on DB2 for z/OS, while reorg is not required and reqired following three steps on LUW.
    alter table test2 add column id integer not null with default 0;
    alter table test2 alter column id drop default;
    alter table test2 alter column id set generated always as identity;

    Because, I found following syntax and descriptions.

    On DB2 Version 9.1 for z/OS SQL Reference:
    Adding an identity column: When you add an identity column to a table that is not
    empty, DB2 places the table space that contains the table in the REORG pending
    state. When the REORG utility is subsequently run, DB2 generates the values for
    the identity column in all existing rows and then removes the REORG pending
    status. These values are guaranteed to be unique, and their order is
    system-determined.
    On IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference Volume 2:
    Extraction from syntax diagram of ALTER TABLE:
    Syntax
    ALTER TABLE table-name
    .....
    ADD [COLUMN] column-definition
    .....

    column-definition:
    column-name [data-type(1)] [column-options]

    column-options:
    .....
    generated-column-definition
    .....

    generated-column-definition:
    default-clause
    or
    GENERATED [ALWAYS | BY DEFAULT] as-row-change-timestamp-clause
    or
    GENERATED [ALWAYS] AS ( generation-expression )
    Although, the chain of definitions are long,
    I interpreted that adding generated as identity column is not included in the syntax.
    So, I thought that it is required at least two steps including adding column without generated as identity and setting it "generated as identity", like dinjo_jo did.

    After I got error messages from the two steps, I found following description on SQL Reference Volume 2:
    SET generated-column-alteration
    .....
    GENERATED ALWAYS or GENERATED BY DEFAULT
    .....
    identity-options
    Specifies that the column is the identity column for the table. The
    column
    must not already be defined as the identity column, cannot
    have a generation expression, or cannot have an explicit default
    (SQLSTATE 42837).
    .......
    So, I added second step,
    alter table test2 alter column id drop default;

    That was I did and thought.
    Last edited by tonkuma; 11-16-09 at 17:33.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow If you add column to non-empty table - REORG

    tonkuma, you are right, as usual....

    Reorg is required on the following sequence of sql statements:

    Code:
    CREATE TABLE test2
    (pk   INTEGER NOT NULL 
    ,col1 INTEGER);
    
    INSERT INTO test2(pk, col1) VALUES  (1, 1);
    INSERT INTO test2(pk, col1)  VALUES  (2, NULL);
    INSERT INTO test2(pk, col1)  VALUES  (3, 3);
    
    select * from test2;
    
    alter table test2 add column col2 integer 
               generated always as identity (start with 0);
    After ALTER you have to run REORG. This is also logicaly true sequence.

    Lenny

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The value of the olumn of existing rows are not updated on my DB2 9.7 for Windows,
    after setting the column to generated always as identity.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test2;
    ------------------------------------------------------------------------------
    
    PK          COL1        ID         
    ----------- ----------- -----------
              1           1           0
              2           -           0
              3           3           0
              5           3           1
              6           -           2
              7           4           3
              8           5           4
              9           6           5
    
      8 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT CASE
           WHEN LAG(tabname) OVER(ORDER BY colno) IS NULL THEN
                CAST(tabschema || '.' || tabname AS VARCHAR(20) )
           ELSE ''
           END  AS table
    .....
      FROM syscat.columns
     WHERE tabschema = 'DB2ADMIN'
       AND tabname = 'TEST2'
     ORDER BY
           colno;
    ------------------------------------------------------------------------------
    
    TABLE                COLNAME  COLNO  DATA_TYPE  LENGTH      NULLS DEFAULT  IDENTITY GENERATED
    -------------------- -------- ------ ---------- ----------- ----- -------- -------- ---------
    DB2ADMIN.TEST2       PK            0 INTEGER              4 N     -        N                 
                         COL1          1 INTEGER              4 Y     -        N                 
                         ID            2 INTEGER              4 N     -        Y        A        
    
    
      3 record(s) selected.
    Even REORG didn't resolved that duplicates.
    Code:
    ------------------------------ Commands Entered ------------------------------
    REORG TABLE test2;
    ------------------------------------------------------------------------------
    DB20000I  The REORG command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test2;
    ------------------------------------------------------------------------------
    
    PK          COL1        ID         
    ----------- ----------- -----------
              1           1           0
              2           -           0
              3           3           0
              5           3           1
              6           -           2
              7           4           3
              8           5           4
              9           6           5
    
      8 record(s) selected.
    So, I tempolary dropped the identity, update id column with unique values, then set identity again specified starting value with larger than exisiting values.

    Drop identity, then update id column:
    Code:
    ------------------------------ Commands Entered ------------------------------
    alter table test2 alter column id drop identity;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO test2 target
    USING (SELECT pk
                , ROWNUMBER() OVER(ORDER BY pk) AS rnum
             FROM test2
          ) AS source
      ON  source.pk = target.pk
    WHEN MATCHED
    THEN UPDATE
         SET id = rnum
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test2;
    ------------------------------------------------------------------------------
    
    PK          COL1        ID         
    ----------- ----------- -----------
              1           1           1
              2           -           2
              3           3           3
              5           3           4
              6           -           5
              7           4           6
              8           5           7
              9           6           8
    
      8 record(s) selected.
    Set identity, insert new rows, then see the rows:
    Code:
    ------------------------------ Commands Entered ------------------------------
    alter table test2 alter column id set generated always as identity (START WITH 10);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test2(pk, col1)
    VALUES
     (10, 0), (11, 1), (12, 2), (13, 3);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test2;
    ------------------------------------------------------------------------------
    
    PK          COL1        ID         
    ----------- ----------- -----------
              1           1           1
              2           -           2
              3           3           3
              5           3           4
              6           -           5
              7           4           6
              8           5           7
              9           6           8
             10           0          10
             11           1          11
             12           2          12
             13           3          13
    
      12 record(s) selected.
    Last edited by tonkuma; 11-16-09 at 20:07.

  13. #13
    Join Date
    May 2008
    Posts
    35
    When i try to run the

    alter table test2 add column id integer not null with default 0;
    alter table test2 alter column id drop default;
    alter table test2 alter column ID set generated always as identity(start with 1,increment by 1);

    First 2 commands work well but last command a error
    Alter table test2 specified attributes for column ID that are not compatibile with existing column

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    dinjo_jo,
    please supply more informations.
    - What platform and DB2 version/relase are you using?
    - DDL(CREATE TABLE/INDEX etc. ) of table test2
    - Data characteristics of table test2(number of rows. values of rows of first some rows.)
    - Sequence of commands and received full message code/text including "DB20000I The SQL command completed successfully.".

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did "alter table test2 alter column id drop default;" completed successfully?

    One way may be to check catalog view after the command,
    if you are tested on DB2 for LUW.
    Like this:
    Code:
    /* Adjust length of casted data types, if some of them were short */
    SELECT CASE
           WHEN LAG(tabname) OVER(PARTITION BY tabschema ORDER BY colno) IS NULL THEN
                CAST(tabschema || '.' || tabname AS VARCHAR(30) )
           ELSE ''
           END  AS table
         , CAST(colname AS VARCHAR(10) ) AS colname
         , colno
         , CAST(typename AS VARCHAR(9) ) AS data_type
         , length
         , nulls
         , CAST(default AS VARCHAR(7) ) AS default
         , identity
         , generated
      FROM syscat.columns
     WHERE tabname = 'TEST2'
     ORDER BY
           tabschema
         , tabname
         , colno
    ;

Posting Permissions

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