CPYFRMQRYF (Copy From Query File)

CPYFRMQRYF Command syntax diagram

 

Purpose

The Copy from Query File command copies a set of database records that satisfies a database query request to a physical file, a program-described printer file, an IBM-supplied printer file (QSYSPRT which is used when *PRINT is specified), a DDM file, a tape file, or a diskette file. The records are copied from the open query file associated with an open identifier. The open identifier must name an open query file that was opened for input, update, or all operations.

Note: For more information on DDM files, see the Distributed Data Management topic in the Information Center.

This command can:

Additional information about CPYFRMQRYF is in the File Management topic in the Information Center and the Database Programming topic in the Information Center.

 

Restrictions

  1. The open query file used by the CPYFRMQRYF command must not use any DDM files (specified on the FILE parameter of the OPNQRYF command).

  2. A member cannot be copied to itself. This restriction means that a member specified by the TOFILE and TOMBR parameters of the CPYFRMQRYF command cannot have the same name as any member specified on the FILE parameter of the OPNQRYF command, nor can the member have the same qualified name as any physical file members used by logical files that are on the FILE parameter of the OPNQRYF command.

  3. When the CRTFILE(*YES) parameter is specified and the FROMOPNID parameter identifies a query file that has an associated trigger, the file created (TOFILE parameter) does not have the associated trigger. The Add Physical File Trigger (ADDPFTRG) command must be used to add a trigger to the file.

  4. In multithreaded jobs, this command is NOT threadsafe if the OPNQRYF command is not run in a threadsafe manner, or if copying to multiple database file members, device files (except SPOOL(*YES) print files), distributed files, or DDM files of type *SNA. This command fails for distributed files that use relational databases of type *SNA and DDM files of type *SNA. It is threadsafe ONLY when copying to single database file members (local or DDM of type *IP) or SPOOL(*YES) print files provided the OPNQRYF command is run in a threadsafe manner.

 

Required Parameters

FROMOPNID
Specifies the name used on the OPNQRYF command for identifying the open identifier for the query file. The open identifier (specified by the OPNID parameter of OPNQRYF) names an open query file that allows input, update, or all operations as specified by the OPNID and OPTION parameters on the OPNQRYF command.

TOFILE
Specifies the qualified name of the file that receives the copied records.

Note: A device file can be a diskette file, tape file, or a program-described printer file.

*PRINT: The data is copied to the IBM-supplied printer device file, QSYSPRT, and the file is formatted according to the OUTFMT parameter.

The IBM-supplied printer file, QSYSPRT, may not be overridden to a different file name, and it must have the RPLUNPRT(*YES) and CTLCHAR(*NONE) attributes.

The name of the file can be qualified by one of the following library values:

*LIBL: All libraries in the job's library list are searched until the first match is found.

*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.

library-name: Specify the name of the library to be searched.

file-name: Specify the qualified name of the physical file or device file that receives the copied records. If no library qualifier is specified, *LIBL is used to locate the file. However, if CRTFILE(*YES) is specified and the specified file cannot be found, the file name must be qualified with a library name. When the physical to-file is created, it is placed in the specified library.

 

Optional Parameters

TOMBR
Specifies the name of the file member that receives the copied records.

Note: If the to-file is a printer file, the value of the TOMBR parameter must be *FIRST.

When a physical file is created by the copy operation for the to-file and TOMBR(*FIRST) is specified, the to-file file name is used as the file member name in the created file.

*FIRST: The first member in the database file receives the copied records.

member-name: Specify the name of the physical to-file member, or the label identifier of the diskette to-file or tape to-file that receives the records. If a member with the specified name does not already exist in the physical to-file, the copy operation attempts to add a member with the specified name to the file.

MBROPT
Specifies whether the new records replace or are added to the existing records.

Note: If the copy is to an existing physical file, this parameter must specify either *ADD or *REPLACE. If the to-file does not exist but CRTFILE(*YES) is specified, the copy operation assumes MBROPT(*ADD) for all records copied to the file after it is created, regardless of the value specified on this parameter. The copied records are always physically added to the end of a database file member in the same order that they are retrieved from the open query file.

