IBM DB2 Create Table with Auto Increment Column, Sequence, Trigger, Truncate

on

Very first time, create table first.
CREATE TABLE TBL_USERS (
    ID_ BIGINT NOT NULL,
    NAMALENGKAP VARCHAR(128) NOT NULL,
    EMAIL VARCHAR(64) NOT NULL,
    PSWD VARCHAR(5120) NOT NULL,
    TGLSIGNUP VARCHAR(20) NOT NULL,
    TGLAUTH VARCHAR(20) NULL,
    TGLLOGIN VARCHAR(20) NULL,
    PRIMARY KEY (ID_)
);

If you want to look table properties,
where your user db access has not a privilleges to create DDL.
You can use this query.
$ SELECT TABSCHEMA, TABNAME, COLNO, COLNAME, TYPENAME, "LENGTH", "SCALE", "NULLS", "DEFAULT", KEYSEQ, "IDENTITY" FROM SYSCAT.COLUMNS WHERE TABNAME = 'TBL_USERS' ORDER BY COLNO ASC;

Now, create sequence for column "ID_" on "TBL_USERS".
$ CREATE SEQUENCE TBL_USERS_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;

If you got typo while give sequence name, drop it (drop sequence) with this query.
$ DROP SEQUENCE TBL_USERS_SEQ;

After that, create trigger for mapping sequence with auto increment column of table.
$ CREATE TRIGGER TBL_USERS_TRIGGER NO CASCADE BEFORE INSERT ON TBL_USERS REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET (NEW.ID_) = (NEXTVAL FOR TBL_USERS_SEQ); END

If you got typo while give trigger name, drop it (drop trigger) with this query.
$ DROP TRIGGER TBL_USERS_TRIGGER;

After that, do not forget to execute this query (reorg table / flush database), to flush any changes for tables, views, sequence, trigger, etc on current database schema.
$ CALL SYSPROC.ADMIN_CMD('REORG TABLE TBL_USERS');

If you want to truncate any rows on current table (truncate table) and then you want column "ID_" being restarted from the begining number again, try this query.
$ ALTER TABLE TBL_USERS ALTER COLUMN ID_ RESTART WITH 1;

If you want to existing table (coumn "ID_") start value for next val of sequence number from TBL_USERS_SEQ, try this query. It means, if value on sequence "TBL_USERS_SEQ" is 10, column "ID_" on table "TBL_USERS" would have starting value 11.
$ UPDATE TBL_USERS SET ID_ = TBL_USERS_SEQ.NEXTVAL;

After that, execute again (reorg table / flush database) query above.

Notice :
* Always be careful on production db! I'm not guarantee if you missed one of steps above.


Source :
https://stackoverflow.com/questions/35457306/db2-add-auto-increment-column-to-an-existing-table

Comments

Post a Comment