One of the strengths of OS/400 is its integrated relational database. Although the database has been given a name in V3R1 (DB2/400 or DB2 for OS/400), it has been part of the operating system since the days of the S/38. Just as the database is part of OS/400, support for SQL is an integrated part of the database. OS/400 and IBM's licensed program products offer many different ways to access the SQL engine, all with the same general goal?to select and sort database information.

This article explores one of the most widely used and most misunderstood methods?the Open Query File (OPNQRYF) CL command. OPNQRYF is part of the base operating system (unlike some other SQL access tools, such as SQL/400). This makes it a very cost-effective way to sort and select data. In theory, OPNQRYF should be easy for AS/400 programmers to master because it takes advantage of the familiar CL interface. Unfortunately, in the translation from SQL syntax to CL syntax, OPNQRYF became one of the least intuitive commands ever designed. An understanding of what OPNQRYF is designed to do and which parameters are really relevant is essential to your ability to take full advantage of this powerful command.

OPNQRYF is an extension of CL, which allows us to build Open Data Paths (ODPs) using OS/400's Query Optimizer. Properly used, OPNQRYF lets you simplify high-level language (HLL) programs, such as RPG or COBOL. OPNQRYF performs the sorting and record selection while the HLL program performs calculations and file updates.

Where OPNQRYF Came From

OPNQRYF originated on the S/38 as a way to dynamically sort, select, and join data. When OS/400 was invented as the successor to the S/38's operating system, the underlying OS code of OPNQRYF was redesigned to use OS/400's integrated Query Optimizer. The Query Optimizer is a facility that finds and builds fast access paths across the database. So transparent was this implementation using the Query Optimizer that many S/38 programmers assumed that OPNQRYF was innately different from other Query facilities. Was one method faster than another? Which one used more AS/400 resources?

In actuality, as 1 shows, OPNQRYF uses the same routines to get access to the DB2/400 database. In other words, OPNQRYF is like all the other Query implementations on the AS/400: it's a front-end to the services and resources of the AS/400's Query Optimizer.

In actuality, as Figure 1 shows, OPNQRYF uses the same routines to get access to the DB2/400 database. In other words, OPNQRYF is like all the other Query implementations on the AS/400: it's a front-end to the services and resources of the AS/400's Query Optimizer.

So, why use OPNQRYF instead of a different Query implementation? Well, first of all, it's a robust extension of Query into the CL syntax. This provides the power to select records, sort data, and join files within CL programs. Second, OPNQRYF provides you with complete prompting support throughout OS/400.

This article focuses on the elements of OPNQRYF and how this facility builds upon the underlying strengths of the Query Optimizer. In it, we'll walk through the steps of building a simple OPNQRYF and give some pointers on ways to streamline your program development.

In our simple example, we'll focus on building an OPNQRYF that takes a Vendor History (VNDRHIST) database file, selects a single Vendor Number (VNDRNO), and accesses those history records in Purchase Date (PDTE) order.

Open Data Path?The Virtual Relational Database

Records in a relational database are physically stored in arrival sequence. Unless the database has a keyed sequence, grouping records in a logical manner, programmers must constantly create new sorted instances of the database. This problem is resolved in OS/400 with ODPs, and ODPs are used extensively by OPNQRYF.

An ODP is a virtual pathway between individual records within a relational database, as well as between individual database files. It's a map of record pointers, maintained within memory, to access requested files, fields, and records. It is exactly this pathway that the OPNQRYF command creates.

The first step, then, in executing an OPNQRYF command is to prepare the database files for the creation of an ODP by telling DB2/400 to share the resources of the associated files. This is usually accomplished by the Override Database File (OVRDBF) command. For instance, this is the command for sharing the VNDRHIST file.

 OVRDBF FILE(VNDRHIST) + SHARE(*YES) 

Once this command has been issued, from the command prompt or from within a CL program, the VNDRHIST file is ready for the construction of an ODP.

