[DB2] how to use Sequence table

1. describe sequence table
describe table syscat.sequences;

2.  select sequence information
SELECT * FROM syscat.sequences;

3. create sequence
CREATE SEQUENCE seq_tab START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24;
-> start 1, increment 1 by 1  to  no limit
CREATE SEQUENCE seq_tab START WITH 1 INCREMENT BY 1 maxvalue 10000 NOCYCLE CACHE 24;
-> start 1 , increment by 1  to  10000, and error on if exceed by max value

4. modify sequence
ALTER SEQUENCE seq_tab INCREMENT BY 5;
    -> modify for increment step by 5
ALTER SEQUENCE seq_tab  maxvalue 100;
    -> modify for 100 max limit

5. remove sequence
DROP SEQUENCE seq_tab  restrict

6. use exsample

CREATE SEQUENCE seq_tab  START WITH 1 INCREMENT BY 2 NOMAXVALUE NOCYCLE CACHE 24;

create table test
(
idx INTEGER not null primary key,
name varchar(10) not null
);

insert into test (idx,name) values (NEXTVAL FOR seq_tab  ,’name_1′);
insert into test (idx,name) values (NEXTVAL FOR seq_tab  ,’name_2′);
insert into test (idx,name) values (NEXTVAL FOR seq_tab  ,’name_3′);

select * from test;
DROP TABLE TEST;

DROP SEQUENCE seq_tab  restrict;

8. like mysql auto_increment column on DB2

CREATE TABLE customer_orders_t (
order_id   INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1
       INCREMENT BY 1
       MINVALUE 1
       NO MAXVALUE NO CYCLE NO CACHE ORDER),
order_date DATE NOT NULL,
cust_id    INT NOT NULL,
product_id INT NOT NULL,
quantity   INT NOT NULL,
price      DECIMAL(10,2) NOT NULL,
status     CHAR(9) NOT NULL,PRIMARY KEY (order_date, order_id))

Fin.