Definition and use of sequence in DB2

Definition and use of sequence in DB2

-- Method 1 (self-used, green, secure, and pollution-free): Create a sequence named SEQ_LDBG, auto-increment 1, create sequence SEQ_LDBG as int start with 1 increment by 1 MINVALUE 1 no maxvalue no cycle no cache order; -- use nextval for SEQ_LDBG in SQL, codechange7 is the table name, select nextval for SEQ_LDBG, code, name from codechange7; --- Method 2: create sequence SEQ_LDBG start with 1 increment by 1 nomaxvalue nocycle cache 24; -- Get the next value select nextval for SEQ_LDBG from codechange7; -- get the current value select prevval for SEQ_LDBG from codechange7; -- insert record insert into codechange7 (id) values (nextval for id) -- delete sequence drop sequence SEQ_LDBG; -- set SEQUENCE start value ALTER sequence SEQUENCE name (SEQ_LDBG) restart with next value (1) -- modify maximum value: alter sequence <sequence_name> max value <numeric-constant> | no maxvalue: alter sequence <sequence_name> min value <numeric-constant> | NO MINVALUE

 

(This value must be smaller than the current value)

Modify the step size: alter sequence <sequence_name> increment by <numeric-constant>; modify the CACHE value: alter sequence <sequence_name> CACHE <numeric-constant> | no cache to modify the loop attribute: alter sequence <sequence_name> <CYCLE | no cycle> modify the sorting attribute: alter sequence <sequence_name> <ORDER | no order> new count: alter sequence <sequence_name> RESTART | restart with <numeric-constant>