Using multiple SQL Sequences to update different record types

There is a file, ACCTFILE, with a unique key of two fields:

1. SEQUENCE:  A unique sequence number
2. ACCTYPE:  Account type code

01 A UNIQUE 02 A R ACCTFILER 03 A SEQUENCE 6P 0 04 A ACCTYPE 2A 05 A K SEQUENCE 06 A K ACCTYPE

The Sequence is incremented every time a record is written to the file, i.e. there is only one record with each Sequence number. The Sequence is now in the 800,000s, and the concern is what will happen when happen when 999,999 + 1 happens?

This is when they asked me for advice.

Fortunately the application that uses file always access the file with both key field, therefore, the same Sequence number can be used for more than one Account type.

While the programmer changed the program that adds records to this file to determine the next Sequence for each Account type. I was asked what would be a "quick and easy" way to change all the existing Sequence numbers to be unique by Account type.

I am sure you will not be surprised that my solution is to use SQL. In my opinion this would be a good scenario for using SQL Sequences. A SQL Sequence will return to me an incremental value based on its starting and increment values.

My example file, DDS is shown above, contains three account types:

SELECT SEQUENCE,ACCTYPE FROM MYLIB.ACCTFILE SEQUENCE ACCTYPE -------- ------- 10 AA 20 AB 30 AA 40 AC 50 AB 60 AC 70 AB 80 AA 90 AA 100 AB

This is so simple I can do all of it in ACS "Run SQL scripts".

I first need to create my Sequences, one for each account type:

01 CREATE SEQUENCE MYLIB.SEQUENCE_AA START WITH 1 INCREMENT BY 1 ; 02 CREATE SEQUENCE MYLIB.SEQUENCE_AB START WITH 1 INCREMENT BY 1 ; 03 CREATE SEQUENCE MYLIB.SEQUENCE_AC START WITH 1 INCREMENT BY 1 ;

All three of these sequences will start at 1, and will increment by 1 each time it is used. I wanted to start with zero, but the file's owner did not like that idea.

I built three Update statements to update the Sequence number for each Account type:

04 UPDATE MYLIB.ACCTFILE 05 SET SEQUENCE = NEXT VALUE FOR MYLIB.SEQUENCE_AA 06 WHERE ACCTYPE = 'AA' ; 07 UPDATE MYLIB.ACCTFILE 08 SET SEQUENCE = NEXT VALUE FOR MYLIB.SEQUENCE_AB 09 WHERE ACCTYPE = 'AB' ; 10 UPDATE MYLIB.ACCTFILE 11 SET SEQUENCE = NEXT VALUE FOR MYLIB.SEQUENCE_AC 12 WHERE ACCTYPE = 'AC' ;

The three statements are almost identical. The only difference being which Sequence they use, lines 5. 8 and 11, and the record selection, lines 6, 9, and 12. I could just execute each statement (Crtl+R) in turn to update the file.

The results are as expected, each Account type has its own unique Sequence numbers.

SELECT ACCTYPE,SEQUENCE FROM MYLIB.ACCTFILE ORDER BY ACCTYPE ACCTYPE SEQUENCE ------- -------- AA 1 AA 2 AA 3 AA 4 AB 1 AB 2 AB 3 AB 4 AC 1 AC 2

I did try and come up with a statement that would do it all, but no matter what I tried I received an error. This was my favorite failure:

UPDATE MYLIB.ACCTFILE SET SEQUENCE = CASE WHEN ACCTYPE = 'AA' THEN NEXT VALUE FOR MYLIB.SEQUENCE_AA WHEN ACCTYPE = 'AB' THEN NEXT VALUE FOR MYLIB.SEQUENCE_AB WHEN ACCTYPE = 'AC' THEN NEXT VALUE FOR MYLIB.SEQUENCE_AC END ;

If you know a way to update all three account types in one statement please put your suggestion in the comments, below.

As I no longer need the Sequences I delete them.

13 DROP SEQUENCE MYLIB.SEQUENCE_AA ; 14 DROP SEQUENCE MYLIB.SEQUENCE_AB ; 15 DROP SEQUENCE MYLIB.SEQUENCE_AC ;

As you can imagine I had all of these statements written and executed in a fraction of the time I would have spent writing a RPG program to do the same thing. This came as a surprise to the programmer who had estimated it would have taken him this morning to write the program and run it.

This is another good example of there are times when using SQL will make your life simpler, and easier, than writing RPG programs to do the same.

 

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