Using Open Query File (OPNQRYF) command

 

By using the Open Query File (OPNQRYF) command, you can open a file to a set of database records that satisfies a database query request.

The OPNQRYF command allows you to perform many data processing functions on database files. Essentially, it acts as a filter between the processing program and the database records. The database file can be a physical or logical file. Unlike the Create Physical File (CRTPF) or Create Logical File (CRTLF) command, the OPNQRYF command creates only a temporary file for processing the data; it does not create a permanent file.

The OPNQRYF command has functions similar to those in data description specifications (DDS) and the CRTPF and CRTLF commands. DDS requires source statements and a separate step to create the file. The OPNQRYF command allows a dynamic definition without using DDS. The OPNQRYF command does not support all of the DDS functions, but it supports significant functions that go beyond the capabilities of DDS. In addition, Query for iSeries™ can be used to perform some of the functions that the OPNQRYF command performs. However, the OPNQRYF command is more useful as a programmer's tool.

The OPNQRYF command parameters also have many functions similar to the SQL SELECT statements. For example, the FILE parameter is similar to the SQL FROM statement, the QRYSLT parameter is similar to the SQL WHERE statement, the GRPFLD parameter is similar to the SQL GROUP BY statement, and the GRPSLT parameter is similar to the SQL HAVING statement. The following list shows the major functions supplied by the OPNQRYF command:

• Dynamic record selection

• Dynamic keyed sequence access path

• Dynamic keyed sequence access path over a join

• Dynamic join

• Handling missing records in secondary join files

• Unique-key processing

• Mapped field definitions

• Group processing

• Final total-only processing

• Improving performance

• Open query identifier (ID)

• Sort sequence processing

To understand the OPNQRYF command, be familiar with its two processing approaches: using a format in the file, and using a file with a different format. The typical use of the OPNQRYF command is to select, arrange, and format the data so it can be read sequentially by your high-level language program.

• Creating a query with the Open Query File (OPNQRYF) command
  You can use the Open Query File (OPNQRYF) command to create a query over database records. Alternatively, you can create a query using the Run SQL Scripts window in iSeries Navigator.

• Using an existing record format in the file
  The Open Query File (OPNQRYF) command does the record selection, and your program processes only the records that meet the selection values. You can use this approach to select a set of records, return records in a different sequence than they are stored, or both.

• Using a file with a different record format
  For more advanced functions of the Open Query File (OPNQRYF) command (such as dynamically joining records from different files), define a new file that contains a different record format.

• CL program coding with the Open Query File (OPNQRYF) command
  When you use the Open Query File (OPNQRYF) command, follow these rules to prevent coding errors.

• The zero-length literal and the contains (*CT) function
  In the Open Query File (OPNQRYF) command, a zero-length literal is denoted as a quoted string with nothing, not even a blank, between the quotation marks (""). Zero-length literal support changes the results of a comparison when used as the compare argument of the contains (*CT) function.

• Usage notes for the Open Query File (OPNQRYF) examples
  These usage notes apply to the examples in the Selecting records without using DDS topic, which describe how to specify the parameters for the major functions of the Open Query File (OPNQRYF) command and how to use the OPNQRYF command in your high-level language program.

• Selecting records without using DDS
  The Open Query File (OPNQRYF) command provides dynamic record selection. That is, you can request a subset of records in a file without using data description specifications (DDS).

• Considerations for using the FORMAT parameter
  Here are the considerations for using the FORMAT parameter on the Open Query File (OPNQRYF) command.

• Considerations for arranging records
  Here are the considerations for arranging records using the Open Query File (OPNQRYF) command.

• Considerations for DDM files
  The Open Query File (OPNQRYF) command can process distributed data management (DDM) files. However, there are some restrictions.

• Considerations for writing a high-level language program
  Here are the considerations for writing a high-level language program that processes a database file using the Open Query File (OPNQRYF) command.

• Messages sent when the Open Query File (OPNQRYF) command is run
  When the Open Query File (OPNQRYF) command is run, messages are sent to inform the interactive user of the status of the OPNQRYF request.

• Using the Open Query File (OPNQRYF) command for more than just input
  You can use the OPTION parameter of the Open Query File (OPNQRYF) command to specify the type of processing.

• Comparing date, time, and timestamp using the Open Query File (OPNQRYF) command
  A date, time, or timestamp value can be compared either with another value of the same data type or with a string representation of that data type.

• Performing date, time, and timestamp arithmetic using the Open Query File (OPNQRYF) command
  Date, time, and timestamp values can be incremented, decremented, and subtracted. These operations might involve decimal numbers called durations.

• Using the Open Query File (OPNQRYF) command for random processing
  In addition to sequential processing, you can use the OPNQRYF command for random processing (for example, the RPG language operation CHAIN or the COBOL language operation READ). However, if you use the grouping or unique-key function, you cannot process the file randomly.

• Open Query File (OPNQRYF) command: Performance considerations
  Here are the tips and techniques for optimizing the performance of the Open Query File (OPNQRYF) command.

• Open Query File (OPNQRYF) command: Performance considerations for sort sequence tables
  Here are the tips and techniques for optimizing the performance of sort sequence tables.

• Performance comparisons with other database functions
  The Open Query File (OPNQRYF) command uses the same database support as logical files and join logical files. Therefore, the performance of functions like building a keyed access path or doing a join operation is the same.

• Field use
  When the grouping function is used, all fields in the record format for the open query file (FORMAT parameter) and all key fields (KEYFLD parameter) must be either grouping fields (specified on the GRPFLD parameter) or mapped fields (specified on the MAPFLD parameter).

• Files shared in a job
  To use the open data path that is built by the Open Query File (OPNQRYF) command, your program must share the query file.

• Checking if the record format description changed
  If record format level checking is indicated, the format level number of the open query file record format (identified on the FORMAT parameter) is checked against the record format your program was compiled against. This occurs when your program shares the previously opened query file.

• Other runtime considerations for the Open Query File (OPNQRYF) command
  You can consider overrides and copying from an open query file when using the Open Query File (OPNQRYF) command.

• Typical errors when using the Open Query File (OPNQRYF) command
  You must specify several functions correctly for the Open Query File (OPNQRYF) command and your program to get correct results.

• Open data path considerations
  An open data path (ODP) is a control block created when a file is opened. An ODP contains information about the merged file attributes and information returned by input or output operations.

• Field names
  Field names specified on the Open Query File (OPNQRYF) command parameters must follow these rules.

• Expressions
  The expressions specified on the Open Query File (OPNQRYF) command must follow these conventions.

• Built-in functions
  These built-in functions are supported for an expression that is used to define a derived field on the MAPFLD parameter or for a complex selection operand specified on the QRYSLT or GRPSLT parameter.

• Restricted built-in functions
  Some built-in functions are restricted in the way certain relational operators are specified on the QRYSLT and GRPSLT parameters.

 

Parent topic:

Opening a database file

 

Related concepts

SQL programming
Control language

 

Related reference

Open Query File (OPNQRYF) command