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))
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online