Open Query File (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. Additional help can be found in the Database category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.

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)

Parameters

File specifications (FILE)

Specifies one or more files, members, and record formats processed by the open query file. All files specified must be physical or logical database files, or Distributed Data Management (DDM) files. An SQL derived key index may not be specified. If Distributed Data Management files are used, all files they refer to must be on the same target system.

When more than one file, member, and record format is specified, the query joins field values to create a single set of records. Any file specified in the list may be a join logical file or view logical file member. More information on view logical files is in DB2 for i SQL reference topic collection in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.

You can specify 32 values for this parameter.

This is a required parameter.

Element 1: File

Qualifier 1: File

name

Specify the name of the file to be processed.

Element 2: Member

*FIRST

The oldest member created is to be used.

*LAST

The newest member created is to be used.

*ALL

All members of a partition file are to be used.

name

Specify the name of the database file member to be used.

Element 3: Record format

*ONLY

The only record format in the file is to be used. If the file has more than one record format, a record format name must be specified.

name

Specify the name of the record format to be used. The record format must exist in the database file specified in the first element of this parameter.

Open options (OPTION)

Specifies the open option used for the query file. The options chosen on the first full open of a file are not changed on subsequent shared opens. You can either specify *ALL or a value that combines *INP, *OUT, *UPD, and *DLT in a list of up to four values in any order.

Single values

*ALL

Open the file for all operations (*INP, *OUT, *UPD, *DLT).

Other values (up to 4 repetitions)

*INP

Open the file for input. *INP is the only value allowed if join processing or group processing is requested, if UNIQUEKEY processing is specified, if all the fields in the open query file record format specified for the Format specifications (FORMAT) parameter are for input-only use, or if a temporary file is required to run the query.

*OUT

Open the file for output. In some high-level languages, output to certain files (such as files defined as 'direct access' in the high-level language program) is done by using a combination of input and update operations. *UPD and *INP are specified, or *ALL is specified, in order to use an open query file with such a program.

*UPD

Open the file for update operations. If an input operation comes before an update, you must specify *INP when *UPD is specified.

*DLT

Open the file for delete operations. If a delete operation is preceded by an input operation, you must specify *INP when *DLT is specified.

Format specifications (FORMAT)

Specifies the record format used for records available through the open query file. The simple field names in the open query file record format must represent fields that are either defined on the Mapped field specifications (MAPFLD) parameter or are unique across all files, members, and record formats specified on the File specifications (FILE) parameter. The value for any field that has the same name as a field specified on the MAPFLD parameter is determined by the mapped-field-definition on the MAPFLD parameter. The value for any field not defined on the MAPFLD parameter is determined by a mapping of the field with the same name in one of the based-on files, members, and record formats specified for the FILE parameter. Only the name, type, length, decimal positions, keyboard shift, and usage attributes of each field specified in the record format that is identified on the Format specifications (FORMAT) parameter are used for the open query file. All other attributes are ignored. The attributes do not have to be the same. If they differ, the fields are mapped in a way similar to the Change Variable (CHGVAR) command.

Single values

*FILE

The record format of the first or only entry on the File specifications (FILE) parameter is used. *FILE is not allowed when more than one file, member, and record format are specified on the FILE parameter (requiring a join query).

Element 1: File

Qualifier 1: File

name

Specify the name of a physical or logical database file, or a Distributed Data Management (DDM) file that contains the record format to be used.

Element 2: Record format

*ONLY

The only record format in the file is used. If no record format name is specified, *ONLY is the default. If the file has more than one record format, a record format name must be specified.

name

Specify the name of the record format to be used. The record format must exist in the database file specified for the first element of this parameter.

Query selection expression (QRYSLT)

Specifies the selection values used (before grouping) to determine the records that are available through the open query file.

*ALL

All records in the physical or logical files, members, and record formats specified for the File specifications (FILE) parameter (after join processing, if required) are selected.

'query-selection'

Specify an expression (contained in apostrophes) that describes the values used to determine which records are selected. You can specify any logical expression formed from relationships (such as *EQ and *NE) of field and constant values or functions of field and constant values. At least one field name is specified in each relationship. However, you cannot specify a field that depends on an aggregate function (either directly in its definition or indirectly by referring to a mapped field).

Each field name may be qualified with either a file name or number that indicates which element in the list of files, members, and record formats specified for the FILE parameter contains the field. The specified value *MAPFLD may be used to qualify the field name if the field is defined on the Mapped field specifications (MAPFLD) parameter.

