Copy From Query File (CPYFRMQRYF)

Copy From Query File (CPYFRMQRYF)

Where allowed to run: All environments (*ALL)
Threadsafe: Conditional Parameters
Examples
Error messages

The Copy From Query File (CPYFRMQRYF) command copies either all or part of a file that is opened with the Open Query File (OPNQRYF) command to a physical program-described printer, diskette, a DDM file, or tape file, or *PRINT can be specified on the TOFILE parameter to print the records by using the IBM-supplied printer file, QSYSPRT.

For more information on DDM files, see the Distributed Data Management information in the iSeries Information Center at http://www.ibm.com/eserver/iseries/infocenter.
This command can:

Add records to an existing physical file member or replace contents of a receiving physical file member by using the MBROPT parameter.
Copy records from an open query file format that is different than the to-file record format and convert records when copying to a source physical file. When the formats are different, the copy operation can:

Map fields that have the same name in the open query file format and the to-file record format
Drop fields from the open query file format that do not exist in the to-file record format
Copy the records directly, disregarding the differences between the open query file format and the to-file record format

Select a printout format when TOFILE(*PRINT) is specified. The records can be listed in character format, or in both character and hexadecimal format (OUTFMT parameter).
If the to-file does not exist before the copy operation, create the file (CRTFILE parameter) so that it has the same format as the open query file. Only the name, type, length, and decimal positions of each field in the open query format are used.
Copy a specified number or all of the records from an open query file depending if NBRRCDS(*END) was used or if a specific number was specified on the NBRRCDS parameter.

Additional information about CPYFRMQRYF is in the Files and file systems topic in the iSeries Information Center at http://www.ibm.com/eserver/iseries/infocenter and the Database information in the iSeries Information Center at http://www.ibm.com/eserver/iseries/infocenter.
Restrictions:

The open query file used by the CPYFRMQRYF command must not use any DDM files (specified on the FILE parameter of the OPNQRYF command).
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.
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.
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 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.

Top

Parameters

Keyword Description Choices Notes
FROMOPNID From open file identifier Name Required, Positional 1
TOFILE To file Single values: *PRINT
Other values: Qualified object name Required, Positional 2
Qualifier 1: To file Name
Qualifier 2: Library Name, *LIBL, *CURLIB
TOMBR To member or label Name, *FIRST Optional
MBROPT Replace or add records *NONE, *ADD, *REPLACE Optional
CRTFILE Create file *NO, *YES Optional
OUTFMT Print format *CHAR, *HEX Optional
NBRRCDS Number of records to copy Unsigned integer, *END Optional
FMTOPT Record format field mapping Single values: *NONE, *NOCHK, *CVTSRC
Other values (up to 2 repetitions): *MAP, *DROP Optional
ERRLVL Errors allowed Unsigned integer, 0, *NOMAX Optional

Top

From open file identifier (FROMOPNID)

Specifies the name used on the OPNQRYF command for identifying the open identifier for the query file. You must specify an open identifier associated with an open query file that allows input, update, or all operations specified by the OPNID and OPTION parameters on the OPNQRYF command.

Top

To file (TOFILE)

Specifies the file that receives the copied records.

A device file can be a diskette file, tape file, or a program-described printer file.
The possible values are:

*PRINT

The data is copied to a system printer device file (QSYSPRT) and formatted according to the value specified for the Print format (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 possible library values are:

*LIBL

All libraries in the library list for the current thread are searched until the first match is found.

*CURLIB

The current library for the job is used to locate the physical file or device file. If no library is specified as the current library, QGPL is used.

library-name

Specify the name of the library where the physical file or device file is located.

file-name

Specify the name and library 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.

Top

To member or label (TOMBR)

Specifies the name of the file member that receives the copied records.
If the to-file is a printer file, *FIRST must be specified for the To member or label prompt (TOMBR parameter).
The possible values are:

*FIRST

The first member of the specified file is used.

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.

Top

Replace or add records (MBROPT)

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

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.
The possible values are:

*NONE

This parameter does not apply to this copy operation. When the to-file is an existing physical file, *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 copied records are added to the existing records in each to-file member that is used.

*REPLACE

The system clears the existing member and adds the new records.

Top

Create file (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.
The possible values are:

*NO

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

*YES

If the to-file does not exist, a physical file is created that has the name specified on the To file prompt (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 To file prompt (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 this command must be authorized to add the file to the TOFILE library and must have operational authority to the Create Physical File (CRTPF) command.

Top

Print format (OUTFMT)

Specifies whether records are printed in character format, or in both character and hexadecimal format. This parameter is used only when *PRINT is specified for the To file (TOFILE) parameter.
The possible values are:

*CHAR

Records are printed in character format.

*HEX

Records are printed in both character and hexadecimal format.

Top

Number of records to copy (NBRRCDS)

Specifies the number of records copied to the to-file.
The possible values are:

*END

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

number-of-records

Specify a record number, ranging from 1 through 4294967288, that identifies the number of records 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.

Top

Record format field mapping (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.

Change the job CCSID to 65535 before running the OPNQRYF command if you plan to use the CPYFRMQRYF command.
The possible values are:

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

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

Top

Errors allowed (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.
The possible values are:

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 or more errors occur than the value specified here, the copy operation ends.

Top

Examples

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

Top

Error messages

*ESCAPE Messages

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.

Top

Keyword	Description	Choices	Notes
FROMOPNID	From open file identifier	Name	Required, Positional 1
TOFILE	To file	Single values: PRINT Other values: Qualified object name*	Required, Positional 2
	Qualifier 1: To file	Name
	Qualifier 2: Library	Name, LIBL, CURLIB
TOMBR	To member or label	Name, *FIRST	Optional
MBROPT	Replace or add records	NONE, ADD, *REPLACE	Optional
CRTFILE	Create file	NO, YES	Optional
OUTFMT	Print format	CHAR, HEX	Optional
NBRRCDS	Number of records to copy	Unsigned integer, *END	Optional
FMTOPT	Record format field mapping	Single values: NONE, NOCHK, CVTSRC Other values (up to 2 repetitions): MAP, *DROP	Optional
ERRLVL	Errors allowed	Unsigned integer, 0, *NOMAX	Optional