In my last post about the various way I could perform file I/O in CL code I deliberately omitted mention of the Open Query file command, OPNQRYF, as I wanted to cover it in its own post. I am sure everyone who has been programming on the IBM i or its predecessors have encountered and used this command.
I used it too, but not anymore. In the days before embedded SQL become efficient and fast within RPG programs I would often use OPNQRYF. This command allowed me to select fields and records, sort the data in a different order to the files' access paths, etc. thereby reducing the amount of checking and processing the following RPG program would need to perform. But it was always slow as it used the Classic Query Engine, CQE, to access the IBM i's database. In version 7.2 OPNQRYF was moved from using CQE to the faster and more efficient, SQL Query Engine, SQE. While this did improve its speed and performance it is still more efficient, and in my opinion easier and clearer, to do the equivalent using SQL embedded in RPG.
I am going to be giving examples of how to use OPNQRYF to do some of the things I talked about in yesterday's post.
Before I get started I need to show the file I will be working with. It should come as no surprise to regular readers that it is called TESTFILE.
01 A R TESTFILER 02 A F1 2A 03 A F2 2A 04 A K F1 F1 F2 1 5 2 4 3 3 4 2 5 1 |
I will be giving three example programs:
- Equivalent of RPG's SETLL operation
- Equivalent of RPG's CHAIN operation
- Copying data from OPNQRYF to another file
Equivalent of RPG's SETLL operation
In this example I want to read all the records in TESTFILE where the value in the field F2 is greater or equal to '3'. F2 is not a key field of TESTFILE, but I will define it as such in the OPNQRYF statement.
01 PGM
02 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1')
03 DCLF FILE(TESTFILE)
04 OVRDBF FILE(TESTFILE) SHARE(*YES)
05 OPNQRYF FILE((TESTFILE)) +
06 QRYSLT('F2 >= 3') +
07 KEYFLD((F2 *ASCEND))
08 DOWHILE COND(&LOOP)
09 RCVF
10 MONMSG MSGID(CPF0864) EXEC(LEAVE)
11 SNDMSG MSG('F2 = ' || &F2) TOUSR(*REQUESTER)
12 ENDDO
13 CLOF OPNID(TESTFILE)
14 ENDPGM
|
Line 2: This is the variable I will be using to control the Do loop in this program.
Line 3: This is the definition of the file.
line 4: For an OPNQRYF to work I must share the open data path of the file I will be opening.
Lines 5 – 7: This is the Open Query file command. On line 5 I give the name of the file I want to open. Line 6 is selection criteria, in this example where F2 is greater or equal to 3. Line 7 is the definition of the key field that will be used, in this case the file be sorted by F2 in ascending order.
Line 8: The start of my Do loop. As this is a DOWHILE it will be performed until the indicator variable &LOOP is no longer on.
Line 9: The RCVF command CL's read. As there is only one file defined I don't have to give its name.
Line 10: The error CPF0864 occurs when the end of file is reached. When it is I want to exit the Do loop, which I do using a LEAVE command.
Line 11: What I am doing here is the equivalent of me using a DSPLY operation in RPG, it is just a way to see what the value of the field F2 is. The two pipe characters ( || ) are the concatenation characters used to concatenate my string, 'F2 = ', and the value in F2.
Line 13: As OPNQRYF opens the file I have to explicitly close it using the Close File command, CLOF.
Having compiled this program when I run I can see that the records read are the ones I wanted, and they are in the desired order.
From . . : SIMON DD/DD/DD TT:TT:TT F2 = 3 From . . : SIMON DD/DD/DD TT:TT:TT F2 = 4 From . . : SIMON DD/DD/DD TT:TT:TT F2 = 5 |
Equivalent of RPG's CHAIN operation
I do not have make many changes to the OPNQRYF command from previous example to have it do the same as RPG's CHAIN. I am going to use a variable in the Query Select parameter, QRYSLT, to show you can build selections from variables passed to your program.
01 PGM
02 DCL VAR(&ERROR) TYPE(*LGL)
03 DCL VAR(&SEARCH) TYPE(*CHAR) LEN(1)
04 DCL VAR(&TEXT) TYPE(*CHAR) LEN(30)
05 DCLF FILE(TESTFILE)
06 CHGVAR VAR(&SEARCH) VALUE('3')
07 OVRDBF FILE(TESTFILE) SHARE(*YES)
08 CHGVAR VAR(&TEXT) VALUE('F2 = ''' || &SEARCH || '''')
09 OPNQRYF FILE((TESTFILE)) QRYSLT(&TEXT)
10 RCVF
11 MONMSG MSGID(CPF0864) +
EXEC(CHGVAR VAR(&ERROR) VALUE('1'))
12 CLOF OPNID(TESTFILE)
13 IF COND(&ERROR) THEN(+
CHGVAR VAR(&TEXT) VALUE(' was not found'))
14 ELSE CMD(+
CHGVAR VAR(&TEXT) VALUE(' was found'))
15 SNDMSG MSG('Record with F2 = ' || &SEARCH || &TEXT) +
TOUSR(*REQUESTER)
16 ENDPGM
|
Line 6: I am going to use the variable &SEARCH to contain the unique character of the record I am going to retrieve.
Line 8: This is where I build the query selection string, concatenating a string to the contents of a variable.
Line 9: The OPNQRYF opens the file positioning the file pointer at the place where F2 is equal to 3.
Line 10: I "read” the file to get the record.
Line 11: If end of file is encountered, there is no matching record, CPF0864 happens and I want to set on the logical variable (indicator) &ERROR.
Line 12: I close the opened file.
Lines 13 and 14: Depending upon whether &ERROR is on I move a value to the field &TEXT, which I will using for a message.
Line 15: Now I send a message telling me if the "chain” was successful or not.
With the above example it is and I would see:
From . . : SIMON DD/DD/DD TT:TT:TT Record with F2 = 3 was found |
If I change line 6 of the program to be:
06 CHGVAR VAR(&SEARCH) VALUE('x')
|
When the OPNQRYF is execute no records are selected and the program will inform me:
From . . : SIMON DD/DD/DD TT:TT:TT Record with F2 = x was not found |
Equivalent Copying data from OPNQRYF to another file
it is possible to join files together using OPNQRYF. I have found that doing this is a resource hog, therefore, when I have joined I copy the data from the result set into a work file, and work with the result set in the work file. There is a specific command to copy data from the OPNQRYF result set, Copy From Query File, CPYFRMQRYF. In this example I am going to join the file I have been using, TESTFILE, to another, TESTFILE1. TESTFILE1 looks like this:
01 A R TESTFILE1R 02 A F2 R REFFLD(F2 TESTFILE) 03 A F3 R REFFLD(F2 *SRC) F2 F3 1 A 3 B 5 C 7 D |
I want to all the data from both files that match into a work file in QTEMP, called @TESTFILE.
01 PGM 02 DLTF FILE(QTEMP/@TESTFILE) 03 MONMSG MSGID(CPF2105) 04 OVRDBF FILE(TESTFILE) SHARE(*YES) 05 OVRDBF FILE(TESTFILE1) SHARE(*YES) 06 OPNQRYF FILE((TESTFILE) (TESTFILE1)) + 07 FORMAT(JOINED) + 08 JFLD((TESTFILE/F2 TESTFILE1/F2)) + 09 MAPFLD((F2 'TESTFILE/F2') + 10 (F2A 'TESTFILE1/F2')) + 11 OPNID(ID1) 12 CPYFRMQRYF FROMOPNID(ID1) TOFILE(QTEMP/@TESTFILE) + 13 MBROPT(*ADD) CRTFILE(*YES) 14 CLOF OPNID(ID1) 15 ENDPGM |
Lines 4 and 5: As I will be using two files I have to override them both with SHARE(*YES).
Lines 6 – 11: This is the OPNQRYF statement to join the two files.
Line 6: These are the files that will be joined.
Line 7: This is one thing I do not like with OPNQRYF. As I am joining files I have give a name of a file in the FORMAT keyword that will match the results. This is the DDS for the file JOINED I had to create to be the definition for the result set.
A R JOINEDR A F1 R REFFLD(F1 TESTFILE) A F2 R REFFLD(F2 TESTFILE) A F2A R REFFLD(F2 TESTFILE1) A F3 R REFFLD(F3 TESTFILE1) |
This is one of the many reasons I prefer using SQL as when I create a table "on the fly” I can do so without needing a file to act as a template for the output.
Line 8: This is the join criteria, which in this case the results will be linked by the values in F2.
Lines 9 and 10: I cannot have two fields with the same name in my results, therefore, I need to map the fields with the same name to different names. In this example I map the F2 field in TESTFILE to the name F2 in the results, and F2 in TESTFILE1 to F2A.
Line 11: In the previous examples I have used the default open id, which will be the name of file. As I am joining files here I need to give the results a unique name, ID1.
Lines 12 and 13: This is the command to copy the data from the open id ID1 into the file @TESTFILE in QTEMP, I want to create the file, and then add records to it.
Line 14: I close the open id, not the files.
When I look in the work file, QTEMP/@TESTFILE, I see:
F1 F2 F2A F3 5 1 1 A 3 3 3 B 1 5 5 C |
In my own work I would not code this example as an OPNQRYF. I would do the equivalent in SQL:
01 PGM
02 DLTF FILE(QTEMP/@TESTFILE)
03 MONMSG MSGID(CPF2105)
04 RUNSQL SQL('CREATE TABLE QTEMP.@TESTFILE +
(F1,F2,F2A,F3) AS +
(SELECT A.F1,A.F2,B.F2,B.F3 +
FROM TESTFILE A +
INNER JOIN TESTFILE1 B +
ON A.F2 = B.F2) +
WITH DATA') COMMIT(*NC)
05 ENDPGM
|
I hope that this and the previous post have given you insight into how you can perform the various types of file I/O in CL.
You can learn more about this from the IBM website:
This article was written for IBM i 7.3, and should work for earlier releases too.
I hate OPNQRYF!! Total pain to try and debug, only thing IBM invented that is worse is RLU! LOL
ReplyDeleteI whole hardheartedly agree about RLU!
DeleteBy today’s standards, OPNQRYF is lacking, but in its time, it was wonderful. It sure beat what we had before, which was nothing.
DeleteThe biggest complaint I have heard about OPNQRYF was the use of apostrophes in QRYSLT expressions. This criticism is unwarranted. The need to double apostrophes within a character literal was not a defect in OPNQRYF, but a general trait of CL character expressions that anyone who writes CL code should understand. OPNQRYF made this task even easier by allowing quotation marks (“double quotes”) in the QRYSLT parameter.
I rarely use OPNQRYF any more, especially since IBM added the RUNSQL command, but I’m grateful it was there to help me accomplish so many tasks over the years.
Ted; I completely agree with what you have said.
DeleteI read your blog regularly and benefit from it greatly. So thank you for that. However you must be the only one in the past ten years to write an article about OPNQRYF. On second thought make that twenty..
ReplyDeleteI agree, there has not been much said about using OPNQRYF for a long time. But most of those stories focused on using it with RPG.
DeleteIn this case, performing file I/O in CL, OPNQRYF is simple way to assist with various kinds of file I/O which is, in my opinion, more complicated when not using it.
I agree with Simon. Since returning values from a database via SQL in CL is clumsy at best, OPNQRYF can be a good tool.
ReplyDeleteWhy have 2 syntaxes: one for CL and one for RPG?
ReplyDeleteI would suggest to IBM to unify both languages.
Add all CL functionality to RPG, and You have one excellent one. No need to jump to a CL PGM for just a few lines of code !
The major CL function RPG is missing is the ability to execute commands directly. That need to be added to the RPG language.
If that is done, more resources could be used for something more productive, both from the IBM team and the RPG community.
It could be done by using EXEC-CL, similar to EXEC-SQL, or mayb using a certain sign that will make it a CL command.
An example how it would look like:
!CPYF FROMFILE(&FROMFILE) TOFILE(QTEMP/&TOFILE) CRTFILE(*YES)
!RTVSYSVAL SYSVAL(QSRLNBR) RTNVAR(&SRLNBR)
or use ':' for variables, like in SQL.
RTVSYSVAL SYSVAL(QSRLNBR) RTNVAR(:SRLNBR)
OPNQRYF is great with native IO in RPG. You can filter the data easily by building the QRYSLT string (not any harder than building a Select/Where for SQL). imho, the HUGE advantage that OPNQRYF has over SQL, is that with the Share Open Path (SHARE(*YES)), you can call another program and access the same filtered/sorted file without rebuilding the entire Select/Where string (cant share an open data path in SQL...) Lets say you show a subfile with filtered/sorted data based on user criteria (OPNQRYF used), then want a printed output...just call the print program and boom! magically the list is filtered/sorted as shown on the subfile. Thanks to OPNQRYF!
ReplyDeleteOpnqryf is a dog for it’s intended use (except for setll and chains).
ReplyDeleteBut it is a great secret weapon with at rest db2 encryption.
working on IBM i since 6 years and never used this OPNQRYF... It is ugly and useless if it was important then I would've used it. USE THE LOGICAL FILE.
ReplyDeleteCan anyone please tell me a case where OPNQRYF must be used and no other alternate available.
The only place where I would ever consider using an OPNQRYF is in the examples above.
DeleteSQL is a far better alternative.
For the people that think OPNQRYF is bad, I remember converting FMTDTA (Format Data) to OPNQRYF statements 30 years ago
DeleteI can remember converting #GSORT to FMTDTA.
DeleteTimes and tools change for the better. Thank goodness we don't have to use those, or OPNQRYF, any more.