Constructing the ODP with OPNQRYF

Now, you're ready to actually build the virtual database using OPNQRYF. Typing in OPNQRY and pressing the F4 prompt key will give you a list of parameters that?nine times out of ten?will totally intimidate even the most hardened of programmers. There are six full screens of prompts, each with their own confusing sets of parameters (see 2). Fortunately, there are only a few parameters required to successfully build the ODP, but spotting them through the pages and pages of prompts is difficult.

Now, you're ready to actually build the virtual database using OPNQRYF. Typing in OPNQRY and pressing the F4 prompt key will give you a list of parameters that?nine times out of ten?will totally intimidate even the most hardened of programmers. There are six full screens of prompts, each with their own confusing sets of parameters (see Figure 2). Fortunately, there are only a few parameters required to successfully build the ODP, but spotting them through the pages and pages of prompts is difficult.

Despite the plethora of potential parameters, there are essentially just three basic ones required to construct the ODP: FILE, QRYSLT, and KEYFLD.

FILE obviously identifies the file or files that compose the ODP. QRYSLT identifies the selection criteria of the individual records needed. KEYFLD identifies the order in which the ODP will read the records of the database files.

Here is an example of constructing the ODP with an OPNQRYF statement.

 OPNQRYF FILE(VNDRHIST) + QRYSLT('VNDRNO=30') + KEYFLD((PDTE)) 

In this example, OPNQRYF builds an ODP to the VNDRHIST file, selecting VNDRNO 30 and accessing the history records in PDTE order.

Since OPNQRYF is integrated with OS/400's underlying SQL, if an ODP that matches the OPNQRYF criteria already exists, OS/400 will find it and use it instead of building a new one. Also, since no new instances of VNDRHIST data have been created, there's no possibility that the data you will be using will not be "current." As long as the ODP is active?until you either close it or end the job?you can be sure that the data being referenced will always be up-to-the-minute.

Connecting the HLL Program to the Virtual Database Highway

So now that an ODP has been constructed, what's next? It's time to climb on that virtual database highway and step on the gas pedal. You simply call an HLL program to process the records, like this:

 CALL PGM(MYPGM) 

The only requirement within the HLL is that it must reference the ODP appropriately. In RPG, this means the filename used in the F-spec must reference a file that has the same database format that the ODP is referencing.

Within the HLL program, only those records selected by OPNQRYF will be processed (in our example, those records with a VNDRNO of "30"), and those records will arrive in the order defined by the KEYFLD parameter (PDTE order). There's no other required code within the HLL; it's as if you were accessing a completely separate database of selected records. However, if the file is updated, your HLL program will have immediate access to the new information.

Closing Down the Highway with CLOF

After your HLL program has finished processing the records accessed by the ODP, you will probably want to deconstruct the virtual database highway. Why? Because, if you subsequently run another program that uses the same database file, OS/400 will assume you want to run along that same virtual database highway. Obviously, this could cause you some problems, presenting you with only the records you'd selected and sorted in the OPNQRYF.

To close down the highway, you simply need to close the file. This is accomplished with the Close File (CLOF) command. In our example, the command would look like this:

 CLOF OPENID(VNDRHIST) 

Of course, you don't have to worry about closing the ODP if you sign off the system or end a batch job; OS/400 keeps your unique ODP open only as long as the job that opened it is running. However, if you don't close the ODP after you're finished, OS/400 will expend its resources to maintain it, so it's common practice to always use a CLOF after the call to the HLL.

Restoring DB2/400

The final step in deconstructing the ODP is to inform DB2/400 that it no longer needs to share the resources of the particular database file. This is accomplished by telling OS/400 to delete the initial OVRDBF. The command to execute this is Delete Override (DLTOVR), and the last statement in our sample CL program looks like this:

 DLTOVR FILE(VNDRHIST) 

The complete step-by-step construction and deconstruction of this ODP is shown in 3.

