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.
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.
ReplyDeleteI can then use it on multiple partitions as the need presents itself.
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.
ReplyDeleteSQL performance is noticeable when we create and optimize indexes, instead of using logical files.
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