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:

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

Keyword Description Choices Notes
FILE File specifications Values (up to 32 repetitions): Element list Required, Positional 1
Element 1: File Qualified object name
Qualifier 1: File Name
Qualifier 2: Library Name, *LIBL, *CURLIB
Element 2: Member Name, *FIRST, *LAST, *ALL
Element 3: Record format Name, *ONLY
OPTION Open options Single values: *ALL
Other values (up to 4 repetitions): *INP, *OUT, *UPD, *DLT
Optional, Positional 2
FORMAT Format specifications Single values: *FILE
Other values: Element list
Optional
Element 1: File Qualified object name
Qualifier 1: File Name
Qualifier 2: Library Name, *LIBL, *CURLIB
Element 2: Record format Name, *ONLY
QRYSLT Query selection expression Character value, *ALL Optional
KEYFLD Key field specifications Single values: *NONE, *FILE
Other values (up to 50 repetitions): Element list
Optional
Element 1: Key field Qualified object name
Qualifier 1: Key field Name
Qualifier 2: File or element Name, *MAPFLD, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32
Element 2: Key field order *ASCEND, *DESCEND
Element 3: Order by absolute value *ABSVAL
UNIQUEKEY Unique key fields 1-120, *NONE, *ALL Optional
JFLD Join field specifications Single values: *NONE
Other values (up to 50 repetitions): Element list
Optional
Element 1: From field Qualified object name
Qualifier 1: From field Name
Qualifier 2: File or element Name, *MAPFLD, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32
Element 2: To field Qualified object name
Qualifier 1: To field Name
Qualifier 2: File or element Name, *MAPFLD, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32
Element 3: Join operator *EQ, *NE, *LT, *GT, *LE, *GE
JDFTVAL Join with default values *NO, *YES, *ONLYDFT Optional
JORDER Join file order *ANY, *FILE Optional
GRPFLD Grouping field names Single values: *NONE
Other values (up to 50 repetitions): Qualified object name
Optional
Qualifier 1: Grouping field names Name
Qualifier 2: File or element Name, *MAPFLD, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32
GRPSLT Group selection expression Character value, *ALL Optional
MAPFLD Mapped field specifications Single values: *NONE
Other values (up to 50 repetitions): Element list
Optional
Element 1: Mapped field Name
Element 2: Field definition expression Character value
Element 3: Mapped field type *CALC, *BIN2, *BIN4, *FLT4, *FLT8, *DEC, *ZONED, *CHAR, *VCHAR, *HEX, *VHEX, *DATE, *TIME, *TIMESTP, *ONLY, *VONLY, *OPEN, *VOPEN, *EITHER, *VEITHER, *GRAPHIC, *VGRAPHIC
Element 4: Length 0-32766
Element 5: Decimal positions 0-63
Element 6: Mapped field CCSID 1-65535, *CALC, *HEX
IGNDECERR Ignore decimal data errors *NO, *YES Optional
OPNID Open file identifier Name, *FILE Optional
SEQONLY Limit to sequential only Single values: *NO
Other values: Element list
Optional
Element 1: Sequential only *YES
Element 2: Number of records 1-32767
COMMIT Commitment control active *NO, *YES Optional
OPNSCOPE Open scope *ACTGRPDFN, *ACTGRP, *JOB Optional
DUPKEYCHK Duplicate key check *NO, *YES Optional
ALWCPYDTA Allow copy of data *YES, *OPTIMIZE, *NO Optional
OPTIMIZE Performance optimization Single values: *ALLIO, *MINWAIT
Other values: Element list
Optional
Element 1: Performance optimization *FIRSTIO
Element 2: Number of records 1-2147483647
OPTALLAP Optimize all access paths *NO, *YES Optional
SRTSEQ Sort sequence Single values: *HEX, *JOB, *LANGIDSHR, *LANGIDUNQ
Other values: Qualified object name
Optional
Qualifier 1: Sort sequence Name
Qualifier 2: Library Name, *LIBL, *CURLIB
LANGID Language ID Name, *JOB Optional
CCSID Final output CCSID 1-65535, *JOB, *HEX Optional
TYPE Type of open *NORMAL, *PERM Optional

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.

Qualifier 2: Library

*LIBL
The library list is used to locate the database file.
*CURLIB
The current library for the job is used to locate the database file. If no library is specified as the current library for the job, QGPL is used.
name
Specify the name of the library where the database file is located.

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.

Qualifier 2: Library

*LIBL
The library list is used to locate the database file.
*CURLIB
The current library for the job is used to locate the database file. If no library is specified as the current library for the job, QGPL is used.
name
Specify the name of the library where the database file is located.

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.

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.

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.

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.

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.

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.

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:

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).
Figure: Table 1. Query Field Structure

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.