Wednesday, October 7, 2020

Using multiple SQL Sequences to update different record types

sql sequence to update file with unique sequence number for each record

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.

5 comments:

  1. Hi.

    I solved that problem using MERGE:



    MERGE INTO MYLIB.ACCTFILE AS T
    USING (SELECT ACCTYPE, SEQUENCE FROM MYLIB.ACCTFILE)
    AS S
    ON (S.ACCTYPE, S.SEQUENCE) = (T.ACCTYPE, T.SEQUENCE)
    WHEN MATCHED AND T.ACCTYPE='AA'
    THEN UPDATE SET SEQUENCE = NEXT VALUE FOR SEQUENCE_AA
    WHEN MATCHED AND T.ACCTYPE='AB'
    THEN UPDATE SET SEQUENCE = NEXT VALUE FOR SEQUENCE_AB
    WHEN MATCHED AND T.ACCTYPE='AC'
    THEN UPDATE SET SEQUENCE = NEXT VALUE FOR SEQUENCE_AC
    ;


    Not nice, but it works in just one sentence.

    ReplyDelete
  2. ...and the concern is what will happen when happen when 999,999 + 1 happens?

    the problem remains, it is just delayed

    ReplyDelete
    Replies
    1. You are correct. But as they do not need to retain data that is more than 7 years old they can purge the older data. When 999999 + 1 = 0 then there will not be a duplicate key error.

      They say "never" access the physical file, which has just those two key fields. They use a logical file that includes the "post date" in the key. I told them to check all their programs, and be 100% sure that is true.

      Delete
  3. I'm out of work at the moment so don't have a machine to try this on but isn't your new sequence number simply the row number of your file ordered by Acctype/Seq and partitioned by Acctype - the update will be a single statement and you won't have to hard code any acctypes

    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.