For more information on data type compatibility, see Database category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.

Key field specifications (KEYFLD)

Specifies the name of one or more key fields that are used to arrange the query records, or specifies that the access path sequence of the first or only file, member, and record format specified for the File specifications (FILE) parameter is used to arrange the query records. If key field names are specified, you also indicate whether the part of the key associated with each key field is ascending or descending, and whether the records are arranged by the absolute value of a numeric key field. If the key field specified is a double-byte (DBCS) field, the data is arranged in a single-byte sequence.

Single values

*NONE

No key fields are used to arrange the query records; therefore, any arrangement is acceptable. It is even possible for the system to give query records in a different arrangement if the same query is run twice, based on such factors as the current number of records in the file members queried. *NONE allows the system more flexibility to improve the performance of processing records through the open query file.

*FILE

The query records have the same arrangement as the first file, member, and record format specified for the File specifications (FILE) parameter. *FILE can be specified even if the first file in the list has only an arrival sequence access path, in which case the query record arrangement matches the arrival sequence of the first file, member, and record format specified for the FILE parameter.

When KEYFLD(*FILE) is specified, and a sort sequence other than *HEX has been specified for the SRTSEQ parameter, you may receive your records in an order that does not reflect the true file order. If the file is keyed, the sort sequence is applied to the key fields of the file. If the file has a sort sequence table or an alternative collating sequence table, ordering is ignored. This allows users to indicate which fields to apply a sort sequence to without having to list all the field names. If a sort sequence is not specified for the query, the query is ordered as in releases previous to V2R3M0.

Element 1: Key field

Specify one or more field names (a maximum of 50 field names can be specified) to be used to define a keyed access path to arrange the query records. Each field name may be qualified with either a file name or number that indicates which element value in the list of files, members, and record formats specified for the File specifications (FILE) parameter contains the field. The special value *MAPFLD may also be used to qualify the field name if the field is defined on the Mapped field specifications (MAPFLD) parameter.

The sum of the lengths of all key fields cannot be more than 32766 bytes.

Note: The limits noted above are reduced by 2 bytes for each variable-length key field used. For instance, if three key fields are variable-length, the sum of the lengths of all key fields cannot exceed 32760 bytes, since 32766 bytes - (3 variable-length fields * 2 bytes per field) = 32760 bytes.

Qualifier 1: Key field

name

Specify the name of the field to be used as a key field.

Element 2: Key field order

*ASCEND

The part of the key defined by the specified key field is ordered by ascending key values.

*DESCEND

The part of the key defined by the specified key field is ordered by descending key values.

Element 3: Order by absolute value

*ABSVAL

The part of the key defined by the specified key field is arranged by the absolute value of the key field. *ABSVAL is specified together with either *ASCEND or *DESCEND, but it is ignored if the key field is not numeric. If *ABSVAL is not specified, the records are arranged by the signed value of a numeric key field.

Unique key fields (UNIQUEKEY)

Specifies whether the query is restricted to records with unique key values, and specifies how many of the key fields must be unique. If *ALL or a number is specified for this parameter, null values are considered equal.

*NONE

The key fields specified for the Key field specifications (KEYFLD) parameter are not required to be unique. All query records are available through the open query file, regardless of key value.

*ALL

All key fields specified for the KEYFLD parameter must be unique. If there are multiple query records with the same values for all of the key fields, only the first such record is available through the open query file.

1-120

Specify the number of key fields, ranging from 1 through 120, that is unique. This value must be no larger than the number of key fields determined by the KEYFLD parameter. If there are multiple query records with the same value for the specified number of consecutive key fields, only the first such record is available through the open query file.

Join field specifications (JFLD)

Specifies whether the query joins records from multiple file members, and specifies how to join field values from the files, members, and record formats specified for the File specifications (FILE) parameter in constructing the query records.

The first file, member, and record format specified for the FILE parameter is called the join primary, and all other element list values specified for the FILE parameter are called join secondaries. This parameter specifies a list of pairs of field names, in which the first field in each pair provides a value that is used to select records in a join secondary that have the same value in the second field name of the pair.

The join from-field and to-field may be mapped fields (specified for the Mapped field specifications (MAPFLD) parameter), but you cannot use a field that depends on an aggregate function either directly in its definition or indirectly by referring to a mapped field.

The join from-field and to-field are not required to have identical field attributes. For more information on data type compatibility, see Database category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.

