System i: Copying between different database record formats (FMTOPT parameter)

Copying between different database record formats (FMTOPT parameter)

When you copy from a database file to a database file, use the FMTOPT parameter if the record formats are not identical or if the files are different types (source or data). If either file is a device file or inline data file, the FMTOPT parameter does not apply. The records are truncated or padded with blanks or zeros when record lengths are different. A message is sent if the records are truncated.
You can use this parameter on the CPYF and CPYFRMQRYF commands.
For database files, when either FMTOPT(*CVTSRC) or FMTOPT(*NOCHK) is specified and the record data copied from any from-file record is not long enough to fill a to-file record, the extra bytes in the to-file record are set to a default value. If a default value other than *NULL is specified for the DFT keyword of the database description specifications (DDS) for a field, that field is initialized to the specified default. Otherwise, all numeric fields are initialized to zeros, all character fields are initialized to blanks, all date, time, and timestamp fields are initialized to the current system date and time. If *NULL is specified on the DFT keyword, only the default buffer value is used. The *NULL default is ignored.
If the from-file or to-file is a device file or an inline data file, copy automatically adds or deletes the source sequence number and date fields for each record copied.
If one file is a data file and the other a source file, specify FMTOPT(*CVTSRC) to perform the copy. The sequence number and date fields are added or deleted as appropriate and the data part of each record is copied without regard to the other field definitions in the file record formats. The SRCSEQ parameter can be used to control how the sequence numbers are created, provided SRCOPT(*SEQNBR) is also specified.
Binary character fields are padded with zeros when using FMTOPT *MAP. For database-to-database copies, you can reconcile any differences in record formats by specifying:

*DROP to drop those fields in the from-file record format for which there are no fields of the same name in the to-file record format.

*MAP to convert fields in the from-file to the attributes of like-named fields in the to-file and to fill extra fields in the to-file, that are not in the from-file, with their default values. The default values are:

The parameter value (including *NULL) for the DFT keyword, if specified for the field

Blanks (for character fields without the DFT keyword)

Zeros (for numeric fields without the DFT keyword)

Current date, time, or timestamp for those type fields without the DFT keyword

*MAP is required if fields with the same name are in different positions in the file record formats, even though these fields have the same attributes.

*DROP and *MAP to drop fields in the from-file not named in the to-file and to convert remaining fields through mapping rules to fit the to-file fields that have different attributes or positions.

*NOCHK to disregard the differences. Data is copied left to right directly from one file to the other. Null values are ignored. The copied records are either truncated or padded with default buffer values. Because no checking is done, fields in the to-file might contain data that is not valid for the field as defined.

Dropping and mapping fields are based on a comparison of field names. Unless all the fields in the from-file have the same name in the to-file, specify *DROP. If the names are the same, but the attributes or position in the record is different, specify *MAP. Dropped fields are not copied. There must be at least one like-named field in both record formats to do mapping.
When *MAP is specified, fields in the to-file record format that do not exist in the from-file record format are filled with their default values, as described earlier in this section. For fields that have the same name and attributes, the field in the from-file record format is mapped to the field with the same name in the to-file record format, even if their positions in the formats are different.
For example, the field CUSNO is the first field in the record format ORDHD, but it is the second field in record format ORDHD1. When the CUSNO field is copied with *MAP, it is mapped to the second field of ORDHD1.
It is possible for files with large record formats (many fields) to have the same format level identifiers even though their formats might be slightly different. Problems can occur when copying these files if the record format names of the from-file and the to-file are the same. When copying such files using FMTOPT(*NONE) or FMTOPT(*MAP), it is recommended that the record format names of the from-file and the to-file be different.
Table 1 summarizes the database-to-database copy operations for each value on the FMTOPT parameter.

Table 1. Database-to-Database copy operations
FMTOPT parameter values (see note 4) Database file record formats
All field names in From-and To-Files are the same (like-named) Some field names in From-and To-Files are the same No field names in either file are the same

