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

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.