If more than one file is specified for the FILE parameter, *NO is specified for the Join with default values (JDFTVAL) parameter and *ANY is specified for the Join file order (JORDER) parameter, then the system takes information from the Join field specifications (JFLD) parameter and the Query selection expression (QRYSLT) parameter and derives the final join specifications. If you specify a file on the FILE parameter that is not referred to on the QRYSLT parameter or the JFLD parameter, all records for that file are logically joined to all other records created from the other files specified for the FILE parameter.

If either *YES or *ONLYDFT is specified for the JDFTVAL parameter, or *FILE is specified for the JORDER parameter, the join fields must be specified for the JFLD parameter.

Up to 50 join field pairs can be specified.

Single values

*NONE

No join operation is specified. If more than one file is specified for the FILE parameter, *NO is specified for the JDFTVAL parameter, and *ANY is specified for the JORDER parameter, the system automatically finds the join fields from the QRYSLT parameter.

Element 1: From field

Specify a field name to provide the value used to select records in a join secondary file, member, and record format. The field name may be qualified with either a file name or number that indicates which element in the list of files, members, and record formats, specified for the FILE parameter contains the field. The special value *MAPFLD can also be used to qualify the field name if the field is defined on the MAPFLD parameter.

A join from-field is a simple field or a mapped field, defined on the MAPFLD parameter. If either *YES or *ONLYDFT is specified for the JDFTVAL parameter, a join from-field depends only on fields that are contained in the join primary or in join secondaries specified for the FILE parameter ahead of the join secondary associated with the to-field of the pair.

Qualifier 1: From field

name

Specify the name of the from-field.

Element 2: To field

Specify a field name used to select records from a join secondary file, member, and record format in constructing the query records. The field name is qualified with either a file name or number that indicates which element in the list of files, members, and record formats specified in the FILE parameter contains the field. The special value *MAPFLD can also be used to qualify the field name if the field is defined on the MAPFLD parameter.

A join to-field is a simple field or a mapped field, defined on the MAPFLD parameter. If either *YES or *ONLYDFT is specified for the JDFTVAL parameter, a join to-field depends only on fields that are contained all in a single join secondary. If the join secondary is a join logical file, only fields contained in the primary physical file member for the join logical file are used as components of the join to-field. The sum of the lengths of all to-fields for each join secondary (after change, if the from-field and to-field attributes are not identical) cannot be more than 2000 bytes unless JDFTVAL(*NO) is specified, where there is no 2000-byte limit.

Qualifier 1: To field

name

Specify the name of the to-field.

Element 3: Join operator

Specifies the type of join operation that is performed for the specified from-field and to-field. If *NO is specified for the JDFTVAL parameter and *ANY is specified for the JORDER parameter, or if more than one join field pair is specified, a different join operator may be specified for each pair. If *YES or *ONLYDFT is specified for the JDFTVAL parameter, or *FILE is specified for the JORDER parameter, then only one join operator may be specified regardless of the join pairs.

*EQ

An equal join operation is performed.

*GT

A greater than join operation is performed.

*LT

A less than join operation is performed.

*NE

A not equal join operation is performed.

*GE

A greater than or equal join operation is performed.

*LE

A less than or equal join operation is performed.

Join with default values (JDFTVAL)

Specifies whether the query file should include join records that use default values for any of the fields from a join secondary file that does not contain a record with correct field values that satisfy the join connections specified on the Join field specifications (JFLD) parameter.

Join processing attempts to collect field values from the join primary and join secondaries. It does so by matching join from-field values to records in a join secondary that produce the appropriate values in the join to-field. If there are no records in a join secondary to produce the to-field values required for the pairs of join fields associated with the join secondary, this parameter specifies whether query records should be constructed using default values for all fields obtained from the join secondary.

If the File specifications (FILE) parameter includes any join logic files, all join logical files must be compatible with this parameter's value. If the data description specification (DDS) used to create a queried join logical file does not contain the JDFTVAL keyword, this parameter may not be used for any of the join logical files specified for the FILE parameter, and JDFTVAL(*NO) is required. If any join logical file has the JDFTVAL keyword specified for the FILE parameter, then join logical files for this open query file must be created using the JDFTVAL keyword, and *YES is required. If any files on the FILE parameter are view logical files, then *NO must be specified this parameter.

If the JDFTVAL attribute is not compatible with the attributes of the join logical files processed, you can replace the join or view logical files specified for the FILE parameter with their based-on physical file members. You can provide the correct, additional from-field and to-field pairs on the JFLD parameter in order to join records from the physical file members in any way.