The complete step-by-step construction and deconstruction of this ODP is shown in Figure 3.

Expanding the Basics

If you think the example we've been using is deceptively simple, you're right! As I said before, the OPNQRYF statement is a CL front-end for the underlying SQL engine of OS/400. Consequently, CL programs that use OPNQRYF require a substantial amount of code to define the OPNQRYF parameters. For the CL programmer, this requires both ingenuity and a firm understanding of CL string variables.

For instance, in our example, we hard-coded the QRYSLT statement to always choose VNDRNO 30. In real life, the program would be much more robust if we could use a CL variable. You might assume that we could use the following statements to accomplish this:

 PGM PARM(&VNDRVAR) DCL VAR(&VNDRVAR) + TYPE(*CHAR) LEN(5) OPNQRYF FILE(VNDRHIST) + QRYSLT('VNDRNO=&VNDRVAR') + KEYFLD((PDTE)) 

Unfortunately, this won't work. Why? Because, when the CL program parses the QRYSLT statement, it will not substitute the value of the variable &VNDRVAR because it considers everything within quotes as a literal.

The solution to this problem?and the technique almost always used by skilled CL programmers?is to build up a string variable of selection criteria within CL and then to pass that entire string along to the SQL compiler as the QRYSLT field.

Consequently, the solution to our above requirement would look like this:

 PGM PARM(&VNDRVAR) DCL VAR(&VNDRVAR) + TYPE(*CHAR) LEN(5) DCL VAR(&QRYSLT) + TYPE(*CHAR) LEN(256) CHGVAR VAR(&QRYSLT) + VALUE('VNDRNO=' *CAT &VNDRVAR) OPNQRYF FILE(VNDRHIST) + QRYSLT(&QRYSLT) KEYFLD((PDTE)) 

As your OPNQRYF skills expand, you'll often find yourself spending a tremendous amount of time fine-tuning QRYSLT statements and KEYFLD statements using this technique. Unfortunately, this effort is not well supported by the prompting capabilities of OS/400. When you press F4 to prompt the QRYSLT parameter, you will see that IBM has not provided any means to interactively build the QRYSLT field as a string. Perhaps some day it will occur to Rochester that this is an area in need of improvement.

Finally, don't neglect to consider all of the other integrated capabilities that OS/400 provides to the OPNQRYF command. Using OPNQRYF functions such as %WLDCRD, %NULL, %MAX, %XLAT, and, of course, the powerful CLfunction %SST adds a tremendous capability to OPNQRYF. Building your ODP using OPNQRYF can be as powerful as any HLL method of accessing data.

OPNQRYF's Unique Contribution to SQL/400

So, should you be using OPNQRYF? Absolutely! It's the CL equivalent of the Swiss army knife for building powerful ODPs to DB2/400. No other SQL tool syntactically integrates as transparently into CL as OPNQRYF, and, since it uses the same underlying SQL resources of OS/400, you can be certain that its power is as consistent and robust as DB2/400 itself.

Thomas M. Stockwell is a senior technical editor for Midrange Computing.

OPNQRYF Demystified

Figure 1: Finding OPNQRYF in the Plethora of AS/400 Query Tools

OPNQRYF Demystified

Figure 2: The Six Prompting Screens of the OPNQRYF Command

OPNQRYF Demystified

Figure 3: The Five Steps to OPNQRYF

Thomas M. Stockwell is an independent IT analyst and writer. He is the former Editor in Chief of MC Press Online and Midrange Computing magazine and has over 20 years of experience as a programmer, systems engineer, IT director, industry analyst, author, speaker, consultant, and editor.  

 

Tom works from his home in the Napa Valley in California. He can be reached at ITincendiary.com.

 

 

Also Read

How to implement enterprise resource planning (ERP)

What Does It Mean To Be an Insights-driven Business? Paving the Way for Organizations Running on IBM i / AS400

Organizing for Data Governance