DB2 add auto increment column to an existing table

Question

I have a table with following schema in my DB2 database.

CREATE TABLE IDN_OAUTH_CONSUMER_APPS (
        CONSUMER_KEY VARCHAR (255) NOT NULL,
        CONSUMER_SECRET VARCHAR (512),
        USERNAME VARCHAR (255),
        TENANT_ID INTEGER DEFAULT 0,
        APP_NAME VARCHAR (255),
        OAUTH_VERSION VARCHAR (128),
        CALLBACK_URL VARCHAR (1024),
        GRANT_TYPES VARCHAR (1024)
/

I need to add a new column ID of Type integer not null auto increment, and make it the primary key. How can I do that without deleting the table?

Has the table already values? What did you try so far, any errors, anything learned to share? — data_henrik, Feb 17 '16 at 13:02
Yes, the table has values, I tried "ALTER TABLE IDN_OAUTH_CONSUMER_APPS ADD COLUMN ID INTEGER NOT NULL" thinking of adding a sequence after adding the column, but I can't add not null without adding a default value — Chamila Wijayarathna, Feb 17 '16 at 13:10
`alter table IDN_OAUTH_CONSUMER_APPS add ID INTEGER not null default 0; alter table IDN_OAUTH_CONSUMER_APPS alter column ID drop default; alter table IDN_OAUTH_CONSUMER_APPS alter column ID set GENERATED ALWAYS AS IDENTITY (start with 1); update IDN_OAUTH_CONSUMER_APPS set ID = DEFAULT; alter table IDN_OAUTH_CONSUMER_APPS add primary key (ID); call sysproc.admin_cmd('reorg table IDN_OAUTH_CONSUMER_APPS');` — Charles Tempo, Jun 17 '19 at 11:48

score 3 · Accepted Answer · answered Feb 17 '16 at 16:21

I could do this successfully using following set of queries.

ALTER TABLE IDN_OAUTH_CONSUMER_APPS ADD COLUMN ID INTEGER NOT NULL DEFAULT 0

CREATE SEQUENCE IDN_OAUTH_CONSUMER_APPS_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE

CREATE TRIGGER IDN_OAUTH_CONSUMER_APPS_TRIGGER NO CASCADE BEFORE INSERT ON IDN_OAUTH_CONSUMER_APPS REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET (NEW.ID) = (NEXTVAL FOR IDN_OAUTH_CONSUMER_APPS_SEQUENCE); END

REORG TABLE IDN_OAUTH_CONSUMER_APPS

UPDATE IDN_OAUTH_CONSUMER_APPS SET ID = IDN_OAUTH_CONSUMER_APPS_SEQUENCE.NEXTVAL

And then add primary key using alter table.

score 2 · Answer 2 · answered Feb 17 '16 at 13:19

Use a multi-step approach:

add the column ALTER TABLE ADD... with just the integer data type and as nullable
update the table to set the intended identity values for that column
alter the table to add the auto-generation
alter the table to add the primary key on that column

You need to have multiple steps because the identity values need to be added manually. Syntax and examples for ALTER TABLE can be found here.

score 2 · Answer 3 · answered Feb 21 '16 at 19:40

2

There is an easy way to do it. Just run the alters above:

ALTER TABLE idn_oauth_consumer_apps ADD COLUMN id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE idn_oauth_consumer_apps ALTER COLUMN id SET GENERATED ALWAYS AS IDENTITY;

It is simple and fast even on big tables. Tested and working on DB2 for i V7R2.

answered Feb 21 '16 at 19:40

Mita

412
3
5

7

This isn't working for me on DB2 10.5.7. I get `set generated always not compatible with the existing column` – coberlin Aug 23 '18 at 20:11

score 2 · Answer 4 · answered Jan 18 '21 at 12:49

I recommend using this approach. It does not require creating any satellite objects - no triggers, sequences, etc...

alter table test.test2  add column id integer not null default 0;
alter table test.test2 alter column id drop default;
alter table test.test2  alter column id set generated always as identity;
call sysproc.admin_cmd ('reorg table test.test2');
update  test.test2 set id = default;
commit;

If using "db2" cli then the reorg command may be run directly without the "call sysproc.admin_cmd" wrapper.

score 0 · Answer 5 · answered Feb 17 '16 at 13:02

0

Create a new table with the primary key field. Insert the records from the old table. Drop the old table and if you can, rename the new one. If you can't rename it, recreate it and populate from the one that now has the records.

answered Feb 17 '16 at 13:02

Dan Bracuk

20,699
4
26
43

I want to do it without deleting the existing table. – Chamila Wijayarathna Feb 17 '16 at 13:09

score 0 · Answer 6 · answered Aug 24 '18 at 15:20

Building on Chamila Wijayarathna's answer, I used the following:

ALTER TABLE IDN_OAUTH_CONSUMER_APPS ADD COLUMN ID INTEGER NOT NULL DEFAULT 0

CREATE SEQUENCE IDN_OAUTH_CONSUMER_APPS_ID_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE

CREATE TRIGGER IDN_OAUTH_CONSUMER_APPS_ID_TRIGGER NO CASCADE BEFORE INSERT ON
  IDN_OAUTH_CONSUMER_APPS REFERENCING NEW AS NEW
  FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET (NEW.ID) = (NEXTVAL FOR
  IDN_OAUTH_CONSUMER_APPS_ID_SEQUENCE); END

REORG TABLE IDN_OAUTH_CONSUMER_APPS

UPDATE IDN_OAUTH_CONSUMER_APPS SET ID = IDN_OAUTH_CONSUMER_APPS_ID_SEQUENCE.NEXTVAL

ALTER TABLE IDN_OAUTH_CONSUMER_APPS ADD PRIMARY KEY (ID)

REORG TABLE IDN_OAUTH_CONSUMER_APPS

Then to reverse:

REORG TABLE IDN_OAUTH_CONSUMER_APPS

ALTER TABLE IDN_OAUTH_CONSUMER_APPS DROP PRIMARY KEY

DROP TRIGGER IDN_OAUTH_CONSUMER_APPS_ID_TRIGGER

DROP SEQUENCE IDN_OAUTH_CONSUMER_APPS_ID_SEQUENCE

ALTER TABLE IDN_OAUTH_CONSUMER_APPS DROP COLUMN ID

REORG TABLE IDN_OAUTH_CONSUMER_APPS

score 0 · Answer 7 · answered Jan 06 '20 at 14:24

0

Tried this on DB2 for z/OS v12 and it worked:

alter table TABLE_NAME add column id integer generated always as identity

answered Jan 06 '20 at 14:24

ramazan polat

7,111
1
48
76

DB2 add auto increment column to an existing table

7 Answers7