AS/400-iSeries Starter Kit

In this chapter, I give you the foundation you need to use the OPNQRYF (Open Query File) command, and then I leave you to discover the rewards as you apply this knowledge to your own applications.

OPNQRYF's basic function is to open one or more database files and present records in response to a query request. Once opened, the resulting file or files appear to high-level language (HLL) programs as a single database file containing only the records that satisfy query selection criteria. In essence, OPNQRYF works as a filter that determines the way your programs see the file or files being opened. You can use the OPNQRYF command to perform a variety of database functions: joining records from more than one file, grouping records, performing aggregate calculations such as sum and average, selecting records before or after grouping, sorting records by one or more key fields, and calculating new fields using numeric or character string operations.

One crucial point to remember when using OPNQRYF is that you must use the SHARE(*YES) file attribute for each file opened by the OPNQRYF command. When you specify SHARE(*YES), subsequent opens of the same file will share the original open data path and thus see the file as presented by the OPNQRYF process. If OPNQRYF opens a file using the SHARE(*NO) attribute, the next open of the file will not use the open data path created by the OPNQRYF command, but instead will perform another full open of the file.

Don't assume the file description already has the SHARE(*YES) value when you use the OPNQRYF command. Instead, always use the OVRDBF (Override with Database File) command just before executing OPNQRYF to explicitly specify SHARE(*YES) for each file to be opened. Be aware that the OPNQRYF command ignores any parameters on the OVRDBF command other than TOFILE, MBR, LVLCHK, WAITRCD, SEQONLY, INHWRT, and SHARE.

The Command

Figure 28.1 shows the entire OPNQRYF command. OPNQRYF has five major groups of parameters (specifications for file, format, key field, join field, and mapped field) and a few extra parameters not in a group. Using the OPNQRYF command is easier once you master the parameter groups.

There are some strong, but awkwardly structured, parallels between OPNQRYF parameters and specific SQL concepts. For instance, the file and format specifications parallel the more basic functions of the SQL SELECT and FROM statements; the query selection expression parallels SQL's WHERE statement; the key field specifications parallel SQL's ORDER BY statement; and the grouping field names expression parallels the GROUP BY statement. If you compare OPNQRYF to SQL (page 351), you'll see that the OPNQRYF command is basically a complicated SQL front end that offers a few extra parameters.

Start with a File and a Format

For every query, there must be data -- and for data, there must be a file. OPNQRYF's file specifications parameters identify the file or files that contain the data. A simple OPNQRYF command might name a single file, like this:

OPNQRYF FILE(MYLIB/MYFILE) ...

This partial command identifies MYLIB/MYFILE as the file to be queried. Notice that the FILE parameter in Figure 28.1 has three separate parameter elements: the qualified file name, data member, and record format. A specified file must be a physical or logical file, an SQL view, or a Distributed Data Management file. In the sample command above, I specify the qualified file name only and do not enter a specific value for the second and third elements of the FILE parameter. Therefore, the default values of *FIRST and *ONLY are used for the member and record format, respectively.

You can select a particular data member to be queried by supplying a member name. The default value of *ONLY for record format tells the database manager to use the only record format named in file MYFILE in our example. When you have more than one record format, you must use the record format element of the FILE parameter to name the particular record format to open.

You can enter a plus sign in the "+ for more values" field and enter multiple file specifications to be dynamically joined (as opposed to creating a permanent join logical file on the system). When joining more than one record format, you must enter values in the join field specifications parameter (JFLD) to specify the field the database manager will use to perform the join.

The FORMAT parameter specifies the format for records made available by the OPNQRFY command. The fields defined in this record format must be unique from those named in the FILE or MAPFLD parameter. When you use the default value of *FILE for the FORMAT parameter, the record format of the file defined in the FILE parameter is used for records selected. You cannot use FORMAT(*FILE) when the FILE parameter references more than one file, member, or record format.

To return to our example, if you key

OPNQRYF FILE(MYLIB/MYFILE) ...

the record format of file MYFILE would be used for the records presented by the OPNQRYF command. On the other hand, if you use the command

OVRDBF FILE(MYJOIN) TOFILE(MYLIB/MYFILE) SHARE(*YES)

with this OPNQRYF command

OPNQRYF FILE(MYLIB/MYFILE) FORMAT(MYJOIN)

the database manager uses the record format for file MYJOIN.

