ChannelDB2

How to add IDENTITY column to existing table

Every DB2 admin discovers sooner or later that DB2 can not add identity column to existing table easily. Following statements fails due to syntax error: alter table public.clicks add column id integer generated always as identity

 

But i have good news for everyone. It can be still done, but it requires more steps.

 

1. Add not null integer column

You need to provide default value for it otherwise db2 will refuse it to make not null

alter table public.clicks add column id integer not null default 0
DB20000I  Příkaz SQL byl úspěšně dokončen.

 

2. Drop default value from column

I am not exactly sure why it is needed because some manuals on internet omits this step but i was not able to make it work without this on DB2 9.7.3 LUW

alter table public.clicks alter column id drop default
DB20000I  Příkaz SQL byl úspěšně dokončen.

 

3. Now set column to always generated

alter table public.clicks alter column id set generated always as identity
DB20000I  Příkaz SQL byl úspěšně dokončen.

 

4. Reorg table to make it writeable

reorg table public.clicks
DB20000I  Příkaz REORG byl úspěšně dokončen.

 

5. Now replace zeros with generated itentity values

update public.clicks set id = default

DB20000I  Příkaz SQL byl úspěšně dokončen.

 

6. And optionally make id column primary key for table

alter table public.clicks add constraint pkey primary key(id)
DB20000I  Příkaz SQL byl úspěšně dokončen.

 

Thats it, i hope you enjoyed a lesson. Now you can use actions on table which requires primary key. For example DB2 9.7 famous online table move by admin_move_table procedure for moving table into different tablespace or replication.

Views: 9428

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Bilal Abdullah on October 20, 2011 at 2:47am
Good One.. Many thanks for posting..
Comment by Leon Katsnelson on April 2, 2011 at 2:18am
Brilliant!

Featured Downloads

Try BLU Acceleration on Cloud

© 2017   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service