(FAQ) How To Use OPNQRYF

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:
o Read (*READ) authority if the file is opened for input (using option *INP)
o Add (*ADD) authority if the file is opened for output (using option *OUT)
o Update (*UPD) authority if the file is opened for updates (using option *UPD)
o Delete (*DLT) authority if the file is opened for deletions (using option *DLT)
o *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)

Sample:
OVRDBF FILE(ARD) SHARE(*YES)

OPNQRYF FILE((ARD)) QRYSLT('ARDDEL *EQ ("' *CAT +
&DELCDE *CAT '") *AND ARPDCQ *NE ("' *CAT +
&ARDCQ *CAT '") *AND XDATE *LE ("' *CAT +
&TOYMD *CAT '")') KEYFLD(*FILE) +
MAPFLD((XPRN PRNCPL) (XDATE +
'%DIGITS(ARCRTD)'))
CALL PGM(ZPG308C) PARM(&PBFR &PBTO &COLDVF &COLDVT +
&PDTFR &PDTTO &USER &JOB &RDATE)

CLOF OPNID(ARD)

DLTOVR FILE(*ALL)