If more than one file is specified for the FILE parameter, and either *YES or *ONLYDFT is specified, the system uses the join fields as specified for the JFLD parameter as the final join specification.

*NO

No default values are used to construct join query records.

*YES

Create all records for the join, including those produced both with and without using default values. No view logical files are allowed on the FILE parameter.

*ONLYDFT

Create only the records produced by using default values in constructing the join. This option is used to include only exception records in the records available through the open query file. If *ONLYDFT is specified, no join or view logical files may be specified for the FILE parameter.

Join file order (JORDER)

Specifies, for a join query, whether the join order must match the order specified for the File specifications (FILE) parameter. If the join order is varied, the query records are generated in a different arrangement. If the value specified for the Join with default values (JDFTVAL) parameter is *YES or *ONLYDFT, this parameter is ignored. The order specified for the FILE parameter is always preserved, because changing the join order can change which records are returned when join default value processing is required.

If more than one file is specified in the FILE parameter and *FILE is specified, the system uses the join fields as specified for the Join field specifications (JFLD) parameter as the final join specifications.

*ANY

Any join file order is allowed, and any such arrangement may be used by the system to create the query records. It is possible for a query to return result records in a different arrangement if the same query is run twice consecutively (based on factors such as the current number of records in the files that are asked). *ANY allows the system more flexibility to improve the performance of processing records through the open query file than any other Join file order (JORDER) parameter value.

*FILE

The order of the file, member, and record format elements specified for the FILE parameter are preserved in the join operation.

Grouping field names (GRPFLD)

Specifies the field names that are used to group query results. One query record is created for each group of records (after join processing, if required) selected by the Query selection expression (QRYSLT) parameter. The group is defined by the collection of records that has the same set of values for the fields specified in the record format identified on the Format specifications (FORMAT) parameter. All null values within a group are considered equal. If no field names are specified and group processing is required, the whole file is considered to be one group. Each query record that is created is either made available through the open query file or is discarded, depending on the selection values specified for the Group selection expression (GRPSLT) parameter. To ensure a sequence, you must specify the Key field specifications (KEYFLD) parameter.

Single values

*NONE

No fields are used to form groups. If the grouping function is required (because selection values are specified for the GRPSLT parameter, or an aggregate function is used by a field specified for the Mapped field specifications (MAPFLD) parameter), all records selected by the values specified for the QRYSLT parameter are handled as a single group.

Other values

Specify one or more field names (up to 50) to be used to group the query results. Each field name may be qualified with either a file name or number to indicate which element in the list of files, members, and record formats specified for the FILE parameter contains the field. The special value *MAPFLD may also be used to qualify the field name if the field is specified for the MAPFLD parameter.

A grouping field defined on the MAPFLD parameter cannot refer to an aggregate function in its definition (either directly, or indirectly through the use of another field specified for the MAPFLD parameter). The sum of the lengths of all grouping fields cannot exceed 2000 bytes.

Qualifier 1: Grouping field names

name

Specify the name of a field to be used to group query results.

Qualifier 2: File or element

*MAPFLD

The field is defined on the MAPFLD parameter.

1-32

Specify the position of the element list value for the FILE parameter to be used. The element list value identifies the database file, file member, and record format to be used.

name

Specify the name of a database file specified for the FILE parameter.

Group selection expression (GRPSLT)

Specifies the selection values used after grouping to determine which records are available through the open query file.

*ALL

All records defined by the grouping function described by the Grouping field names (GRPFLD) parameter are selected.

'group-selection'

Specify an expression (contained in apostrophes) that describes the values used to determine which records are to be selected. Any logical expression formed from relationships (such as *EQ and *NE) of field and constant values, or functions of field and constant values, are specified. Only grouping fields (specified for the GRPFLD parameter), literals, aggregate functions (such as %AVG and %STDDEV), and mapped fields (specified for the Mapped field specifications (MAPFLD) parameter) that are composed of grouping fields, aggregate functions, and literals are referred to in any relationship. At least one field must be specified in each relationship.

Each field name may be qualified with either a file name or number that indicates which element in the list of files, members, and record formats specified for the File specifications (FILE) parameter contains the field. The special value *MAPFLD may also be used to qualify the field name if the field is specified for the MAPFLD parameter.

For more information on data type compatibility, see Database category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.

Mapped field specifications (MAPFLD)

Specifies the definition of query fields that are mapped or derived from other fields. MAPFLD is generally not needed if the field names specified on other parameters are simple field names that exist in only one of the file, member, and record format elements specified for the File specifications (FILE) parameter.