Attributes and relative order also the same (see note 1) Attributes and relative order not the same (see note 1) Like-named fields have identical attributes and relative order (see note 1) Not all like-named fields have identical attributes and relative order (see note 1)
*NONE Complete copy Command ends Command ends Command ends Command ends
*DROP Complete copy (value ignored) Command ends If there are extra fields in the from-file, they are dropped, all others are copied. If there are extra fields in the to-file, the command ends. If there are extra fields in the from-file and in the to-file, the command ends. Command ends Command ends
*MAP (see note 2) Complete copy (value ignored) Complete copy (corresponding fields are mapped) If there are extra fields in the from-file, the command ends. If there are extra fields in the to-file, they are filled, and the like-named fields are mapped. If there are extra fields in the to-file and the from-file, the command ends. Command ends
*MAP and *DROP (see note 2) Complete copy (value ignored) Complete copy (corresponding fields are mapped) Extra fields in the from-file are dropped; like-named fields are mapped; extra fields in the to-file are filled. Command ends
*NOCHK Complete copy (value ignored) Complete copy (direct data transfer disregarding fields) (see note 3)
Notes:

Field attributes include the data type (character, zoned, packed, binary or floating point), field length, decimal position (for numeric fields), date or time format (for date or time fields), null capability, CCSID, and whether the field has variable length or fixed length.

Mapping consists of converting the data in a from-file field to the attributes of the corresponding (like-named) to-file field. If the attributes of any corresponding fields are such that the data cannot be converted, the copy is ended.

The records are padded or truncated as necessary. Data in the from-file might not match the to-file record format.

Any other value specified for the FMTOPT parameter is ignored when the *CVTFLOAT value or the *NULLFLAGS value is specified (except the *CVTFLOAT and *NULLFLAGS values).

Specifying data for different field types and attributes
You can use FMTOPT(*MAP) to map data between fixed-length and variable-length fields and between variable-length fields with different maximum lengths.

Converting universal coded character set (UCS-2) graphic fields
You can use FMTOPT(*MAP) to convert universal coded character set (UCS-2) graphic fields.

Converting universal coded character set transformation format (UTF-8 character and UTF-16 graphic)
You can use FMTOPT(*MAP) to convert universal coded character set transformation format.

Converting System/370 floating-point and null fields
To copy floating-point fields and null fields that are in a System/370™ format to an i5/OS format, use FMTOPT(*CVTFLOAT) for the floating-point fields and FMTOPT(*NULLFLAGS) for the null fields. You can use these two values together in one command: FMTOPT(*CVTFLOAT *NULLFLAGS).

Conversion rules for copying files
Here are the conversion rules the system follows when copying files.

Parent topic:
Copying files

FMTOPT parameter values (see note 4)	Database file record formats
FMTOPT parameter values (see note 4)	All field names in From-and To-Files are the same (like-named)	Some field names in From-and To-Files are the same	No field names in either file are the same
	Attributes and relative order also the same (see note 1)	Attributes and relative order not the same (see note 1)	Like-named fields have identical attributes and relative order (see note 1)	Not all like-named fields have identical attributes and relative order (see note 1)
*NONE	Complete copy	Command ends	Command ends	Command ends	Command ends
*DROP	Complete copy (value ignored)	Command ends	If there are extra fields in the from-file, they are dropped, all others are copied. If there are extra fields in the to-file, the command ends. If there are extra fields in the from-file and in the to-file, the command ends.	Command ends	Command ends
*MAP (see note 2)	Complete copy (value ignored)	Complete copy (corresponding fields are mapped)	If there are extra fields in the from-file, the command ends. If there are extra fields in the to-file, they are filled, and the like-named fields are mapped. If there are extra fields in the to-file and the from-file, the command ends.		Command ends
MAP and DROP (see note 2)	Complete copy (value ignored)	Complete copy (corresponding fields are mapped)	Extra fields in the from-file are dropped; like-named fields are mapped; extra fields in the to-file are filled.		Command ends
*NOCHK	Complete copy (value ignored)	Complete copy (direct data transfer disregarding fields) (see note 3)
Notes: Field attributes include the data type (character, zoned, packed, binary or floating point), field length, decimal position (for numeric fields), date or time format (for date or time fields), null capability, CCSID, and whether the field has variable length or fixed length. Mapping consists of converting the data in a from-file field to the attributes of the corresponding (like-named) to-file field. If the attributes of any corresponding fields are such that the data cannot be converted, the copy is ended. The records are padded or truncated as necessary. Data in the from-file might not match the to-file record format. Any other value specified for the FMTOPT parameter is ignored when the CVTFLOAT value or the NULLFLAGS value is specified (except the CVTFLOAT and NULLFLAGS values).