CPYFRMIMPF (Copy From Import File)

CPYFRMIMPF Command syntax diagram

 

Purpose

The Copy From Import File (CPYFRMIMPF) command copies all or part of an import file to the TOFILE. The term import file is used to describe a file created for purposes of copying data between heterogenous databases. The import file (FROMSTMF or FROMFILE parameter) is called the from file for this command.

An important aspect of this command is its ability to copy the data in parallel. By using the Change Query Attributes (CHGQRYA) command, the number of tasks used to perform the copy is determined by the DEGREE parameter of the CHGQRYA command. The system feature DB2 Symmetric Multiprocessing for OS/400 must be installed for using multiple tasks. See the CHGQRYA command and the example section.

Some of the specific functions that can be performed by the CPYFRMIMPF command include the following:

• Copying a from file to an externally described physical file. The TOFILE must exist on the system before the copy can occur.

  • Limiting the range of records copied based on starting and ending relative record numbers.

• Adding records to an existing file member or replace the contents of a receiving file member (MBROPT parameter).

Error Handling: The escape message CPF2817 is sent for many different error conditions that can occur during a copy operation. At least one diagnostic message that indicates the specific error condition always comes before the escape message. More information on handling errors is in the File Management topic in the Information Center.

Overrides: Overrides are processed for all files.

Status Message: During the running of the CPYFRMIMPF command, message CPI2801 is sent as a status message informing the interactive user that a copy is occurring. More information on preventing status messages from appearing is in the File Management topic in the Information Center.

 

Restrictions

1. The from file and TOFILE cannot be the same file.
   
   
2. The TOFILE must exist prior to the copy.
   
   
3. The TOFILE will not have the same relative record numbers as the from file.
   
   
4. The from file must be a source file, or a valid file with 1 field that is not a numeric data type.
   
   
5. If the from file is defined with the SHARE(*YES) option for the file, unpredictable results can occur. Therefore, if the file is defined with SHARE(*YES), the user should make sure the file is not opened by any process prior to the copy.

Performance:

To increase the performance of the copy:

1. Delete any logical keyed files based on the TOFILE.
   
   
2. Remove all constraints and triggers of the TOFILE.
   
   
3. Ensure the FROMFILE records will be copied correctly by attempting to copy a few of the records, by using the FROMRCD and number of records option, before copying all the records.
   
   
4. Use the ERRLVL(*NOMAX) parameter after knowing the data can be copied correctly.

Notes For Delimited Data:

1. A delimiters can not be a blank(' ') character.
   
   
2. A blank(' ') can not be contained within a numeric field.
   
   
3. Fields in the FROMFILE that are longer than the TOFILE's fields will be truncated(right).
   
   
4. If the data of the FROMFILE does not represent all the fields in the TOFILE, the fields of the TOFILE will be set to null. If this happens and the TOFILE fields do not allow a null value, an error will occur and the record will not be copied to the TOFILE, unless RPLNULLVAL(*FLDDFT) is specified. *FLDDFT will allow the field default value to be inserted in place of the null value.
   
   
5. A null field in the FROMFILE can be specified by 2 adjacent field delimiters, 2 adjacent string delimiters, a field delimiter followed by a record delimiter, or a field of all blanks(' ').

Notes For Fixed Data:

The Field Definition File has the following format:

 
The following example is described:
 
 
Field Definition File to describe
fixed formatted file:

- ***************************************/
- *****  Field Definition File          */
- ***************************************/
-   Description: This Field Definition  */
-   File defines the import's file      */
-   (FROMFILE) field start and          */
-   end positions.                      */
- ***************************************/

FIELD1   1    12   13
FIELD2  14    24    0
FIELD3  25    55   56
FIELD4  78    89   90
FIELD5 100   109    0
*END
 
 
The following is a brief explanation of the
Field Definition File format:
 
    -     =  Comment line
    *END  =  End of definition
 
 
Field     Starting  Ending     Null
Name      Position  Position   Char Position
______________________________________________
 
FIELD1   1         12             13
FIELD2   14        24             None
FIELD3   25        55             56
FIELD4   78        89             90
FIELD5  100        109            None
 
 
 
