Create and use Sequence object in DB2 for i SQL

Create and use Sequence object in DB2 for i SQL

Sequence is very similar to identity column as they both generate the unique values. However, sequences are objects that are independent from tables. When we create sequence on IBM i a *DTAARA type of object created for the sequence. We can use CREATE SEQUENCE statement to create the sequence object.

Create a Sequence object

CREATE SEQUENCE SEQ1
START WITH 1
INCREMENT BY 1
MAXVALUE 5000
CYCLE
CACHE 10

This sequence object is defined with a starting value of 1, incremented by 1 and restarts with 1 when maximum value 5000 is reached. This sequence is *DTAARA object of default type *CHAR and default length 2000.

How to insert values into a column using a Sequence

Create a table using below script

CREATE TABLE PF1             
   (ROLLNO BIGINT NOT NULL,  
   NAME VARCHAR (30) ,       
   CLASS CHAR(2))            

Then insert the next sequence value into column ROLLNO in table PF1:

INSERT INTO PF1 (ROLLNO, NAME, CLASS)
VALUES (NEXT VALUE FOR SEQ1, 'A', '1')

Run the SELECT statement on table PF1

 ROLLNO   NAME                            CLASS
      1   A                                1   

Execute the same above INSERT statement again on table PF1

Run the SELECT statement on table PF1

ROLLNO   NAME                            CLASS 
     1   A                                1    
     2   A                                1    

We can also insert the previous value for the sequence SEQ1 by using the PREVIOUS VALUE expression.

Then insert the previous sequence value into column ROLLNO in table PF1:

INSERT INTO PF1 (ROLLNO, NAME, CLASS)
VALUES (PREVIOUS VALUE FOR SEQ1, 'A', '1')

Run the SELECT statement on table PF1

ROLLNO   NAME                            CLASS
     1   A                                1   
     2   A                                1   
     2   A                                1   

How to alter Sequence

We can alter sequence by using ALTER SEQUENCE statement.

Restart the sequence

Change the increment between future sequence values

set or remove the MIN and MAX value for sequence

Change the number of cached sequence numbers using CACHE clause which basically used for pre-allocating some values in memory for faster access.

Change the CYCLE attribute for sequence

Change whether sequence numbers must be generated in order of request using ORDER clause. Default is NO ORDER.

Change the Sequence SEQ1 increment by value from 1 to 2

ALTER SEQUENCE SEQ
INCREMENT BY 2

Now run the below insert query and select to see the result

INSERT INTO PF1 (ROLLNO, NAME, CLASS)
VALUES (NEXT VALUE FOR SEQ1, 'A', '1')

ROLLNO   NAME                            CLASS
     1   A                                1   
     2   A                                1   
     2   A                                1   
    12   A                                1   

We have noticed that the next assigned value for the ROLLNO column is 12 and which seems to be incorrect. however, it is correct, now look how this happens, originally when this sequence was created a cache value of 10 was assigned and system assigns the first 10 values for this sequence and when its altered the first 10 values were dropped and it start again with the next available value, in this case original 10 was cached, plus the next increment 2. means 12. If we did not specified the CACHE clause in the original CREATE SEQUENCE clause then system assigns a default cache value of 20. If that sequence was altered then the next available value would be 22 as per above set increment.