Wednesday, August 14, 2019

More about SQL Sequences

sequences view, select next value, select previous value

Last week I wrote a post introducing what SQL Sequences are. Having had a chance to "play" with them some more I wanted to write about what I have discovered.

Before I go into any examples I need to have some Sequences to "play" with:

CREATE SEQUENCE MYLIB.BY_ONE
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_TEN
START WITH 10
INCREMENT BY 10
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_100
START WITH 100
INCREMENT BY 100
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_THREE
START WITH 30
INCREMENT BY 3
NO MAXVALUE
NO CYCLE ;

 

How do I know what Sequences are out there?

As I am using Sequences there must be a way to know what ones there are on this IBM i partition. Fortunately in Db2 there is a view I can use: SEQUENCES. I am not going to list all of the columns here, if you want to know what they are click on the link to the IBM documentation at the bottom of this post. In this example I am only concerned with the Sequences in my library, MYLIB:

SELECT SEQUENCE_SCHEMA,
       SEQUENCE_NAME,
       START_VALUE,
       INCREMENT,
       MINIMUM_VALUE,
       MAXIMUM_VALUE,
       CYCLE_OPTION,
       DATA_TYPE,
       NUMERIC_PRECISION
  FROM QSYS2.SEQUENCES
 WHERE SEQUENCE_SCHEMA = 'MYLIB'

One of the things I like about the columns' long names in the SQL Views is that they are so descriptive when I show the results you will know what each of those columns contain.

SEQUENCE_SCHEMA SEQUENCE_NAME START_VALUE INCREMENT MINIMUM_VALUE
--------------- ------------- ----------- --------- -------------
MYLIB           BY_ONE                  1         1             1
MYLIB           BY_TEN                 10        10            10
MYLIB           BY_THREE               30         3            30
MYLIB           BY_100                100       100           100


MAXIMUM_VALUE CYCLE_OPTION DATA_TYPE NUMERIC_PRECISION
------------- ------------ --------- -----------------
2147483647    NO           INTEGER                 10
2147483647    NO           INTEGER                 10
2147483647    NO           INTEGER                 10
2147483647    NO           INTEGER                 10

 

Update column in table on Insert

In the previous post I update all the rows in a table. If I wanted to update a row when I inserted I would do:

INSERT INTO MYLIB.TESTTABLE
  VALUES('ALPHA',NEXT VALUE FOR MYLIB.BY_TEN),
        ('BETA',NEXT VALUE FOR MYLIB.BY_TEN),
        ('GAMMA',NEXT VALUE FOR MYLIB.BY_TEN),
        ('DELTA',NEXT VALUE FOR MYLIB.BY_TEN)

The SEQNBR has been given the next sequence number generated by the Sequence.

TESTCOL  SEQNBR
-------  ------
ALPHA        10
BETA         20
GAMMA        30
DELTA        40

 

What was the last used value in the Sequence?

This is the first question I thought of when I started "playing" with Sequences. Many other databases have a column in their SEQUENCES that contains the "Current Value" (last used) sequence number. Alas, Db2 for i does not have such a column.

If anyone from IBM is reading this the addition of the "Current Value" column would be very useful.

The only I found to do this is using the SELECT PREVIOUS VALUE as in:

SELECT PREVIOUS VALUE FOR MYLIB.BY_THREE
  FROM SYSIBM.SYSDUMMY1

When I run this statement I get an error message, SQL0845 or SQL code -845. The kernel of the error is:

A NEXT VALUE expression must be evaluated before a PREVIOUS VALUE 
expression can be used.

As the name suggests NEXT VALUE gets the next sequence number from the Sequence.

SELECT NEXT VALUE FOR MYLIB.BY_THREE AS "Next"
  FROM SYSIBM.SYSDUMMY1

The result is:

Next
---- 
  30

When I run the SELECT NEXT VALUE again the next sequence number is retrieved:

Next
---- 
  33

Now I can run the SELECT PREVIOUS VALUE and get a result without an error.

SELECT PREVIOUS VALUE FOR MYLIB.BY_THREE AS "Last seq"
  FROM SYSIBM.SYSDUMMY1

Last seq
-------- 
  33

 

How would I use the sequence numbers from a Sequence?

If I wanted want to give each row in a table a unique number I would NOT use a Sequence to generate that number. I would define one of the columns in the table as an Identity column and let Db2 do all the work in determining the next number to use.

If I needed to create a temporary/transitory sequential number in a "work" table that, I could then use to uniquely identify the data within or sort using the sequence numbers generated, I would use a Sequence to generate unique number. I would restrict the scope of using the numbers from a Sequence to the same job. If I need the scope to go beyond this job then I would use an Identity column.

 

You can learn more about the SEQUENCES view from the IBM website here.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.

1 comment:

  1. We use sequences where we want an ID field to be unique across multiple files.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.