Up to 50 mapped field definitions can be specified.

Single values

*NONE

No mapped fields are needed. All field names specified on other parameters exist in some record format specified for the FILE parameter.

Element 1: Mapped field

name

Specify the simple field name used on any other parameter that must refer to this mapped field. A qualified name is not allowed for the first part of the parameter list element. All specified mapped-field-name values must be unique.

Element 2: Field definition expression

character-value

Specify an expression of up to 256 characters (contained in apostrophes) which defines the mapped field in terms of other fields that either exist in one of the file, member, and record format elements specified for the FILE parameter, or are defined by some other mapped field definition appearing earlier in the list. Either numeric operations or string operations are allowed, depending on the data type of the fields used in the definition.

Each field name may be qualified with either a file name or number that indicates which element in the list of files, members, and record formats specified for the FILE parameter contains the field. The special value *MAPFLD may also be used to qualify the field name if the field is specified for the Mapped field specifications (MAPFLD) parameter.

Element 3: Mapped field type

Specify the field type for this mapped field, or specify *CALC to allow the system to calculate appropriate attributes (including field type) for the mapped field. *CALC is the default if no field-type value is specified.

When *CALC is used, the field attributes are determined in one of two ways. The attributes either match the field definition in the record format identified on the Format specifications (FORMAT) parameter, or (if the field is not in the record format on the FORMAT parameter) the attributes are calculated based on the expression specified in the mapped-field-definition for this field. If the mapped field is used in the record format identified on the FORMAT parameter, you must either use *CALC or specify attributes (field-type, field-length, and field-decimals) identical to those of the field in the record format specified for the FORMAT parameter.

The field type must be valid for the final result of the expression specified for the mapped-field-definition.

The following are the mappings that are not supported between character, DBCS-open, DBCS-either, DBCS-only, graphic, binary string, and numeric types:

• From character or numeric to DBCS-only
• From DBCS-open to DBCS-either or DBCS-only
• From DBCS-either to character, numeric, or DBCS-only
• From DBCS-only or DBCS-graphic to character or numeric
• From UCS-2 or UTF-16 to DBCS-either or DBCS-only
• From binary string to any non-binary string
• From numeric to binary string

Note: Binary string refers to both BLOB and BINCHAR data types.

For more information on mappings see Database category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.

*CALC

Calculate appropriate field type attributes.

*BIN2

Two-byte binary field.

*BIN4

Four-byte binary field.

*FLT4

Four-byte floating-point field.

*FLT8

Eight-byte floating-point field.

*DEC

Packed decimal field.

*ZONED

Zoned decimal field.

*CHAR

Character field.

*VCHAR

Variable length character field.

*HEX

Hexadecimal field.

*VHEX

Variable length hexadecimal field.

*DATE

Date field.

*TIME

Time field.

*TIMESTP

Timestamp field.

*ONLY

DBCS-only field.

*VONLY

Variable length DBCS-only field.

*OPEN

DBCS-open field.

*VOPEN

Variable length DBCS-open field.

*EITHER

DBCS-either field.

*VEITHER

Variable length DBCS-either field.

*GRAPHIC

DBCS-graphic field.

*VGRAPHIC

Variable length DBCS-graphic field.

Element 4: Length

0-32766

Specify the field length in number of digits for a numeric field, number of bytes for a character or DBCS field, or number of characters for a graphic field. A field length must be an even value for DBCS-only and DBCS-either field types. The range of valid lengths for each field type is shown Table 1. A value must not be specified if *CALC is used for the element 3 (Mapped field type).

+------------------+----------------+------------------+
| Field Type       | External       | Default Length   |
|                  | Field Length   | and Decimals     |
+------------------+----------------+------------------+
| *BIN2            | 1-5            |  5   0           |
| *BIN4            | 1-10           | 10   0           |
| *FLT4            | 1-9            |  7   6           |
| *FLT8            | 1-17           | 15  14           |
| *DEC             | 1-63           | 15   5           |
| *ZONED           | 1-63           | 15   5           |
| *CHAR            | 1-32766        | 32               |
| *VCHAR           | 0-32740        | 32               |
| *HEX             | 1-32766        | 32               |
| *VHEX            | 0-32740        | 32               |
| *DATE            | 5-10           | 8                |
| *TIME            | 4-8            | 7                |
| *TIMESTP         | 14; 16-26      | 26               |
| *ONLY            | 4-32766        | 32               |
| *VONLY           | 0-32740        | 32               |
| *OPEN            | 4-32766        | 32               |
| *VOPEN           | 0-32740        | 32               |
| *EITHER          | 4-32766        | 32               |
| *VEITHER         | 0-32740        | 32               |
| *GRAPHIC         | 1-16383        | 32               |
| *VGRAPHIC        | 0-16370        | 32               |
+------------------+----------------+------------------+