*NONE: The MBROPT parameter does not apply to this copy operation. When the to-file is an existing physical file, MBROPT(*NONE) is not allowed; either *ADD or *REPLACE must be specified to indicate whether records should be added or replaced in each to-file member used.

*ADD: The system adds the new records to the end of the existing records.

*REPLACE: The system clears the existing member and adds the new records.

CRTFILE
Specifies whether a physical file is created to receive the data if the to-file does not exist. If the to-file already exists when this command is started, this parameter is ignored.

*NO: The to-file must exist when this command is started. A physical file is not created to receive the data.

*YES: If the specified to-file does not exist, a physical file is created that has the name specified on the TOFILE parameter. If the open query file format contains a user defined type, datalink, or LOB field type, the physical file created will be an SQL table. In all other instances the to-file created will be a database physical file that is not an SQL table. In addition to the normal copy operation validity checks, the following special conditions must all be true for the copy operation to create a to-file:

  • A library name must be specified on the TOFILE parameter (the default value, *LIBL, is not allowed).

  • There cannot be an override to a different file or library name; the values specified on this command for the to-file must be used.

  • The user running the CPYFRMQRYF command must be authorized to add the file to the TOFILE library and must have operational authority to the CRTPF command.


NBRRCDS
Specifies the number of records copied to the to-file.

Note: The first record copied is the record at the start of the open query file access path.

*END: Records are copied until the end-of-file condition is indicated.

number-of-records: Specify the number of records, ranging from 1 to 4294967288, that are copied to the to-file. Fewer records are copied if an end-of-file condition occurs before the specified number of records have been copied.

FMTOPT
Specifies, when the open query file is copied to a physical to-file, what field-level record format processing (if any) is done. If the to-file is a source physical file, *CVTSRC must be specified on this parameter. The CCSIDs for character and DBCS fields in the open query format are determined by the CCSID of the job in which the Open Query File (OPNQRYF) command is run. All CCSIDs other than 65535 are reset to the job CCSID of the OPNQRYF command, unless the OPNQRYF job CCSID is 65535, in which case the CCSIDs are unchanged. If the open query file format and to-file record formats are identical and the to-file is a data physical file, any FMTOPT value except *CVTSRC can be specified to perform the copy operation.

Note: Change the job CCSID to 65535 before running the OPNQRYF command if you plan to use the CPYFRMQRYF command.

*NONE: No field mapping or field dropping is done during the copy operation. This value is valid only if the open query file and to-file have the same record format, or if the to-file is not a database file. The record formats are the same only if every field exists in both the open query file and to-file formats, and has the same starting position and attributes in both formats. Attributes include CCSIDs, whether or not a field is null-capable, and the date/time format and separator (if the field is a date/time field). Null values are copied if *NONE is valid and both files are database files.

*NOCHK: If the record formats of the open query file and the to-file are different, the copy operation continues despite the differences. Record data is copied directly (left to right) from one file to the other. If this value is specified, null values are ignored, no conversion of date/time data occurs, and no CCSID conversions are done.

*CVTSRC: This value is used to copy to a source file. It is valid only when the to-file is a source file. If the to-file is a source file, sequence number and date fields are added, and the open query file record data is copied to the source data part of each to-file record. Null values are ignored and no conversion of date/time data occurs.

*MAP: Fields with the same name in the open query file and to-file record formats are copied, and fields in the to-file that do not exist in the open query file format are set to the default value specified on the DFT keyword for the data description specification (DDS) of the to-file (or zero for numeric fields, blanks for character fields, current date/time for date/time fields, or the null value for null-capable fields). If *MAP is specified, *DROP can also be specified. Mapped fields may have different starting positions in the open query file and to-file record formats. *MAP allows for CCSID conversions, the conversion of date/time data and for the copying of null values.

*DROP: This value must be specified for field-level mapping if any of the field names in the open query file record format do not exist in the to-file format. If *DROP is specified, *MAP can also be specified. When *DROP is specified, all the field names that exist in both record formats must have the same attributes and relative positions in the open query file and to-file record formats, or *MAP must also be specified. Null values are copied if *DROP is valid.

OUTFMT
Specifies, if TOFILE(*PRINT) is specified, whether the copied records are printed in character or hexadecimal format.