The name of the field. This name is the
name of the TOFILE field name.
 
Starting Position is the starting position
for the field in the import file of each record.
This is the byte position.
 
Ending Position is the ending position
for the field
in the import file of each record.
This is the byte position.
 
Null Character Position is the position
for the NULL value for the field in the
import file  of each record.
The value 0 is defined to be
that there is not a value for
the NULL.  The value in the import file
can be 'Y' or 'N'.
 
'Y' means the field is NULL.
'N' means the field is not NULL.
 
Each column must be separated
by a blank character.
 

 

Required Parameters

FROMSTMF

Specifies the path name of the stream file from which data is copied. Either this parameter or the FROMFILE parameter is required. See path names for more information on specifying path names.

from-file-path-name: Specify the path name of the input stream file. Note: If the stream file is not in the QSYS.LIB or independent ASP QSYS.LIB > file system, a temporary physical file will be created to contain the data of the stream file. This temporary file will be created in QRECOVERY and named QACPXXXXXX, where XXXXXX is a named generated by the system. The data will then be copied from the temporary file to the TOFILE. After the copy completes, the temporary file will be deleted.

FROMFILE

Specifies the qualified name of the from file that contains the records being copied.

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 name of the file that contains the records to be copied.

Element 2: From File Member

Specifies the from member name.

*FIRST: The first member (in order of creation date) of the from file is used.

Specifying *FIRST is not allowed if the FROMFILE file has no members, unless a member name was specified on an OVRDBF (Override Database File) command for the FROMFILE file.

member-name: Specify the name of the file member to receive the copied records. If a member with the specified name does not already exist in the file, the member will be created.

The from file can be any one of the file types:

• source physical file.
  
  
• DDM file.
  
  
• distributed physical file.
  
  
• program described physical file.
  
  
• single format logical file.
  
  
• physical file with 1 field that has a non-numeric field.
  
  
• tape file.

TOFILE

Specifies the name of the output database file and member that receives the copied records. This parameter is required.

Element 1: To File Name

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 name of the file that receives the copied records.

Element 2: To File Member

Specifies the member name of the output file to receive the copied records.

*FIRST: The first member (in order of creation date) of the output file is used.

Specifying *FIRST is not allowed if the TOFILE has no members, unless a member name was specified on an OVRDBF (Override Database File) command for the TOFILE.

member-name: Specify the name of the file member to receive the copied records. If a member with the specified name does not already exist in the file, the member will be created.

The TOFILE can be any one of the file types:

• source physical file.
  
  
• DDM file.
  
  
• distributed physical file.
  
  
• program described physical file.
  
  
• externally described physical file.

MBROPT

Specifies whether the copy operation replaces, adds, or updates the records in a database file member if a member with the specified name already exists. If the member does not exist, it is created and added to the database file.

Note:

If *ADD or *UPDADD is specified and the TOFILE contains no records, the copy operation completes normally. If *REPLACE is specified and the TOFILE contains no records, the copy operation ends abnormally.

*ADD: The copied records are added to the end of the existing member records.

*REPLACE: The copied records replace the existing member records.

*UPDADD: The system updates the duplicate key records and adds the new records to the end of the existing records. Additional information is available in the File Management topic in the Information Center.

STMFRCDLEN

The maximum record length of any record of the stream file when the DTAFMT(*DLM) is specified, or the actual record length of all the records of the stream file when DTAFMT(*FIXFLD) is specified.

*TOFILE: The record length of the TOFILE record is used.

record-length: The length of the record of the stream file.

FROMCCSID

Specifies the FROMFILE's CCSID.

*FILE: The CCSID of the FROMFILE's CCSID is used. If the FROMFILE is a tape file, the job's default CCSID is used.

CCSID value: The CCSID the data should be copied from if the FROMFILE's CCSID is 65535, or a tape file. If the FROMFILE's CCSID is not 65535, or the FROMFILE is not a tape file, an error condition is created.

RCDDLM

Specifies the record delimiter of the from file.

*EOR: End of record.

