Dynamic Sequencing with OPNQRYF

Business Intelligence
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Logical files are excellent for interactive processing that requires data file access paths that are always current. However, if you have ever created a logical file solely for the purpose of creating a new data path for a report program, you will be happy to know there is a better method. This article will show you how to use the Open Query File (OPNQRYF) command within an RPG program to create temporary access paths which eliminate the additional overhead required by logical files.

In recent articles (see "The Fastest Sequencing Method," MC, August 1994 and "Lab Notes: Another Hooray for OPNQRYF," MC, September 1994), a series of tests revealed that the fastest method for processing 1,000 or more records is to physically sort the data with OPNQRYF-using the ALWCPYDTA(*OPTIMIZE) parameter-and then process it in arrival sequence.

The AS/400 high-level languages (HLLs) perform better when they read data in arrival sequence. In fact, the time spent putting that data in the appropriate arrival sequence is often negated by the decreased time required to read the records.

When reading a data file by key, the system must first read the key and then go get the physical data. The HLL program must double the I/O processing right off the bat. Data buffering (getting multiple records on every I/O operation) is not as useful as it would be when the file is processed in arrival sequence because the physical data is being accessed in a random fashion. This can lead to a tremendous increase in the amount of system resources that have to be used when the HLL program is run.

There are other disadvantages. Logical files require disk space for the index. They can require that extra I/O be done to maintain the index when the physical file is changed. Even if the file is built to delay the index maintenance, the additional processing must be performed sometime.

Using the OPNQRYF command to create access paths eliminates the disadvantages of logical files. It also offers an important feature logical files cannot- dynamic sequencing of the access path.

Using OPNQRYF Within an RPG Program

The sample program in this article (see 1) is a simple file listing program that will print in customer name or customer number order. There is a parameter that is passed when the RPG program is run that will be used to determine the order in which the records are to be processed. If a "1" is passed to the program, the list will print in customer name sequence. Otherwise, the list will print in customer number order.

The sample program in this article (see Figure 1) is a simple file listing program that will print in customer name or customer number order. There is a parameter that is passed when the RPG program is run that will be used to determine the order in which the records are to be processed. If a "1" is passed to the program, the list will print in customer name sequence. Otherwise, the list will print in customer number order.

For this illustration, the OPNQRYF command has been incorporated directly into the RPG program. This shows that the sequencing can be performed within the program, and additional components are not necessarily required. If the OPNQRYF command you are using is more complicated, it might be advantageous to use a CL program to run OPNQRYF and call the RPG program.

The File Description Specification for the CUST file has "UC" in positions 71 and 72, which tells the system that the program will explicitly control the opening of the CUST file. We will code the program to open and close the file as needed.

The RPG program calls QCMDEXC to execute the required commands. (QCMDEXC is an IBM-supplied program that runs a single command from within an HLL program.) Before we can open the file, we must first tell the system that we want our RPG program and the OPNQRYF command to share the same data path. We do this by using the OVRDBF (Override Data Base File) command.

The next command required is OPNQRYF, which sequences the data. The ALLWCPYDTA(*OPTIMIZE) option is specified to enhance the performance of the OPNQRYF function. We use the CAT operation to concatenate the primary element of the OPNQRYF command to the appropriate KEYFLD parameter. The program determines which KEYFLD to use by the value passed in parameter one (SEQ).

Once the OPNQRYF command has been performed, the CUST file can be opened for use within the RPG program. The access path built by the OPNQRYF command will automatically be used to read the records from the CUST file.

Because the CUST file was opened twice (once by OPNQRYF and once by the RPG program), two close operations are required. The first close (RPG CLOSE operation) is for the RPG program, and the second (CLOF command) is for OPNQRYF.

Create Flexible Sequencing Solutions

If your system has multiple report programs that produce the same output, odds are they are different programs simply because they have different sequence or selection criteria. When output change requests are made, you may be asked to make the same changes to all of them. By dynamically sequencing and selecting your data, you could add a great deal of flexibility to your programs and reduce the amount of maintenance to be performed. (To learn more about dynamic sequencing through OPNQRYF, see "Variable Control Breaks," MC, March 1995.)

The OPNQRYF command is a wonderful tool, and it can be exploited right from your RPG programs as illustrated here. The syntax can be a little cumbersome, and it can take some detective work to find your errors, but the advantages are too beneficial to ignore.

Doug Pence is the founder and Ron Hawkins is the research and development manager of Computer Processing Unlimited, Inc. in San Diego, California.


Dynamic Sequencing with OPNQRYF

Figure 1 Sample RPG Program Using OPNQRYF for Dynamic Sequ

 *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ... FCUST IF E DISK UC FQSYSPRT O F 132 OF PRINTER E ACMD 1 4 80 I 'CLOF OPNID(CUST)' C CLOF C *ENTRY PLIST C PARM SEQ 1 * Override to share C MOVELACMD,1 CMD 80 C Z-ADD80 LEN C CALL 'QCMDEXC' C PARM CMD C PARM LEN 155 C SEQ IFEQ '1' C ACMD,2 CAT ACMD,3:1 CMD P C ELSE C ACMD,2 CAT ACMD,4:1 CMD P C ENDIF * Perform OPNQRYF C CALL 'QCMDEXC' C PARM CMD C PARM LEN * Open file for this program C OPEN CUST C EXCPTHEDING * C *IN50 DOUEQ'1' C READ CUSREC 50 C *IN50 IFEQ *OFF C *INOF IFEQ *ON C EXCPTHEDING C ENDIF C EXCPTDETAIL C ENDIF C ENDDO * Close the file opend by this program C CLOSECUST * Close the file opened by OPNQRYF C CALL 'QCMDEXC' C PARM CLOF CMD C PARM 20 LEN 155 * C SETON LR OQSYSPRT E 202 HEDING O 72 'CUSTOMER LIST' O E 1 HEDING O 15 'CUSTOMER NUMBER' O 40 'CUSTOMER NAME' O EF 1 DETAIL O CUSNO 10 O CUSNAM 55 *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ... ** OVRDBF FILE(CUST) SHARE(*YES) OPNQRYF FILE((CUST)) ALWCPYDTA(*OPTIMIZE) KEYFLD((CUSNAM)) KEYFLD((CUSNO)) 
BLOG COMMENTS POWERED BY DISQUS