The copy file command on the AS/400 is an often-used and very powerful command. Many people use this command, but few exploit its full potential. Although I am not attempting to define every possible use of the CPYF command, I want to highlight some of the more overlooked and useful features of this command. I use the CPYF command for a variety of purposes, including setting up test data, as another tool for data analysis and debugging purposes, database conversion, etc. Listed below are some examples of some often-overlooked features of the copy file command and some possible reasons why you may want to consider using them.
CPYF FROMFILE(FILE1) TOFILE(FILE2) INCREL((*IF EMP# *GT 11111)
(*AND STATE *EQ 'OH'))
For files that have external field definitions, this is a good way to pick out records based on values in specific fields.
CPYF FROMFILE(FILE1) TOFILE(FILE2) FROMRCD(1)
If you have a keyed physical file, this will cause the copy file command to ignore the key values and copy by relative record number instead of using the index, which will result in substantially shorter times to perform the copy. I do not put keys on physical files, but if you do and you use this technique be aware that the records in your to file will be in relative record number order and not ordered by key. Also, copying by relative record number is sometimes a good way to isolate blocks of records for debug or test data purposes.
CPYF FROMFILE(FILE3) TOFILE(FILE2) CRTFILE(*YES)
FROMKEY(*BLDKEY ('766654' 01)) TOKEY(*BLDKEY ('766654' 99))
This example uses an existing index to copy a file by using key values. Although copies using keys are vastly slower than copies using a relative record number, copying by key values can significantly speed up the copy process if you can use an index to pick out only the records that you want.
CPYF FROMFILE(FILE1) TOFILE(FILE2) INCCHAR(*RCD 10 *EQ 'AN')
This copies all records where the characters "AN" exist, beginning in position 10 of the record. This is especially useful for selecting records from flat files that do not have any external DDS definitions.
CPYF FROMFILE(FILE1) TOFILE(*PRINT) INCCHAR(*RCD 10 *EQ 'AN')
Using copy file to print the contents of a file is not the most useful format to look at data, but it does list the relative record numbers of all records printed. I use this primarily for those times when I need to get or examine relative record numbers of a specific record or group of records in a subset. If you have a file that is defined to not reuse deleted records (REUSEDLT (* NO)), this can also give you a clue as to the order in which records were written to a file. But it will do it only if the file hasn't been reorganized based on an existing index or hasn't been the target of a copy file.
CPYF FROMFILE(FILE1) TOFILE(FILE2) MBROPT(*REPLACE)
INCCHAR(ORDER# 2 *EQ 65)
This copies all records where the in position 2, or the ORDER# field, the value equals 65. This is especially useful for selecting records based on a partial field value of an externally described file.
CPYF FROMFILE(FILE1) TOFILE(FILE2) FMTOPT(*MAP *DROP)
The FMTOPT(*MAP *DROP) will copy fields by field name from the fromfile to the tofile. This will not copy any fields that exist in the fromfile that don't exist in the tofile, and it will initialize any fields in the tofile that don't exist in the fromfile. As long as your data type has not changed (numeric to alphanumeric, etc.) for field names that exist in both files, this is an excellent tool to convert data when adding new fields, lengthening existing fields, building work files, etc.
About the author: Tim Granatir is vice president of Technical Services for Interlink Technologies in Maumee, Ohio, and he has 17 years' experience working primarily on the IBM midrange platforms.