Element 5: Decimal positions

0-63

Specify the number of decimal positions for a numeric field, expressed as a number of decimal digits, that is no larger than the total number of digits specified for the field length. If no value is given, the value is assumed to be zero. A value must not be specified for a binary or character field, or if *CALC is specified for element 3 (Mapped field type).

Element 6: Mapped field CCSID

*CALC

The coded character set identifier (CCSID) value is determined by the CCSIDs of the fields or literal values that make up the MAPFLD field definition.

*HEX

A pre-defined value is used such that no translation of the field data takes place.

1-65535

Specify the CCSID to be used. To see a complete list of identifiers when prompting this command, position the cursor on the field for this parameter and press F4 (Prompt).

Literal values in the MAPFLD definition are tagged with the job default CCSID. However, if the MAPFLD consists of only a literal value and the user specifies a field-CCSID value, the literal value will be tagged with that CCSID. This allows you to tag a literal with a CCSID other than the job's default CCSID.

Note: Normally, *HEX and *VHEX fields do not have an associated CCSID. Because of this, the data in the field is treated the same regardless of the default CCSID of the system on which the data is being used. However, if you specify a CCSID for a *HEX or *VHEX field, the CCSID overrides the hexadecimal attribute of the field (causing the field to be treated as *CHAR or *VCHAR), and the data in the field may be treated differently if it is moved to a system that has a different default CCSID.

Ignore decimal data errors (IGNDECERR)

Specifies whether the system ignores decimal data errors during query processing.

*NO

The system does not ignore decimal data errors.

*YES

The system ignores decimal data errors. When errors in decimal data are encountered, the not valid sign or digits are automatically changed to valid values.

Open file identifier (OPNID)

Specifies the identifier used to name the open query file so that it is referred to on the Close File (CLOF) or Position Database File (POSDBF) command when it is closed. The identifier must differ from the identifier associated with any other file previously opened with the Open Database File (OPNDBF) command or OPNQRYF command, and which is not yet closed.

*FILE

The name of the first or only file specified for the File specifications (FILE) parameter is used for the open identifier.

name

Specify the name you want to associate with this open query file.

Limit to sequential only (SEQONLY)

Specifies whether sequential-only processing is used for the file, and specifies the number of records processed as a group when read or write operations are performed to the open query file. The open query file ODP uses a different SEQONLY value than the one specified on this parameter, depending on other parameter values specified on this command. A message is sent if the SEQONLY value is changed.

Single values

*NO

The file does not use sequential-only processing.

Element 1: Sequential only

*YES

The open query file uses sequential-only processing.

Element 2: Number of records

1-32767

Specify the number of records that are processed as a group when read or write operations are performed to the open query file. If no value is specified, the system calculates the number of records to be processed as a group.

Commitment control active (COMMIT)

Specifies whether this file is placed under commitment control.

Before a database file is opened under commitment control, the user must ensure that all files in the commitment transaction are journaled. If only the after images are being journaled, the system implicitly begins journaling both the before and the after images for the duration of the changes being made to files opened under this commitment definition.

*NO

The open query file is not placed under commitment control.

*YES

The open query file is placed under commitment control.

Open scope (OPNSCOPE)

Specifies the extent of influence (scope) of the open operation.

Note: This parameter is not valid when TYPE is also specified.

*ACTGRPDFN

The scope of the open operation is determined by the activation group of the program that called the OPNQRYF command processing program. If the activation group is the default activation group, the scope is the call level of the caller. If the activation group is a non-default activation group, the scope is the activation group of the caller. In a multithreaded job, only those opens within the same thread and within the same activation group can share this ODP.

*ACTGRP

The scope of the open data path (ODP) is the activation group. Only those shared opens from the same activation group can share this ODP. This ODP is not reclaimed until the activation group is deactivated, or until the Close File (CLOF) command closes the activation group.

*JOB

The scope of the open operation is the job in which the open operation occurs. If the job is multi-threaded, only those opens from the same thread can share this ODP.

Duplicate key check (DUPKEYCHK)

Specifies whether duplicate key checking should be done on input and output operations for the file opened by this command.

*NO

