OPNQRYF (Open Query File) Command Description

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

1. Overrides can change file, library, or member names on the FILE parameter, but not on the FORMAT parameter unless FORMAT(*FILE) is specified.
2. OPNQRYF does not share an existing ODP. If a shared ODP already exists with the same file/member, the command fails.
3. Subsequent shared opens must use the same options as the initial OPNQRYF.
4. Some system commands (e.g., DSPPFM, CPYF) do not share ODPs, so OPNQRYF cannot be used with them.
5. BASIC programs cannot use OPNQRYF because they don’t share ODPs.
6. In multithreaded jobs, OPNQRYF is not threadsafe for distributed files using *SNA.
7. 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.

---