Copy File (CPYF) command copies all or part of a file from the database or from an external device to the database or to an external device.

It can Copy data and source files between database files. Records can be copied from physical or logical files. However, records can be copied only to physical files, not to logical files.

Copy data and source files from external devices, such as diskette and tape, to the database. Copy data and source files from the database to external devices. Copy data and source files from external devices to other external devices. Copy data and source files from inline data files to the database or to external devices.

Important parameters and examples : -

Below parameter is needed when Destination file/To file exists:-

MBROPT(*REPLACE) : Replace data in To-file(Destination file) from From-file (Source file)

MBROPT(*ADD) : Add data in To-file(Destination file) ,existing records before copy will be intact .

Below parameter is needed when destination file doesn't exists.:- CRTFILE(*YES) . Note : Library name parameter of destination file with CFRTFILE (*YES) can't be *LIBL it has to be a *CURLIB or Library name.

Below parameter is important when we have different number of fields/ different record formats

*MAP
Fields with the same name in the from-file and to-file record formats are copied, and any fields in the
to-file that do not exist in the from-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.

*DROP
This value must be specified for field-level mapping if any of the field names in the from-file record
format do not exist in the to-file format.

*MAP and *DROP can be specified together

*NOCHK : If the record formats of the database files are
different, the copy operation continues despite the
differences. Record data is copied directly (left to
right) from one file to the other.

This can be specified when

1. There is a copy between flat file and structured file.
2. Multiformat logical file and Physical file.

*CVTSRC
This value is used to copy between database files,
from a source file to a data file, or from a data file to a source file. It is valid only when the from-file and to-file are different types (source and data).

Examples .

CPYF FROMFILE(DEPTPF) TOFILE(*CURLIB/DEPTBK) CRTFILE(*YES) Deptbk doesn't exist ,it will create DEPTBK file with data.

This is using both *MAP and *DROP

CPYF FROMFILE(DEPTPF) TOFILE(*CURLIB/DEPT1) MBROPT(*REPLACE) CRTFILE(*NO) OUTFMT(*CHAR) FMTOPT(*MAP *DROP)

CPYF based on record number In DB2 /AS400 every record in file will be assigned a number based on entry. This is called RRN or relative record number. We can do cpyf based on record numbers by default it will be from start till end.

Note : Query to check record numbers in SQL. SELECT rrn(EMPLOYEEPF), EMPNO, NAME FROM EMPLOYEEPF SELECT rrn(A), A.* FROM EMPLOYEEPF A

CPYF example based on record number.

CPYF FROMFILE(PRAKASH522/EMPLOYEBK) TOFILE(PRAKASH522/EMPLOYEEPF) MBROPT(* ADD) FROMRCD(2) TORCD(3) FMTOPT(*MAP *DROP)

CPYF based on character test:-

Records in EMPPF file

Copy records where EMPCITY starts with 'H' (First character is 'H') # INCCHAR parameter

CPYF FROMFILE(*LIBL/EMPPF) TOFILE(*LIBL/EMPPFBK) FROMMBR(*FIRST) TOMBR(*FIRST) MBROPT(*REPLACE) CRTFILE(*NO) FROMRCD(*START) INCCHAR(EMPCITY 1 *EQ 'H')

Field test (INCREL) parameter using relational operator :

CPYF FROMFILE(*LIBL/EMPPF) TOFILE(*LIBL/EMPPFBK) FROMMBR(*FIRST) TOMBR(*FIRST) MBROPT(*REPLACE) CRTFILE(*NO) FROMRCD(*START) INCREL((*IF EMPCITY * EQ 'HYD') (*AND DEPTNO *EQ 1))

Copy based on Key

CPYF FROMFILE(EMPPF) TOFILE(EMPPFBK) MBROPT(*REPLACE) FROMKEY(1 (X'00000000100F')) TOKEY(1 (X'00000000103F'))

For composite key use *BLDKEY for number of key fields value. It can also be specified with files having one key , no hexadecimal value is needed for numeric parameter as this attribute converts values.

CPYF FROMFILE(EMPPF) TOFILE(EMPPFBK) MBROPT(*REPLACE) FROMKEY(*BLDKEY (100)) TOKEY(*BLDKEY (101))