The FORMAT parameter can specify a qualified file name and a record format (e.g., (MYLIB/MYJOIN JOINR)), or it can simply name the file containing the format to be used (e.g., (MYJOIN)). Although you can select (via the QRYSLT parameter) any fields defined in the record format of the file named in the FILE parameter, OPNQRYF will make available only those fields defined by the record format named in the FORMAT parameter. In the previous example, the HLL program would open file MYJOIN, and the OVRDBF command would redirect the open to the queried file, MYLIB/MYFILE. The format for MYJOIN would present records from MYFILE. Later, in the discussion of field mapping, I'll explain why you might want to do this.

Because this chapter is only an introduction to OPNQRYF, I won't talk any more about join files. Instead, let's focus on creating queries for single file record selection, sorting, mapping fields, and HLL processing.

Record Selection

As I said earlier, the record selection portion of the OPNQRYF command parallels SQL's WHERE statement. The QRYSLT parameter provides record selection before record grouping occurs (record grouping is controlled by the GRPFLD parameter). The query selection expression can be up to 2,000 characters long, must be enclosed in apostrophes (because it comprises a character string for the command to evaluate), and can consist of one or more logical expressions connected by *AND or *OR. Each logical expression must use at least one field from the files being queried. The OPNQRYF command also offers built-in functions that you can include in your expressions (e.g., %SST, %RANGE, %VALUES, and %WILDCARD).

This simple logical expression

QRYSLT('DLTCDE = "D"')

instructs the database manager to select only records for which the field DLTCDE contains the constant value D. A more complex query might use the following expression:

