OPNQRYF Command Overview
Purpose
The Open Query File (OPNQRYF) command is used to open a file that contains records meeting specific query conditions. Once opened, the file behaves like one opened with the OPNDBF command, and programs can access its records through a shared open data path (ODP). When finished, the path and resources are released with the CLOF command.
This command can perform several database operations:
- Join records from multiple files, members, or formats (supports both equal and non-equal joins).
- Compute new values using arithmetic or character expressions.
- Group records by field values and apply aggregate functions (e.g., MIN, AVG).
- Filter records before or after grouping.
- Sort results by one or more key fields.
Restrictions
- Overrides can change file, library, or member names on the FILE parameter, but not on the FORMAT parameter unless FORMAT(*FILE) is specified.
- OPNQRYF does not share an existing ODP. If a shared ODP already exists with the same file/member, the command fails.
- Subsequent shared opens must use the same options as the initial OPNQRYF.
- Some system commands (e.g., DSPPFM, CPYF) do not share ODPs, so OPNQRYF cannot be used with them.
- BASIC programs cannot use OPNQRYF because they don’t share ODPs.
- In multithreaded jobs, OPNQRYF is not threadsafe for distributed files using *SNA.
- Required authorities include:
- Execute authority for libraries.
- Operational and data authorities (read, add, update, delete) depending on the file access mode.
- Operational authority for FORMAT files.
- Use authority for translation tables on MAPFLD.
Key Parameters
FILE
Defines the files, members, and record formats to be processed.
- Supports physical, logical, or DDM files.
- Library qualifiers: *LIBL, *CURLIB, or explicit library name.
- Member options: *FIRST, *LAST, or specific member.
- Record format options: *ONLY or a named format.
- Up to 32 physical file members can be joined in one query.
OPTION
Specifies how the file is opened:
- *INP → Input only
- *OUT → Output
- *UPD → Update
- *DLT → Delete
- *ALL → All operations
FORMAT
Defines the record format for the query results.
- Can be mapped fields or unique field names.
- FORMAT(*FILE) uses the format of the first file listed.
QRYSLT
Sets selection criteria before grouping.
- *ALL → Selects all records.
- Expression → Logical conditions (e.g.,
CUSNBR<7000 *AND BALDUE>CRLIMIT*0.9).
KEYFLD
Determines record ordering.
- *NONE → No specific order.
- *FILE → Order follows the first file’s access path.
- Qualified field names → Up to 50 fields, with ascending/descending options.
UNIQUEKEY
Controls uniqueness of key fields.
- *NONE → No uniqueness required.
- *ALL → All key fields must be unique.
- Number → Specifies how many key fields must be unique.
JFLD
Defines join conditions between files.
- Specifies pairs of fields for join operations.
- Supports operators like *EQ, *NE, *GT, *LT, *GE, *LE.
JDFTVAL
Controls whether default values are used when join matches are missing.
- *NO → No defaults.
- *YES → Include records with defaults.
- *ONLYDFT → Only include records created with defaults.
JORDER
Specifies join order.
- *ANY → System chooses order for performance.
- *FILE → Order follows FILE parameter.
GRPFLD
Defines grouping fields.
- *NONE → No grouping.
- Qualified field names → Up to 50 fields.
GRPSLT
Applies selection after grouping.
- *ALL → All groups included.
- Expression → Logical conditions using grouping fields or aggregate functions.
MAPFLD
Defines mapped or derived fields.
- *NONE → No mapped fields.
- Named fields → Used in other parameters to reference derived values.
