DB2 - Sequences


Advertisements

This chapter introduces you to the concept of sequence, creation of sequence, viewing the sequence, and dropping them.

Introduction

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:

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:

Syntax:

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  

Output:

 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:

Syntax:

db2 drop sequence <seq_name>>

Example: [To drop sequence ‘sales1_seq’ from database]

db2 drop sequence sales1_seq  

Output:

 DB20000I The SQL command completed successfully. 

Useful Video Courses


Video

DB2 Online Training

10 Lectures 1.5 hours

Nishant Malik

Video

Oracle DB Online Training

41 Lectures 8.5 hours

Parth Panjabi

Video

Oracle DBA Performance Tuning

53 Lectures 11.5 hours

Parth Panjabi

Video

Oracle DB 12c Online Training

33 Lectures 7 hours

Parth Panjabi

Video

MongoDB Online Training

44 Lectures 3 hours

Arnab Chakraborty

Video

DBMS for GATE Exams

178 Lectures 14.5 hours

Arnab Chakraborty

Advertisements