QRYSLT('CSTNBR *EQ %RANGE(10000 49999) *AND  +
        CURDUE *GT CRDLIM *AND CRDFLG *EQ "Y"')

In this example, CSTNBR (customer number), CURDUE (current due), and CRDLIM (credit limit) are numeric fields, and CRDFLG (credit flag) is a character field. The QRYSLT expression uses the %RANGE function to determine whether the CSTNBR field is in the range of 10000 to 49999 and then checks whether CURDUE is greater than the credit limit. Finally, it tests CRDFLG against the value Y. When all tests are true for a record in the file, that record is selected.

You can minimize trips to the manual by remembering a few rules about the QRYSLT parameter. First, enclose all character constants in apostrophes or quotation marks (e.g., 'char-constant' or "char-constant"). For example, consider the following logical expression comparing a field to a character constant:

CRDFLG "EQ "Y"

If you want to substitute runtime CL variable &CODE for the constant, you would code the expression as:

'CRDFLG *EQ "' *CAT &CODE *CAT '"'

After substitution and concatenation, quotation marks enclose the value supplied by the &CODE variable, and the expression is valid.

Second, differentiate between upper and lower case when specifying character variables. Character variables in the QRYSLT parameter are case-sensitive; in other words, you must either specify a "Y" or a "y" or provide for both possibilities.

Numeric constants and variables cause undue anxiety for newcomers to the OPNQRYF command. Look again at this example:

QRYSLT('CSTNBR *EQ %RANGE(10000 49999) *AND  +
        CURDUE *GT CRDLIM *AND CRDFLG *EQ "Y"')

Two of the logical expressions use numeric fields or constants. In the first expression

'CSTNBR *EQ %RANGE(10000 49999)'

notice there are no apostrophes or quotation marks around the numeric constants. Although these numbers appear in a character string (the QRYSLT parameter), they must appear as numbers for the system to recognize and process them, which brings us to the third QRYSLT parameter rule: Don't enclose numeric or character variables in quotation marks if the value of a variable should be evaluated as numeric.

The second logical expression

CURDUE *GT CRDLIM

compares two fields defined in the record format or mapped fields. Again, there are no quotation marks around the names of these numeric fields.

A dragon could rear its ugly head when you create a dynamic query selection in a CL or HLL program. Suppose you want to let the user enter the range of customer numbers to select from rather than hard-coding the range. To build a dynamic QRYSLT, you must use concatenation, and concatenation can only be performed on character fields. However, you would probably require the user to enter numeric values so you could ensure that all positions in the field are numeric. This means that the variables that define the range of customer numbers must be converted to characters before concatenation, but later they must appear as numbers in the QRYSLT parameter so they can be compared to the numeric CSTNBR field.

Figure 28.2 shows one way to create the correct QRYSLT value. Suppose the user enters the numeric values at a prompt provided by display file USERDSP. First, you use the CHGVAR (Change Variable) command to move these numeric values into character variables &LOWCHR and &HIHCHR. You can use the character variables and concatenation to build the QRYSLT string in variable &QRYSLT. When the substitution is made, the numeric values appear without quotation marks, just as though the numbers were entered as constants.

The GRPSLT parameter functions exactly like the QRYSLT parameter, except the selection is performed after records have been grouped. The same QRYSLT functions are available for the GRPSLT expression, and the same rules apply.

Key Fields

Besides selecting records, you can establish the order of the records OPNQRYF presents to your HLL program by entering one or more key fields in the key field specifications. The KEYFLD parameter consists of several elements. You must specify the field name, whether to sequence the field in ascending or descending order, whether or not to use absolute values for sequencing, and whether or not to enforce uniqueness.

Let's look at a couple of examples. The following OPNQRYF command:

OPNQRYF FILE(MYLIB/MYFILE) QRYSLT('....') KEYFLD(CSTNBR)

would cause the selected records to appear in ascending order by customer number because *ASCEND is the default for the key field order. The command

OPNQRYF FILE(MYLIB/MYFILE) QRYSLT('....')  +
        KEYFLD((CURBAL *DESCEND) (CSTNBR))

would present the selected records in descending order by current balance and then in ascending order by customer number.

Any key field you name in the KEYFLD parameter must exist in the record format referenced by the FORMAT parameter. The key fields specified in the KEYFLD parameter can be mapped from existing fields, so long as the referenced field definition exists in the referenced record format. The KEYFLD default value of *NONE tells the database manager to present the selected records in any order. Entering the value *FILE tells the query to use the access path definition of the file named in the FILE parameter to order the records.

Mapping Virtual Fields

One of the richer features of the OPNQRYF command is its support of field mapping. The mapped field specifications let you derive new fields (known as "virtual" fields in relational database terms) from fields in the record format being queried. You can map fields using a variety of powerful built-in functions. For example, %SST returns a substring of the field argument, %DIGITS converts numbers to characters, and %XLATE performs character translation using a translation table. You can use the resulting fields to select records and to sequence the selected records.

Look at the following OPNQRYF statement:

OPNQRYF FILE(INPDTL) FORMAT(DETAIL) QRYSLT('LINTOT *GT 10000')+
  KEYFLD((CSTNBR) (INVDTE)) MAPFLD((LINTOT 'INVQTY * IPRICE'))

Fields INVQTY (invoice item quantity) and IPRICE (invoice item price) exist in physical file INPDTL. Mapped field LINTOT (line total) exists in the DETAIL format, which is used as the format for the selected records. As each record is read from the INPDTL file, the calculation defined in the MAPFLD parameter ('INVQTY * IPRICE') is performed, and the value is placed in field LINTOT. The database manager then uses the value in LINTOT to determine whether to select or reject the record.

OPNQRYF Command Performance

Whenever possible, the OPNQRYF command uses an existing access path for record selection and sequencing. In other words, if you select all customer numbers in a specific range and an access path exists for CSTNBR, the database manager will use that access path to perform the selection, thus enhancing the performance of the OPNQRYF command. However, if the system finds no access path it can use, it creates a temporary one; and creating an access path takes a long time at the machine level, especially if the file is large. Likewise, when you specify one or more key fields in your query, the database manager will use an existing access path if possible; otherwise, the database manager must create a temporary one, again degrading performance.

Overall, the OPNQRYF command provides flexibility that is sometimes difficult to emulate using only HLL programming and the native database. However, OPNQRYF is a poor performer when many temporary access paths must be created to support the query request. You may also need to weigh flexibility against performance to decide which record-selection method is best for a particular application. To help you make a decision, you can use these guidelines:

• If the application is interactive, use OPNQRYF sparingly; and, unless the file is relatively small (i.e., fewer than 10,000 records), ensure that existing access paths support the selection and sequencing.
• If the application is a batch application run infrequently or only at night, you can use OPNQRYF without hesitation, especially if it eliminates the need for logical files used only to support those infrequent or night jobs.
• If the application runs frequently and in batch during normal business hours, use OPNQRYF when existing access paths support the selection and sequencing or when the files are relatively small. Use native database and HLL programming when the files are large (greater than 10,000 records) or when many (more than three or four) temporary access paths are required.

The next time a user requests a report that requires more than a few selections and whose records must be in four different sequences, use the OPNQRYF command to do the work and write one HLL program to do the reporting... But remember, to be on the safe side, run the report at night!

Sidebar: SQL Special Features