*ALL: Any single or double character combination of carriage-return and line-feed.

*CRLF: Carriage-return followed by line-feed.

*LF: Line-feed.

*CR: Carriage-return.

*LFCR: Line-feed followed by carriage-return.

*EOR: End of record.

end-of-line-character: Specify the single character which indicates the end of a single record.

DTAFMT

Specifies the format of the data in the from file.

*DLM: The data contains delimiter characters. Refer to parameter descriptions for STRDLM, FLDDLM, and RCDDLM for information on string, field, and record delimiter characters.

*FIXED: The data format is fixed. The data is in fixed columns in each record. The description of the format of the data is contained in the file member identified by the FLDDFNFILE parameter.

STRDLM

Specifies the string delimiter for the data of the fields being copied from. This character indicates the start and end of character, date, time, and timestamp strings in the from file. Depending on the utility used to create the from file, some types of strings may appear in the from file without string delimiter characters.

'"': A double quote (") is used as the string delimiter.

*NONE: No delimiter is expected as the string delimiter. The blank character ( ) represents the *NONE value.

character-value: Specify the character value for the string delimiter.

RMVBLANK

Specifies whether leading blanks are removed or retained on character fields when STRDLM(*NONE) is specified.

*LEADING: Leading blanks are removed.

*NONE: The leading blanks are retained on character fields when STRDLM(*NONE) is specified.

FLDDLM

Specifies the field delimiter for the record being copied from. This value is used to determine where one field ends and the next field begins.

',': The comma character is the default name of the field delimiter.

*TAB: The tab character is the delimiter.

character-value: Specify the character value for the field delimiter.

FLDDFNFILE

Specifies the field definition file which defines the format of the data when DTAFMT(*FIXFLD) is specified. For details on the required format for this file, refer to the File Management topic in the Information Center. If DTAFMT(*FIXFLD) is specified, this parameter is required.

Element 1: Field Definition File

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 name of the file that contains the fixed field definition.

Element 2: Field Definition File Member

Specifies the database file member name of the field definition file.

*FIRST: The first member (in order of creation date) in the field definition file is used.

member-name: Specify the name of the field definition file member to use. The field definition file can be any one of the file types:

• source physical file.
  
  
• DDM file.
  
  
• distributed physical file.
  
  
• program described physical file.
  
  
• externally described physical file with 1 field.

DECPNT

Specifies the decimal point character to be used when copying numeric data from the from file.

*PERIOD: A period (.) is used for the decimal point character.

*COMMA: A comma (,) is used for the decimal point character.

DATFMT

Specifies the date format to be used when copying date fields from the from file.

*ISO: The International Organization for Standardization (ISO) date format yyyy-mm-dd is used.

*USA: The United States date format mm/dd/yyyy is used.

*EUR: The European date format dd.mm.yyyy is used.

*JIS: The Japanese Industrial Standard date format yyyy-mm-dd is used.

*MDY: The date format mm/dd/yy is used.

*DMY: The date format dd/mm/yy is used.

*YMD: The date format yy/mm/dd is used.

*JUL: The Julian date format yy/ddd is used.

*YYMD: The date format yyyymmdd is used.

DATSEP

Specifies the date separator for the date format. The separator is ignored for DATFMT of *ISO, *USA, *EUR, and *JIS because these formats have a fixed date separator.

'/': A forward slash (/) is used as the date separator character.

'-': A dash (-) is used as the date separator character.

'.': A period (.) is used as the date separator character.

',': A comma (,) is used as the date separator character.

*BLANK: A blank ( ) is used as the date separator character.

TIMFMT

Specifies the time format to be used when copying time fields from the from file.

*ISO: The International Organization for Standardization (ISO) time format hh.mm.ss is used.

*USA: The United States time format hh:mmxx is used, where xx is AM or PM.

*EUR: The European time format hh.mm.ss is used.

*JIS: The Japanese Industrial Standard time format hh:mm:ss is used.

*HMS: The hh:mm:ss format is used.

TIMSEP

Specifies the time separator for the time format. The parameter is ignored is TIMFMT is *ISO, *USA, *EUR, or *JIS because these formats define the required time separator character.

':': A colon (:) is used as the time separator character.

'.': A period (.) is used as the time separator character.

*BLANK: A blank ( ) is used as the time separator character.

FROMRCD

Specifies which records are copied from the from file.

Element 1: Starting Record Number

*START: The copy operation begins with the first record in the from file.

starting-record-number: Specify a record number, ranging from 1 through 4294967288, that identifies the first record copied from the from file. If both FROMRCD and TORCD record number values are specified, the FROMRCD value must be less than or equal to the TORCD value.

Element 2: Number of Records to Copy

Specifies the number of records to be copied from the from file.

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

number-of-records: Specify a number, ranging from 1 through 4294967288, that identifies the number of records to be copied from the from file. If an end-of-file condition is reached before this number of records have been copied, no error messages are issued.

ERRLVL

Specifies the maximum number of recoverable read or write errors for the TOFILE that are tolerated during the copy operation.

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

0: No errors are allowed.

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

ERRRCDFILE

Specifies the database file where the records that are in error should be written.

*NONE: No error record file is provided.

Element 1: Error Record File Name

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 error record file name.

Element 2: Error Record File Member

Specifies which member of the error file is used to contain the from file records which contained errors.

*FIRST: The first member (in order of creation date) in the error file is used.

member-name: Specify the member name of the file. The error record file can be any one of the file types:

• source physical file.
  
• DDM file.
  
• distributed physical file.
  
• program described physical file.
  
• externally described physical file.

ERRRCDOPT

Specifies how error records are added to the error record file.

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

*REPLACE: The system deletes any existing records and adds the new records.

RPLNULLVAL

Specifies whether null field values will be replaced when copying import file records.

*NO: Null values will not be replaced. If a null value is detected when parsing an import file record, an error message is sent and the copy operation fails.

*FLDDFT: If a null value is detected when parsing an import file record, the corresponding field in the database file record is assigned a default value based on the field type or DDS default value.

IDCOL

Specifies if the to-file is an SQL table which contains a column with the IDENTITY atrribute or a column with the ROWID data type, whether the value for the column will be generated by the system or the default value is used.

*GEN: A system-generated value will be inserted into the Identity Column or ROWID column.

*FROMFLD: If a value exists in the Identity Column or ROWID column of the fromfile field, this value will be inserted into the Identity Column of the to-file.>

Examples for CPYFRMIMPF

Example 1: Copying Physical File Import File

CHGQRYA DEGREE(*NBRTASKS 3)
 
CPYFRMIMPF   FROMFILE(IMPFILE)  TOFILE(DB2FILE)
  FLDDLM(';') RCDDLM(X'07')
  DATFMT(*JIS) TIMFMT(*JIS)

The Change Query Attribute (CHGQRYA) is run prior to CPYFRMIMPF to allow the copy processing to be done by three tasks running in parallel.

All records of from file IMPFILE will be copied to the externally described physical file DB2FILE. Fields in the from file are delimited by semi-colon (;) characters. Each record in the from file is delimited by a hexadecimal '07' character. Input date fields are are in yyyy-mm-dd format. Input time fields are in hh:mm:ss format.

Example 2: Copying Tape File Import File

OVRTAPF   FILE(QTAPE) DEV(TAP02) SEQNBR(3)
 
CPYFRMIMPF   FROMFILE(QTAPE) TOFILE(DB2WHS)
  ERRFILE(IMPERR)

The Override Tape File (OVRTAPF) parameter is run prior to CPYFRMIMPF to indicate that tape device TAP02 should be used for doing the copy. The from file must be the third file on the tape mounted on TAP02.

All records of the from file will be copied to the externally described physical file DB2WHS. Fields in the from file are delimited by comma (,) characters. Input date fields are are in yyyy-mm-dd (ISO) format. Input time fields are in hh.mm.ss (ISO) format. From file records that are found to contain errors and cannot be added to file DB2WHS are added to error file IMPERR.

Error messages for CPYFRMIMPF

*ESCAPE Messages

CPF2817

Copy command ended because of error.