Showcase Article - Open Query Files

The AS/400 has such a robust operating system that programmers often get to choose among several approaches to solving problems. In the case of sequencing data, four different approaches come to mind:

1. Create a logical file to read the data in the desired sequence.
   
2. Use Query/400 to sort the data and create a copy of the original data in a new file.
   
3. Use the CL command, FMTDTA (format data) to sort the data and create a copy of the original data in a new file.
   
4. Use the CL command OPNQRYF to read the data in the desired sequence.
   

The first approach, creating a logical file, is certainly effective. But each logical file that you create must be maintained by the operating system each time a record is added or deleted to the file. So, if you need the data resequenced only when a certain report is requested, you will be adding unecessary overhead to the system.

The second approach, Query/400, works well enough but creates a complete copy of the original data. This makes it unnecessarily slow and requires extra disk space to run. The third approach, FMTDTA, is rarely used. Like Query/400 it creates a complete copy of the original data.

The Open Query File command. OPNQRYF, is a lot like creating a temporary logical file that disappears when the job is done. It not only resequences the data but can easily select a subset of the records. It resembles Structured Query Language (SQL) in structure. With it you can join records from different files, group records together and even calculate new field values.

Consider a store sales evaluation system. Your user would like to list the sales in different sequences. Also, the user sometimes wants to see the stores in only one state. Open Query File is a great way to change the order of the data and select only certain records.

First, look at the very straightforward use of OPNQRYF to order the records by sales. That is, list the store with the most sales first. There are five steps to using OPNQRYF:

1. Share the data path that you are building with the rest of the job
   
2. Run the OPNQRYF command
   
3. Call the program that uses the data from the OPNQRYF command
   
4. Close the file
   
5. Delete the override that you set up in step 1
   

It's not quite as messy as it sounds. You can see the five steps in Figure 1. The first step is always to use an override database file command, OVRDBF, to open the file with the "SHARE(*YES)" option. Without this, the program will not use the new data path that is created with the OPNQRYF command.

The next step in Figure 1 is the OPNQRYF command. Here, I am creating a new data path to the store sales file named "SLS". Since I want to order the data by monthly sales, "SLMNTH", I have used "SLMNTH" as the value for the "KEYFLD" parameter. Also, I want the largest sales first, so I used the "*DESCEND" option.

Now, when the program calls the RPG program to print the report, it will read the "SLS" file using this newly created data path. The program finishes with the housekeeping tasks of closing the file and deleting the override.

Figure 2 is a CL program that will order the data by store number and print only stores in one state. The state is specified as a parameter. Running the command, CALL SLSC101 'TX' will call this program and print the stores in Texas in order by store number.

In Figure 2, notice the parameter "STATE" in the first line. This tells the program that a parameter will be used when the program is called. The "DCL" statement tells the program that the parameter will be a 2 byte field.

The "OPNQRYF" statement will use the "QRYSLT" parameter to select only records for stores in Texas. If I always wanted the records for Texas I would simply code the parameter as: QRYSLT('SLSTAT = "TX" ').

Since I want the user to specify the state as a parameter, I have added a statement to the CL program to build the value for the "QRYSLT". I declared a variable named "QSELECT" and gave it the value needed to select only records for the value used in the CALL statement.

Once you understand these simple examples, you can easily write a program to prompt the user for the desired sequence and state. That program would can call a CL program similar to the one in Figure 2 which will sort and select to right records.

These two examples will get you started with the power and flexiblity of Open Query File.

Figure 1 - This CL program uses Open Query File to order data by sales.

0001.00       PGM
0002.00
0003.00 /* Use OPNQRYF (Open Query File) to sequence store sales in  */
0004.00 /* descending order                                          */
0005.00
0006.00 /* 1st step is to share the data path with rest of job       */
0007.00       OVRDBF     FILE(SLS)  SHARE(*YES)
0008.00
0009.00 /* 2nd step is the OPNQRYF command to build new path to SLS  */
0010.00 /* using monthly store sales in descending order             */
0011.00       OPNQRYF    File((SLS))  KEYFLD((SLMNTH *DESCEND))

0012.00
0013.00 /* 3rd step is to call the RPG program that lists SLS file   */
0014.00       CALL       PGM(SLSR100)
0015.00
0016.00 /* 4th step is to close the file created in step 1           */
0017.00       CLOF       OPNID(SLS)
0018.00
0019.00 /* 5th step is to delete the override from step 2            */
0020.00       DLTOVR     FILE(SLS)


Figure 2 - This Open Query File CL uses a parameter to select data from only one 
state. 
 
0001.00       PGM        PARM(&STATE)
0002.00
0003.00       DCL        VAR(&STATE)   TYPE(*CHAR) LEN(2)
0004.00       DCL        VAR(&QSELECT) TYPE(*CHAR) LEN(2000)
0005.00
0006.00 /* Build a string in QSELECT to use as the QSLT parameter           */
0007.00 /* to select only the records for the state used in the CALL        */ 
0008.00 /* it will build a value like SLSTAT = "TX"                         */
0009.00       CHGVAR     VAR(&QSELECT)
0010.00                  VALUE('SLSTAT = "' *CAT &STATE *CAT '"')
0011.00
0012.00       OVRDBF     FILE(SLS) SHARE(*YES)           
0013.00
0014.00 /* Order by SLSTOR (store#) - select only records for right STATE   */
0015.00       OPNQRYF    FILE((SLS)) QRYSLT(&QSELECT) KEYFLD((SLSTOR))  
0016.00
0017.00       CALL       PGM(SLSR100)
0018.00
0019.00       CLOF       OPNID(SLS)
0020.00
0021.00       DLTOVR     FILE(SLS)