- DB2 Tutorial
- DB2 - Introduction
- DB2 - Server Installation
- DB2 - Instance
- DB2 - Databases
- DB2 - Bufferpools
- DB2 - Tablespaces
- DB2 - Storagegroups
- DB2 - Schemas
- DB2 - Data Types
- DB2 - Tables
- DB2 - Alias
- DB2 - Constraints
- DB2 - Indexes
- DB2 - Triggers
- DB2 - Sequences
- DB2 - Views
- DB2 with XML
- DB2 - Backup and Recovery
- DB2 - Database Security
- DB2 - Roles
- DB2 - LDAP
- DB2 Useful Resources
- DB2 - Questions and Answers
- DB2 - Quick Guide
- DB2 - Useful Resources
- DB2 - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
DB2 - Sequences
This chapter introduces you to the concept of sequence, creation of sequence, viewing the sequence, and dropping them.
A sequence is a software function that generates integer numbers in either ascending or descending order, within a definite range, to generate primary key and coordinate other keys among the table. You use sequence for availing integer numbers say, for employee_id or transaction_id. A sequence can support SMALLINT, BIGINT, INTEGER, and DECIMAL data types. A sequence can be shared among multiple applications. A sequence is incremented or decremented irrespective of transactions.
A sequence is created by CREATE SEQUENCE statement.
Types of Sequences
There are two type of sequences available:
NEXTVAL: It returns an incremented value for a sequence number.
PREVIOUS VALUE: It returns recently generated value.
Parameters of sequences
The following parameters are used for sequences:
Data type: This is the data type of the returned incremented value. (SMALLINT, BIGINT, INTEGER, NUMBER, DOUBLE)
START WITH: The reference value, with which the sequence starts.
MINVALUE: A minimum value for a sequence to start with.
MAXVALUE: A maximum value for a sequence.
INCREMENT BY: step value by which a sequence is incremented.
Sequence cycling: the CYCLE clause causes generation of the sequence repeatedly. The sequence generation is conducted by referring the returned value, which is stored into the database by previous sequence generation.
Creating a sequence
You can create sequence using the following syntax:
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
db2 drop sequence <seq_name>>
Example: [To drop sequence ‘sales1_seq’ from database]
db2 drop sequence sales1_seq
DB20000I The SQL command completed successfully.