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?

Answer 1

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.