CODEWITHSUNDEEP
sqldb2auto-incrementdbmigrate
Chamila Wijayarathna
Chamila Wijayarathna

Reputation: 1933

DB2 add auto increment column to an existing table

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?

Upvotes: 3

Views: 23041

Answers (7)

Michael
Michael

Reputation: 3256

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.

Upvotes: 4

ramazan polat
ramazan polat

Reputation: 7880

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

alter table TABLE_NAME add column id integer generated always as identity

Upvotes: 0

coberlin
coberlin

Reputation: 608

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

Upvotes: 0

Mita
Mita

Reputation: 422

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.

Upvotes: 2

Chamila Wijayarathna
Chamila Wijayarathna

Reputation: 1933

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.

Upvotes: 3

data_henrik
data_henrik

Reputation: 17118

Use a multi-step approach:

  1. add the column ALTER TABLE ADD... with just the integer data type and as nullable
  2. update the table to set the intended identity values for that column
  3. alter the table to add the auto-generation
  4. 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.

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

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.

Upvotes: 0

Related Questions