Wednesday, November 6, 2019

Example of why using SQL can be better than RPG

using sql instead of rpg chain operation to cope with a change in the file key

Here is another example of using SQL can make your life easier, coping with a change of the key in file without having to change the program.

At work we are gradually moving all of the US based IBM i partitions from a PowerSystems model 720 to a brand new model 900. I always thought PowerSystems model 900s could only run IBM i 7.3 and 7.4, it turns out that it will also run IBM i 7.2 but a whole load of PTFs have to be loaded and applied when moving from an older model (that was probably not up to date with PTFs too). The moves have been pretty uneventful, apart a change that makes a process I wrote many years ago fail.

The file QA1ALG in library QUSRBRM has changed, a new timestamp field has been added and that is used to replace the existing date and time fields in the file's key:

Model 720 IBM i 7.2 Model 900 IBM i 7.2
File: QA1ALG
Library: QUSRBRM

Field
----------
LGMDTA
LGDATE
LGTIME
LGSEV
LGPGM
LGUSER
LGJOB
LGJNBR
LGMID
LGAREA
LGFILE
LGLIB
LGARE2
LGCGNAM


Key
No.    Key
---    ------
  1    LGDATE
  2    LGTIME
  3    LGMID
  4    LGUSER
  5    LGJOB
  6    LGJNBR
File: QA1ALG
Library: QUSRBRM

Field
----------
LGMDTA
LGDATE
LGTIME
LGSEV
LGPGM
LGUSER
LGJOB
LGJNBR
LGMID
LGAREA
LGFILE
LGLIB
LGARE2
LGCGNAM
LGTIMESTMP

Key
No.    Key
---    ----------
  1    LGTIMESTMP
  2    LGMID
  3    LGUSER
  4    LGJOB
  5    LGJNBR

This is causing a program I wrote many years ago to error. I used a RPG Chain operation code to get a record from the file, and as the key has been changed the program now errors.

chain (LGDATE:LGTIME:LGMID:LGUSER:LGJOB:LGJNBR) QA1ALGR ;

"Big deal!" I can hear some of you say. "When you move one partition from the old to the new server just change the Chain statement to use the new key":

chain (LGTIMESTMP:LGMID:LGUSER:LGJOB:LGJNBR) QA1ALGR ;

I don't want to have two versions of the program, and to copy the new program to each partition after it is moved to the new server. In my opinion it would just be easier if I could have one program, I could put it in all of the partitions now, and it would work with both releases' version of the file. It also removes the danger of me forgetting to put the new program into the partition after it is moved.

So how to do that? This is where SQL comes to the rescue!

As the two fields that were the original key have not been removed from the file I can use them in a SQL Select statement to fetch the information I desire:

wkFlag = ' ' ;

exec sql SELECT LGMDTA,'1' INTO :wkLogText,:wkFlag
           FROM QUSRBRM.QA1ALG
          WHERE LGMID = :LGMID
            AND LGUSER = :LGUSER
            AND LGJOB = :LGJOB
            AND LGJNBR = :LGJNBR
            AND LGDATE = :LGDATE
            AND LGTIME = :LGTIME
          LIMIT 1 ;

if (wkFlag = ' ') ;

The advantage of doing it this way is that the new program will now work with both versions of the file. And I can move this new version of the program to all of the partitions today!

 

This article was written for IBM i 7.3.

3 comments:

  1. I use SQL to query out BRMS data daily. The QUSRBRM / QA1AHS file and it's LF's make quick work of extracting data. I keep a save SQL in ACS - Run SQL Scripts and adjust it as needed.
    I can then use it on multiple partitions as the need presents itself.

    ReplyDelete
  2. All my programs are in RPG, since not everyone can understand and maintain SQL stored procedures. However I have stopped using native I/O long time back, and use embedded SQL. It helps me organize my program into small chunks.

    SQL performance is noticeable when we create and optimize indexes, instead of using logical files.

    ReplyDelete
  3. I prefer SQL over RPG, they both have inherit strengths and weaknesses. Personally I have encountered a situation where the only solution that would work was to code this particular module as an SQLRPGILE program. This SQLRPGILE program was callable as a stored procedure from JavaScript within an HTML page.

    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.