*CHAR: Records are printed in character format only.

*HEX: Records are printed in both character format and hexadecimal format.

ERRLVL
Specifies the maximum number of recoverable read or write errors that are tolerated for the file during the copy operation. The recoverable error count is reset at the beginning of each CPYFRMQRYF operation. If the number of recoverable errors handled is larger than the number specified on the ERRLVL parameter, the copy operation ends and a message is sent.

0: If a recoverable error occurs, the copy operation ends.

*NOMAX: No maximum number of errors is specified. All recoverable errors are tolerated. The copy operation continues regardless of the number of recoverable errors found.

number-of-errors: Specify a value that specifies the maximum number of recoverable errors that is allowed for the copy operation. If one more error occurs than the value specified here, the copy operation ends.

Examples for CPYFRMQRYF

Example 1: Building a File with a Subset of Records 

OPNQRYF  FILE(CUSTOMER/ADDRESS)
  QRYSLT('STATE *EQ "TEXAS"')
CPYFRMQRYF  FROMOPNID(ADDRESS)
  TOFILE(TEXAS/ADDRESS)  CRTFILE(*YES)

These commands create a file from the CUSTOMER/ADDRESS file containing records that have a value of Texas in the STATE field.

Example 2: Printing Records Based on Selection 

OPNQRYF  FILE(FILEA)  QRYSLT('CITY *EQ "CHICAGO"')
CPYFRMQRYF  FROMOPNID(FILEA)  TOFILE(*PRINT)

These commands print all records from FILEA where the value of the CITY field is Chicago.

Example 3: Copying a Subset of Records to a Diskette 

OPNQRYF  FILE(FILEB)  QRYSLT('FIELDB *EQ "10"')
OPNID(MYID)  CPYFRMQRYF  FROMOPNID(MYID)
TOFILE(DISK1)

These commands copy to a diskette file all records from FILEB where the value of FIELDB is 10.

Example 4: Creating a Copy of the Output from a Dynamic Join

Assume a user wants to create a physical file with the format and data of FILEA and FILEB. Assume the files contain the following fields: 

FILEA        FILEB      JOINAB
-----        -----      ------
Cust         Cust       Cust
Name         Amt        Name
addr                    Amt

OPNQRYF  FILE(FILEA FILEB)  FORMAT(JOINAB)
  JFLD((FILEA/CUST FILEB/CUST))
  MAPFLD((CUST 'FILEA/CUST'))  OPNID(QRYFILE)
CPYFRMQRYF  FROMOPNID(QRYFILE)
  TOFILE(MYLIB/FILEC)  CRTFILE(*YES)

These commands join FILEA and FILEB and save a copy of the results in a new physical file MYLIB/FILEC. The format of the file will be like JOINAB. The file will contain the data from the join of FILEA and FILEB using the Cust field. File FILEC in library MYLIB can be processed like any other physical file with CL commands (for example, Display Physical File Member (DSPPFM)) and utilities (for example, Query/400).

Error messages for CPYFRMQRYF

*ESCAPE Messages

CPF2816
File &1 in &2 not copied because of error.
CPF2816
File &1 in &2 not copied because of error.
CPF2817
Copy command ended because of error.
CPF2858
File attributes not valid for printed output.
CPF2859
Shared open data path not allowed.
CPF2864
Not authorized to file &1 in library &2.
CPF2875
Wrong file member or label opened.
CPF2883
Error creating file &1 in library &2.
CPF2888
Member &3 not added to file because of error.
CPF2909
Error clearing member &3 in file &1 in &2.
CPF2949
Error closing member &3 in file &1 in &2.
CPF2952
Error opening file &1 in library &2.
CPF2971
Error reading member &3 in file &1.
CPF2972
Error writing to member &3 in file &1.
CPF2975
Error while reading from keyed file.
CPF2976
Number of errors greater than ERRLVL value.
CPF3140
Initialize or copy of member &2 canceled.
CPF3143
Increments not allowed for member &2.
CPF3148
New records need too much space for member &2.
CPF3150
Data base copy failed for member &2.
CPF9212
Cannot load or unload DDM file &2 in &3.