Notes on the delimited import file (CPYFRMIMPF command)

 

The characters and data types discussed in this topic interpret the import file's data for a delimited import file.

Blanks

Blanks are treated in the following ways:

  • All leading blanks and trailing blanks are discarded for character fields unless enclosed by string delimiters, according to the RMVBLANK parameter.

  • A field of all blanks is interpreted as a field of one single blank character unless RMVBLANK *NONE is specified.

  • You cannot embed blanks inside numeric data.

  • You cannot select a blank as a field or record delimiter.

The RMVBLANK parameter has the following options:

  • *NONE: All leading and trailing blanks are retained.

  • *LEADING: Leading blanks are removed. This is the default value.

  • *TRAILING: Trailing blanks are removed.

  • *BOTH: Leading and trailing blanks are removed.

Removal of blanks, as specified by the RMVBLANK parameter, takes precedence over string delimiters.

Null fields

A null field can be defined as follows:

  • Two adjacent field delimiters (no data in between).

  • Two adjacent string delimiters (no data in between).

  • A field delimiter followed by a record delimiter (no data in between), an empty string.

If the field is null, the following statement is true:

  • If the record's output field is not nullable and the import is a null field, the record is not to be copied, and an error is signaled.

Delimiters

  • A field or record delimiter cannot be a blank.

  • A string delimiter or a string escape character cannot be the same as a field delimiter, a record delimiter, a decimal point, a date separator, or a time separator.

  • A string delimiter can enclose all fields when the string escape character STRESCCHR(*NONE) is specified. The string delimiter character should not be contained within the character string.

  • A field delimiter can not be the same as a record delimiter.

  • When a string delimiter character is contained by a string, precede the string delimiter in the string with the string escape character specified with the STRESCCHR parameter.

  • The defaults for delimiters are as follows:

    • String is: *DBLQUOTE (double quotation mark)

    • Field is: , (comma)

    • Decimal separator is: . (period)

    • Record is: *EOR (end of record)

  • If the data type of the from is CHARACTER, OPEN, EITHER, or ONLY, all double byte data must be contained within string delimiters or shift characters (for OPEN, EITHER, ONLY data type).

    String delimiters have lower precedence than leading blanks and trailing blanks.

String escape character

When a string delimiter character is contained by a string, you can use the STRESCCHR parameter to specify a string escape character for indicating that the string delimiter character should be treated as a normal character instead of string delimiter.The STRESCCHR parameter has the following options:

  • *NONE: No string escape character is used.

  • *STRDLM: The string delimiter is used as the string escape character.

  • character-value: The character-value is used as the string escape character.

Numeric field

  • Numeric fields can be imported in decimal or exponential form.

  • Data to the right of the decimal point might be truncated depending on the output data format.

  • Decimal separators are either a period or a comma (command option).

  • Signed numeric fields are supported, + or -.

Character or Varcharacter fields

  • Fields too large to fit in the output fields are truncated. The system sends a diagnostic message.

  • An empty string is defined as two string delimiters with no data between them.

  • For the system to recognize a character as a starting or ending string delimiter, it must be the first or last character in the field after the RMVBLANK option has been applied. For example, 'abc' using the single quotation mark (') as the delimiter is the same as abc.

IGC or VarIGC fields

  • The system copies data from the from-file to the to-file. If any of the data is not valid, the system generates a mapping error.

  • Data located between the shift out and shift in characters is treated as double-byte data. This data is also not parsed for delimiters. The shift characters in this case become "string delimiters".

Graphic, VarGraphic fields

The system copies the data from the from-file to the to-file.

CCSIDs

  • The data from the from-file is read into a buffer by the CCSID of the from-file. The data in the buffer is checked and written to the to-file. The CCSID of the open to-file is set to the value of the from-file, unless a to-file CCSID is used.

    If a to-file CCSID is used, the data is converted to that CCSID. If the CCSID of the to-file field is 65535, the data is converted to the CCSID specified in the to CCSID (TOCCSID) parameter. If the from-file is a tape file, and the FROMCCSID(*FILE) is specified, the following limits apply:

    • The job CCSID is used

    • The from-file CCSID is requested by the user

  • The character data (delimiters) passed on the command are converted to the CCSID of the from file. This allows the character data of the from-file and command parameters to be compatible.

Date field

  • All date formats supported by the i5/OS® operating system can be imported (*ISO, *USA, *EUR, *JIS, *MDY, *DMY, *YMD, *JUL, and *YYMD).

  • You can copy a date field to a timestamp field.

Time field

  • All time formats supported by the i5/OS operating system can be imported (*ISO, *USA, *EUR, *JIS, *HMS).

  • You can copy a time field to a timestamp field.

Date and time separators

The system supports all valid separators for date and time fields.

Timestamp field

Timestamp import fields must be 26 bytes. The import ensures that periods exist in the time portion, and a dash exists between the date and time portions of the timestamp.

LOB field

LOB data fields require using secondary stream files that contain the LOB data. The imported data file lists the name of the stream file containing the LOB data. The Field Definition File (FDF) that describes the offsets of the fields in the data file, also describes the offsets of the LOB file name field (not the LOB field itself).

An example of importing LOB data:

FDF file: 

COLCHAR10        1         10         42
COLLOB          12         40         44
*END

The data file would be 44 characters in record length, containing the following data: 

0        1         2         3         4
12345678901234567890123456789012345678901234
aaaaaaaaaa   /lobdata/lob1.dat           N N bbbbbb       /lobdata/lob2.dat           N N              /lobdata/lob3.dat           Y N cccccccccc                               N Y
Notes: For each record in the imported data file:

  1. Characters 1–10 are data for a column named COLCHAR10, with the null indicator at byte position 42.

  2. Characters 12–40 are data for a column named COLLOB, with the null indicator at byte position 44. The COLLOB value is the name of the file which contains the LOB data.

  3. In this example, there are 3 data files (lob1.dat, lob2.dat, and lob3.dat) which contain the actual LOB data to be imported.

Number of fields mismatch

If the from-file or to-file do not have the same number of fields, the data is either truncated to the smaller to-file size, or the extra to-file fields will receive a null value. If the fields cannot contain null values, the issues an error message.

Multiple jobs

The number of jobs that are used to copy the data depends on the DEGREE(*NBRTASKS) parameter of the CHGQRYA command. When multiple jobs are used, the uses batch jobs to copy the data. The user can change, hold or end these batch jobs. The copy does not complete until all the started batch jobs complete.

The relative record numbers can be maintained only if a single job is used and the import file does not contain any deleted records. If the from-file is a distributed physical file or logical file, the performs the copy in a single process.

Files with less than 50 000 records use only one job.

 

Parent topic:

Using the Copy From Import File (CPYFRMIMPF) command to copy between different systems