CL Command Reference - OPNQRYF
CL Command List > OPNQRYF Reference
Description:
The Open Query File (OPNQRYF) command opens a file to a set of database records that satisfies a database query request. Once opened, the file looks like a database file opened using the Open Database File (OPNDBF) command, and the records in the file are accessed by high-level language programs that share the open data path (ODP). The path is closed, and all query resources are deallocated, using the Close File (CLOF) command.
This command is used to do any combination of the following database functions:
. Join records from more than one file, member, and record format. The join may be either equal or non-equal in nature.
. Calculate new field values using numeric and character operations on field values and constants.
. Group records by like values of one or more fields, and calculate aggregate functions, such as minimum field value and average field value, for each group.
. Select a subset of the available records, with selection both before and after grouping the records.
. Arrange result records by the value of one or more key fields.
Restrictions:
1. The user can use overrides to change the file, library, and member names specified for the FILE parameter. Overrides are ignored for the file and library specified for the FORMAT parameter, unless FORMAT(*FILE) is specified. Parameter values specified on an override command, other than TOFILE, MBR, LVLCHK, WAITRCD, SEQONLY, or INHWRT and SHARE, are ignored by the OPNQRYF command.
2. The OPNQRYF command does not share an existing open data path (ODP) in the job or activation group. If an existing SHARE(*YES) ODP in the job or activation group has the same file, library, and member name as the open query file open data path (ODP), the query file does not open and an escape message is sent.
3. Each subsequent shared open operation must use the same open options (such as SEQONLY) that are in effect when the OPNQRYF command is run.
4. Some system functions (such as the Display Physical File Member (DSPPFM) and Copy File (CPYF) commands) do not share an existing open data path. The OPNQRYF command cannot be used with those functions.
5. The file opened with the OPNQRYF command cannot be used in programs written in BASIC because BASIC does not share an existing open data path.
6. This command is conditionally threadsafe. In multithreaded jobs, this command is not threadsafe for distributed files and fails for distributed files that use relational databases of type *SNA. This command is also not threadsafe and fails for Distributed Data Management (DDM) files of type *SNA.
7. Users of this command must have the following authorities:
. Execute (*EXECUTE) authority for any library that is needed to locate the files specified for the FILE and FORMAT parameters
. Object operational (*OBJOPR) authority for any physical or logical file specified for the FILE parameter, and one or more of the following data authorities for the physical file or based-on physical file members of a logical file member:
每 Read (*READ) authority if the file is opened for input (using option *INP)
每 Add (*ADD) authority if the file is opened for output (using option *OUT)
每 Update (*UPD) authority if the file is opened for updates (using option *UPD)
每 Delete (*DLT) authority if the file is opened for deletions (using option *DLT)
每 *READ, *ADD, *UPD, and *DLT authority if the file is opened for all I/O operations (using option *ALL)
. *OBJOPR authority for any file specified for the FORMAT parameter
. Use (*USE) authority for any translate tables specified for the MAPFLD parameter (using option *USE)
Examples:
Example 1: Selecting Specific Records
Note: Additional examples of selecting records using the OPNQRYF command can be found in the Database Programming topic in the Information Center.
OPNQRYF FILE(ordfile) OPTION(*ALL)
QRYSLT(＊orddate=%range("840101" "841231") & ordamt>100＊)
KEYFLD((ordamt *descend))
This command uses the QRYSLT parameter to select only records in the first member of file ORDFILE that have an order date in 1984 and an order amount greater than 100. Because the FORMAT parameter is omitted, the open query file has the same record format as file ORDFILE. The open query file allows all file operations (input, output, update, and delete). The KEYFLD specification is used to force the records to be arranged by descending value of order amount.
Example 2: Using the %XLATE Built-In Function
OPNQRYF FILE(telefile)
QRYSLT(＊%xlate(usrname qsystrntbl) *ct "GEORGE"＊)
This command uses the %XLATE built-in function to translate the field USRNAME to uppercase, and to instruct the *CT operator to select only records that contain the value GEORGE in the field USRNAME. QSYSTRNTBL is an IBM-supplied system translation table that converts lowercase alphabetics (a through z) to uppercase (A through Z). The translation is done to ensure that the search value is recognized even if its characters appear in mixed case. The records available through the open query file have the same record format as those in file TELEFILE.
Example 3: Using the %XLATE Built-In Function
OPNQRYF FILE(telefile) QRYSLT(＊usrname *ct ＊＊GEORGE＊＊＊)
MAPFLD((usrname ＊%xlate(telefile/usrname qsystrntbl)＊))
In the previous example, the value of field USRNAME, which is returned to the high-level language (HLL) program that reads records from the open query file, is not translated to uppercase.
This example shows a way to make the uppercase version of field USRNAME available to the HLL program. This is done by defining a mapped field (MAPFLD parameter) for the translated value of field USRNAME. The field has the same field name as the field name in the open query file record format being used. The translated version of the field is used for selection (QRYSLT parameter) and is used in the open query file record format.
Example 4: Using the %SST Built-In Function
OPNQRYF FILE((histlib/ordfile hist1)) OPTION(*inp *upd *dlt)
FORMAT(ordinfo orddtls) QRYSLT(＊month=7＊)
MAPFLD((year ＊%sst(orddate 1 2)＊ *zoned 2)
(month ＊%sst(orddate 3 2)＊ *zoned 2)
(day ＊%sst(orddate 5 2)＊ *zoned 2))
This command uses the %SST built-in function to create a substring of the year, month, and day parts of character field ORDDATE in file ORDFILE. If the file ORDINFO has a record format, ORDDTLS, containing at least the field＊s YEAR, MONTH, and DAY records, these fields have input-only usage in the open query file record format because they are defined by using a built-in function (%SST) and are mappings that mix character and numeric (zoned decimal format) types. The file is opened for input, update, and delete operations, but none of the field＊s YEAR, MONTH, and DAY records are updated using the open query file open data path (ODP). The open query file uses only records in the HIST1 member of file ORDFILE in library HISTLIB, and the records retrieved through the file have the same format as record format ORDDTLS in file ORDINFO. Only records pertaining to the month of July are processed through the open query file (QRYSLT parameter).
Example 5: Returning the First Record of Each Set
OPNQRYF FILE((routelf *first locusr))
QRYSLT(＊%sst(toloc 1 4) *eq "ROCH"＊)
KEYFLD(fromusr fromloc tousr toloc) UNIQUEKEY(*all)
This command uses the KEYFLD and UNIQUEKEY parameters to return only the first record of each set of records in record format LOCUSR in the first member of file ROUTELF that have the same values for the fields FROMUSR, FROMLOC, TOUSR, and TOLOC. The query result is further restricted by selecting only records that have the value ROCH in the first four characters of field TOLOC. The records available through the open query file contain all of the fields in record format LOCUSR of file ROUTELF. If the file ROUTELF contains information about messages routed by an application, this example identifies all unique sender and receiver pairs in which the receiving location name begins with ROCH.
Example 6: Joining a File to Itself
OPNQRYF FILE(partpf partpf) FORMAT(partjoin) JFLD((1/pnbr 2/pnbr *GE))
MAPFLD((pnm1 ＊1/pname＊) (pnm2 ＊2/pname＊) (pnbr ＊1/pnbr＊))
This example illustrates how a file is joined to itself, as well as how to use the MAPFLD parameter to rename fields in the based-on files. A greater than or join is performed using field PNBR as both the join from-field and the join to-field.
The format of file PARTJOIN is assumed to contain fields named PNBR, PNM1, and PNM2. The field name PNBR is valid in the query output record format because that field is defined on the MAPFLD parameter. If the record format in file PARTJOIN contains a field named PNAME, an error occurs because the field exists in both files specified on the FILE parameter, and is not the name of a field defined on the MAPFLD parameter. The mapped field definitions are field names, so the attributes of fields PNM1 and PNM2 match the attributes of field PNAME, and the attributes of field PNBR in the open query file records match field PNBR in file PARTPF. Further, when a file is joined to itself, it is always necessary to specify a file number name for any field that is defined in the based-on file.
Example 7: Renaming Fields in Based-On Files
The same query can also be specified as follows:
OPNQRYF FILE(partpf partpf) FORMAT(partjoin)
QRYSLT(＊1/pnbr *GE 2/pnbr＊)
MAPFLD((pnm1 ＊1/pname＊) (pnm2 ＊2/pname＊)
(pnbr ＊1/pnbr＊))
Because more than one file is specified on the FILE parameter, and the default value is specified for the JDFTVAL and JORDER parameters, the system takes the join specifications from the values specified on the QRYSLT parameter.
Example 8: Selecting Master Records With No Detail Records
OPNQRYF FILE(cusmas ordfil) FORMAT(cusmas)
JFLD((cusnbr ordfil/cusnbr)) JDFTVAL(*onlydft)
MAPFLD((cusnbr ＊cusmas/cusnbr＊))
This command uses a join query to select only master records that have no associated detail records. The master file (CUSMAS) is joined (equal join) to the detail file (ORDFIL) by the customer number field that appears in both record formats. The customer number field name is the same in both record formats (CUSNBR). Because CUSNBR is the name of a field defined on the MAPFLD parameter, everywhere the simple field name CUSNBR is used, the mapped field version of the CUSNBR field in file CUSMAS is used (including the open query file record format, which matches the customer master file record format). The JDFTVAL parameter indicates that only records that are produced by using default values are available through the open query file. Every master record that has associated detail records (with the same value of the customer number field) is excluded, and every master record that has no associated detail records creates a result record.
Example 9: Identifying Detail Records With No Associated Master Record
OPNQRYF FILE(ordfil cusmas) FORMAT(ordfil)
JFLD((cusnbr cusmas/cusnbr)) JDFTVAL(*onlydft)
MAPFLD((cusnbr ＊ordfil/cusnbr＊))
This change of the previous example (using the same files) shows how to identify all detail records with no associated master record (in this case, all orders with an unregistered customer number):
Example 10: Calculating Basic Statistics
OPNQRYF FILE(scores) FORMAT(clsstats) GRPFLD(clsid)
GRPSLT(＊clsavg<70 & clsmax-clsmin>30＊)
MAPFLD((clscnt ＊%count＊) (clsavg ＊%avg(usrscore)＊)
(clsmin ＊%min(usrscore)＊) (clsmax ＊%max(usrscore)＊))
This command uses the grouping function to calculate basic statistics for each group of records in file SCORES that have the same value in the field CLSID. Assuming file CLSSTATS has a record format containing field CLSID and all fields specified on the MAPFLD parameter, each record available through the open query file contains the value of the grouping field (CLSID) as well as the number of records included in the group and the average, minimum, and maximum values of field USRSCORE in the group. Selection occurs after grouping, so that records are created for groups only when the average value of USRSCORE in the group is less than 70 and the difference between the maximum and minimum scores in the group is greater than 30.
Example 11: Selecting Records With a Specific Value
OPNQRYF FILE(ITMMAST) QRYSLT(＊itmcode=%range(32 50) & itmtype="P"＊)
ALWCPYDTA(*NO) OPTIMIZE(*FIRSTIO) SEQONLY(*YES 10) TYPE(*PERM)
This command selects from the first member of file ITMMAST only the records that have a value of field ITMCODE in the range from 32 through 50 and also have a value of field ITMTYPE equal to the letter P. The ALWCPYDTA parameter specifies that the open query file must never use a copy of the records in file ITMMAST. The OPTIMIZE and SEQONLY parameter values cause the system to attempt to improve processing for the open query file to minimize the time needed to retrieve the first buffer of ten records. This combination of parameter values is a good choice if the file is used with a high-level language interactive inquiry program that shares the open query file open data path (ODP) and shows ten records on each display screen. The open data path (ODP) for the open query file is ＊permanent＊ (TYPE parameter), which means that it remains open either until the file is closed by using the Close File (CLOF) command or until the routing step ends.
Example 12: Tagging a Literal with a Specific CCSID
OPNQRYF FILE(itmmast) QRYSLT(＊itmtype=pfield＊)
MAPFLD((pfield ＊P＊ *CHAR 1 *N 930))
This command selects from the first member of file ITMMAST only the records that have a value of field ITMTYPE equal to the letter ＊P＊ in character set 930. The mapped field is created so that the literal ＊P＊ can be tagged with a specific CCSID.
If a literal is not tagged with a specific CCSID, it is assigned the CCSID of the job running the query. Because of this, if an OPNQRYF statement is part of a CL program that is shared among systems with differing CCSIDs (in different countries, perhaps), a query that uses a literal in the selection specifications may not return the same results on all systems, even though the data in the files is the same. This happens because the internal representation of the literal may be different when the CL program is run in a job with a different CCSID. This representation then may not match the same records in the file. Note that the internal representation of the data in the file does not change based on the CCSID of the current job.
Tagging the literal with a specific CCSID avoids this problem. A literal tagged with a specific CCSID keeps the same internal representation on all systems. The CCSID that is used to tag the literal should be the same as the CCSID assigned to the field against which the literal is being compared.
Example 13: Using a Nonjoin Query
OPNQRYF FILE((EMPLOYEE)) KEYFLD((NAME)) ALWCPYDTA(*OPTIMIZE)
This command returns all of the records in the EMPLOYEE file.
Example 14: Using a Join Query
OPNQRYF FILE((EMPLOYEE) (MANAGEMENT)) FORMAT(EMPLOYEE)
KEYFLD((NAME)) JFLD((1/EMPID 2/MEMPID))
ALWCPYDTA(*OPTIMIZE)
This command returns all of the records required by the join criteria.
Example 15: Query Comparing Character and Numeric Data
OPNQRYF FILE((STAFF)) QRYSLT(＊SALARY > "18357.50"＊)
This command returns all of the records in the STAFF file where their salary is greater than 18357.50 even though SALARY is a numeric field and the literal value in the QRYSLT is character.