No duplicate key feedback is provided on input and output commands.

*YES

Duplicate key feedback is provided on input and output commands.

Allow copy of data (ALWCPYDTA)

Specifies whether the system is allowed to copy data from the files, members, and record formats specified for the File specifications (FILE) parameter. If so, the system is allowed to open the query file to the copy. The system generally tries to avoid using a copy of the data because the copy does not reflect changes made to the database after the information is copied. However, certain requests require that the data be copied in order to perform the specified query functions (such as when key fields contained in multiple based-on files for a join are specified).

*YES

The system may use a copy of data from the files, members, and record formats specified for the File specifications (FILE) parameter. A copy of the data is used only when it is needed to perform the requested query functions.

*OPTIMIZE

The system uses a sort routine to order the output from the files, file members, and record formats specified for theFILE parameter. A sort routine is used only if the KEYFLD parameter is specified, and if using a sort routine would improve query performance without conflicting with other OPNQRYF options.

A sort will improve the performance of a query that returns most or all of the records in the file or files specified for theFILE parameter.

Using a sort can increase the time required for the OPNQRYF command to process. This occurs because the sort is performed and all records to be returned through the query are processed while the OPNQRYF command is active. However, because the records are already processed, the reading of the records (by using either a program or the CPYFRMQRYF command) is very fast. Therefore, the overall time to process the query is reduced.

Specifying the KEYFLD parameter for the OPNQRYF command does not ensure that the query will use an index if ALWCPYDTA(*OPTIMIZE) is specified. If a sort routine is used, the file is not opened with indexed access. If the program reading the records from the OPNQRYF command requires indexed access (random processing rather than sequential processing), ALWCPYDTA(*YES) or ALWCPYDTA(*NO) should be specified.

When a sort is used, the query file's position is not changed when a ROLLBACK statement is issued. Therefore, when a query is opened that has parameters, ROLLBACK statements that follow do not reset the queried file's position to where it was at the start of the unit of recovery.

Note: Do not specify ALWCPYDTA(*OPTIMIZE) if you require that a ROLLBACK statement reposition the query file, or if you require that the queried file be opened with indexed access.

The following items are required before a sort is valid for the OPNQRYF command:

• ALWCPYDTA(*OPTIMIZE) must be specified.
• The OPTION parameter, if specified, must be *INP.
• A value other than *FILE or *NONE must be specified on the KEYFLD parameter.
• The UNIQUEKEY parameter must not be specified, or must specify *NONE.
• The SEQONLY parameter, if specified, must be *YES.
• The DUPKEYCHK parameter must not be specified, or must specify *NO.
• The total buffer length of all fields in the file specified for the FORMAT parameter (or FILE parameter, if the FORMAT parameter is not specified) must not exceed 32700 bytes.

The query optimizer determines whether a sort is used. This decision is based on the number of records expected from the query and the options specified for the OPNQRYF statement. The following items influence the optimizer's choice of a sort:

• The OPTIMIZE parameter should specify *ALLIO or *MINWAIT. If *FIRSTIO is specified, the number of records specified should be close or equal to the number of result records expected from the query.
• The number of records in a file specified for theFILE parameter should contain a minimum of 200 records.
• The query result should contain a minimum of 200 records.

*NO

The system does not use a copy of data from the files, members, and record formats specified for the File specifications (FILE) parameter. If it is necessary to use a copy of the data to perform the requested query functions, the query file is not opened and an error message is issued.

Performance optimization (OPTIMIZE)

Specifies what optimization goal is used by the system in deciding how to perform the selection and join processing necessary to satisfy other specifications on this command.

If the Key field specifications (KEYFLD) parameter or Grouping field names (GRPFLD) parameter require that an access path be built (when no existing access path can be shared), the access path is built completely, regardless of the value specified for this parameter. Optimization primarily affects the timing of selection processing.

Single values

*ALLIO

The system attempts to improve the total time to process the whole query, assuming that all query records are read from the file.

*MINWAIT

The system attempts to improve the query to minimize delays when reading records from the file.

Element 1: Performance optimization

*FIRSTIO

The system attempts to improve the time to open the query file and retrieve the first buffer of records from the file.

Element 2: Number of records

1-2147483647

Specify the number of records expected to be retrieved. The query optimizer will use this information to determine the proper implementation for the query.

Optimize all access paths (OPTALLAP)

Specifies whether the query optimizer should consider all the access paths that exist over the files being queried when determining how to accomplish the query.

*NO

