More about SQL Sequences

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.