Adding identity column to existing table

dinjo_jo · #1 (**permalink**) 11-16-09, 07:28

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.

Mathew_paul · #2 (**permalink**) 11-16-09, 08:07

its better to recreate the table with new identity column

regds
Paul

tonkuma · #3 (**permalink**) 11-16-09, 08:36

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.

Marcus_A · #4 (**permalink**) 11-16-09, 10:09

Quote:

Originally Posted by dinjo_jo

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.

Lenny77 · #5 (**permalink**) 11-16-09, 12:36

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

tonkuma · #6 (**permalink**) 11-16-09, 15:00

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>

Lenny77 · #7 (**permalink**) 11-16-09, 15:08

Quote:

Originally Posted by tonkuma

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

tonkuma · #8 (**permalink**) 11-16-09, 15:33

Quote:

I tried it before I posted. I did not have any exeptions.

On what platform and DB2 version/release?

Lenny77 · #9 (**permalink**) 11-16-09, 15:34

Quote:

Originally Posted by tonkuma

On what platform and DB2 version/release?

DB2 V8 z/os

tonkuma · #10 (**permalink**) 11-16-09, 16:25

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:

Quote:

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:

Quote:

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:

Quote:

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.

Lenny77 · #11 (**permalink**) 11-16-09, 16:38

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

tonkuma · #12 (**permalink**) 11-16-09, 19:03

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.

dinjo_jo · #13 (**permalink**) 11-16-09, 21:54

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

tonkuma · #14 (**permalink**) 11-16-09, 22:26

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.".

tonkuma · #15 (**permalink**) 11-16-09, 22:42

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech