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.