This part acquaints you with the idea of succession, production of grouping, seeing the arrangement, and dropping them.
A succession is a product work that creates whole number numbers in either rising or plummeting request, inside a distinct range, to produce essential key and arrange different keys among the table. You use succession for benefiting whole number numbers state, for employee_id or transaction_id. An arrangement can bolster SMALLINT, BIGINT, INTEGER, and DECIMAL information types. A grouping can be shared among different applications. A succession is augmented or decremented regardless of exchanges.
An arrangement is made by CREATE SEQUENCE articulation.
Types of Sequences
There are two sort of groupings accessible:
- NEXTVAL: It restores an increased an incentive for a grouping number.
- Past VALUE: It returns as of late created esteem.
Parameters of sequences
The accompanying parameters are utilized for groupings:
Information type: This is the information sort of the returned augmented worth. (SMALLINT, BIGINT, INTEGER, NUMBER, DOUBLE)
START WITH: The reference esteem, with which the grouping begins.
MINVALUE: A base an incentive for a succession to begin with.
MAXVALUE: A most extreme incentive for a succession.
Augmentation BY: step an incentive by which an arrangement is increased.
Arrangement cycling: the CYCLE provision causes age of the succession over and again. The arrangement age is directed by alluding the returned esteem, which is put away into the database by past succession age.
Creating a sequence
You can make arrangement utilizing the accompanying sentence structure:
db2 create sequence <seq_name>
Example: [To create a new sequence with the name ‘sales1_seq’ and increasing values from 1]
db2 create sequence sales1_seq as int start with 1 increment by 1
Viewing the sequences
You can view a sequence using the syntax given below:
db2 value <previous/next> value for <seq_name>
Example: [To see list of previous updated value in sequence ‘sales1_seq’]
db2 values previous value for sales1_seq
1 ----------- 4 1 record(s) selected.
Dropping the sequence
To remove the sequence, you need to use the “DROP SEQUENCE” command. Here is how you do it:
db2 drop sequence <seq_name>>
Example: [To drop sequence ‘sales1_seq’ from database]
db2 drop sequence sales1_seq
DB20000I The SQL command completed successfully.