DB2: How to Add an Identity Column to an Existing Table

Adding generated key columns to existing tables seems to be complicated in DB2. The following code adds a new generated primary key id to the table tablename. Granted, the code is ugly, but it works. I was not able to come up with a better solution in time.


ALTER TABLE tablename ADD COLUMN id INTEGER NOT NULL WITH DEFAULT 0
ALTER TABLE tablename ALTER COLUMN id GENERATED BY DEFAULT AS IDENTITY
REORG TABLE tablename
UPDATE tablename SET id = DEFAULT
ALTER TABLE tablename ALTER COLUMN id SET PRIMARY KEY

EDIT: I tested this code with DB2 9.7, but it should work on earlier versions of DB2.

This entry was posted in DB2. Bookmark the permalink.

5 Responses to DB2: How to Add an Identity Column to an Existing Table

  1. glee says:

    hi,
    When i execute the third(ALTER TABLE tablename ALTER COLUMN id SET NOT NULL), i meet erro,like this:
    [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token “NOT” was found following “alter column id set”. Expected tokens may include: “DEFAULT”. SQLSTATE=42601
    Statement processed with ERROR.
    My DB is DB2 V8.3

    BTW,ALTER TABLE tablename ADD COLUMN id INTEGER
    UPDATE tablename SET id = 0
    make that two become one,like this:
    ALTER TABLE tablename ADD COLUMN id INTEGER NOT NULL with default 0;

    Thank you for your reply.

    Glee

    • Thanks for the info. Apparently, DB2 8.3 does not support adding NOT NULL constraints to existing columns. However, I have modified my code so that it should work on DB2 8.3 now. Please give it a try.

  2. Anthalamus says:

    Thanks, very useful. I suggested your page in:
    http://www.dbforums.com/db2/1650358-adding-identity-column-existing-table-2.html
    Cheers

  3. Stanislaw says:

    hallo blog.joachim-selke.de team,finde ich echt super dass ihr weiter macht, weiter so
    wünscht

  4. Ramon Gajardo says:

    Hi, this process working good. but if you have 27 millions record take 6 hrs and received the error : The transaction log for the database is full.

Leave a Reply

Your email address will not be published. Required fields are marked *