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.