Allow the query optimizer to operate normally. When determining how to start a query, the optimizer considers access paths until an internal timeout value has been exceeded. If there are a large number of access paths over the files being queried, the optimizer may time out before it has considered all the available access paths.

*YES

Force the query optimizer to ignore the internal timeout value and consider all the available access paths over all the files in the query. Note that if there are a large number of access paths over the files it may take a long time to optimize the query.

Sort sequence (SRTSEQ)

Specifies the sort sequence to be used for sorting and grouping selections specified for the QRYSLT or GRPSLT parameters, joins specified for the JFLD parameter, ordering specified for the KEYFLD parameter, grouping specified for the GRPFLD parameter, %MIN or %MAX built in functions, or unique key values specified for the UNIQUEKEY parameter.

Single values

*JOB

The SRTSEQ value for the job is retrieved for the job.

*HEX

A sort sequence table is not used, and the hexadecimal values of the characters are used to determine the sort sequence.

*LANGIDSHR

A shared weight sort table is used.

*LANGIDUNQ

A unique weight sort table is used.

Qualifier 1: Sort sequence

name

Specify the name of the sort sequence table to be used with this query.

Qualifier 2: Library

*LIBL

All libraries in the user and system portions of the job's library list are searched.

*CURLIB

The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.

name

Specify the name of the library to be searched.

Language ID (LANGID)

Specifies the language identifier to be used when SRTSEQ(*LANGIDUNQ) or SRTSEQ(*LANGIDSHR) is specified.

*JOB

The LANGID value for the job is retrieved for the job.

language-ID

Specify the language identifier to be used by the job.

Final output CCSID (CCSID)

Specifies the coded character set identifier (CCSID) in which data from character, DBCS-open, DBCS-either and graphic fields will be returned. Data from UTF-8, UCS-2, or UTF-16 fields will not be converted.

*JOB

Data is returned in the CCSID of the job issuing the OPNQRYF command.

*HEX

No CCSID conversion is performed before the data is returned.

1-65535

Specify a CCSID value. Data will be converted to this CCSID before it is returned.

Type of open (TYPE)

Specifies the level at which the Reclaim Resources (RCLRSC) command closes the file.

Note: This parameter is ignored unless the default value is specified on the OPNSCOPE parameter and the request is from the default activation group.

*NORMAL

The Reclaim Resources (RCLRSC) command closes the file if the program call that ran this command is ended without closing the file.

*PERM

The file remains open until the Close File (CLOF) command closes it, or until the routing step or default activation group ends. The query file remains open even if the Reclaim Resources (RCLRSC) command is run.

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.

Error messages

*ESCAPE Messages

CPF2115

Object &1 in &2 type *&3 damaged.

CPF2169

Job's sort sequence information not available.

CPF2619

Table &1 not found.

CPF3BCC

Language identifier &1 not valid.

CPF3BC6

Sort sequence &1 not valid.

CPF3BC7

CCSID &1 outside of valid range.

CPF3BC8

Conversion from CCSID &1 to CCSID &2 is not supported.

CPF3BC9

Conversion from CCSID &1 to CCSID &2 is not defined.

CPF3BDD

Sort sequence &1 not valid for UCS2 data.

CPF3FC0

Language identifier is not valid.

CPF4174

OPNID(&4) for file &1 already exists.

CPF8133

Table &4 in &9 damaged.

CPF9801

Object &2 in library &3 not found.

CPF9802

Not authorized to object &2 in &3.

CPF9803

Cannot allocate object &2 in library &3.

CPF9807

One or more libraries in library list deleted.

CPF9808

Cannot allocate one or more libraries on library list.

CPF9810

Library &1 not found.

CPF9812

File &1 in library &2 not found.

CPF9813

Record format &3 in file &1 not found.

CPF9815

Member &5 file &2 in library &3 not found.

CPF9820

Not authorized to use library &1.

CPF9822

Not authorized to file &1 in library &2.

CPF9826

Cannot allocate file &2.

CPF9830

Cannot assign library &1.

CPF9899

Error occurred during processing of command.

*STATUS Messages

CPI4011

Query running. &2 records selected, &1 processed.

CPI4301

Query running.

CPI4302

Query running. Building access path for &2 in &1.

CPI4303

Query running. Creating copy of file &1 in &2.

CPI4304

Query running. &1 records selected. Selection complete.

CPI4305

Query running. Sorting copy of file *N in *N.

CPI4306

Query running. Building access path from file &1 in &2.

CPI4307

Query running. Building hash table from &2 in &1.