DB2 sequence and primary key self-growth

1. Define the primary key as the auto-grow identifier type

In MySQL, if the primary key of the table is set to the Auto_increment type, the database is automatically assigned a value for the primary key. For example:

CREATE TABLE Customers (ID int auto_increment PRIMARY key NOT NULL, name varchar (15));

INSERT into customers (name) VALUES ("Name1"), ("name2");

Select ID from Customers;

The above SQL statement first creates the Customers table and then inserts two records, only setting the value of the Name field when inserting. The ID field in the last query table, the query result is:

Id

1

2

Thus, once the ID is set to the Auto_increment type, the MySQL database automatically assigns a value to the primary key in an incremental manner.

In MS SQL Server, if the primary key of the table is set to the identity type, the database is automatically assigned a value for the primary key. For example:

CREATE TABLE Customers (ID int identity (primary) key NOT NULL, name varchar (15));

INSERT into customers (name) VALUES ("Name1"), ("name2");

Select ID from Customers;

The query results are the same as for MySQL. Thus, once the ID is set to the identity type, the MS SQL Server database automatically assigns a value to the primary key in an incremental manner. The identity contains two parameters, the first parameter represents the starting value, and the second parameter represents the increment.

In DB2, if the primary key of the table is set to:
UserID integer NOT NULL generated always as identity (start with 1,increment by 1),

2. Get an auto-growing identifier from the sequence

1. Sequence definition
ORACLE:

CREATE SEQUENCE <sequence_name>

INCREMENT by <integer>

START with <integer>

MAXVALUE <integer>/Nomaxvalue

MINVALUE <integer>/Nominvalue

Cycle/nocycle

CACHE <#>/NOCACHE

Order/noorder;

DB2:

CREATE SEQUENCE <sequence-name>

As data-type default as Integer
START with <numeric-constant>
INCREMENT by <numeric-constant> default INCREMENT by 1
MINVALUE <numeric-constant> | No MINVALUE default No MINVALUE
MAXVALUE <numeric-constant> | No MAXVALUE default No MAXVALUE
NO CYCLE | Cycle default NO Cycle
CACHE <numeric-constant> | NO Cache Default Cache 20
NO ORDER | Order default NO Order

2. Sequence value
Remove a value:
ORACLE: Sequence. Nextval
Db2:nextval for sequence or NEXT VALUE for sequence
To take the current value:
ORACLE: Sequence. Currval
Db2:previous VALUE for sequence or prevval for sequence

3. Sequence Set start value
ORACLE: Gets the next value, sets the step (the difference between the next value and the set start value), gets the next value, and then changes the step to the original value
Db2:alter SEQUENCE sequence name RESTART with next value

4. Modification
ORACLE:
Modify maximum alter SEQUENCE <sequence_name> max value <integer>
Modify minimum value: ALTER SEQUENCE <sequence_name> min value <integer> (this value needs to be smaller than the current value)
Modify step: Alter SEQUENCE <sequence_name> INCREMENT by <integer>;
Modify the Cache value: ALTER SEQUENCE <sequence_name> Cache <integer> | NOCACHE
Modify Loop properties: ALTER SEQUENCE <sequence_name> <cycle | Nocycle>
Modify Sort properties: ALTER SEQUENCE <sequence_name> <order | Noorder>

DB2:
Modify Max: ALTER SEQUENCE <sequence_name> max value <numeric-constant> | NO MAXVALUE
Modify Minimum: ALTER SEQUENCE <sequence_name> min value <numeric-constant> | NO MINVALUE (this value needs to be smaller than the current value)
Modify step: Alter SEQUENCE <sequence_name> INCREMENT by <numeric-constant>;
Modify the Cache value: ALTER SEQUENCE <sequence_name> Cache <numeric-constant> | NO CACHE
Modify Loop properties: ALTER SEQUENCE <sequence_name> <cycle | NO cycle>
Modify Sort properties: ALTER SEQUENCE <sequence_name> <order | NO order>
New count: ALTER SEQUENCE <sequence_name> RESTART | RESTART with <numeric-constant>

5. DROP
Oracle:drop SEQUENCE <sequence_name>;
Db2:drop SEQUENCE <sequence_name>;

6. Examples

CREATE SEQUENCE Jobshow_seq
As INTEGER
CACHE 20
MAXVALUE 999999
ORDER;

CREATE SEQUENCE Jobwork_seq
As INTEGER
CACHE 20
MAXVALUE 999999
ORDER;

CREATE SEQUENCE Yn_seq
As INTEGER
MAXVALUE 99999999;

Select Yn_seq.nextval from Ynzp_job_jobinfo

Select Nextval for kfcmcc_seq from sysibm.sysdummy1

DB2 sequence and primary key self-growth