Unloading delimited files

You can use the DELIMITED option to specify that UNLOAD is to produce an output file in delimited format. All fields in the output data set are either in character string or numeric external format. Each column is separated from the next by a column delimiter, and character strings are marked by character string delimiters.

Recommendation: If a delimited file is to be transferred to or from a platform other than z/OS® or between Db2® for z/OS systems that use different EBCDIC or ASCII CCSIDs, use Unicode as the encoding scheme for the delimited file. Using Unicode avoids possible CCSID translation problems.

You are responsible for ensuring that the chosen delimiters are not part of the data in the file. If the delimiters are part of the file's data, unexpected errors can occur.

Restrictions: The following general restrictions apply to the use of delimiters:
  • You cannot specify the same character for more than one type of delimiter (COLDEL, CHARDEL, and DECPT).
  • You can specify a character constant for a delimiter if the utility control statement is coded in the same encoding scheme as the output file. For example, the utility control statement is coded in Unicode and the output data is also coded in Unicode.
  • Use the hex representation for non-default delimiters if the utility control statement is coded in a different encoding scheme than the output file. For example, the utility control statement is coded in Unicode and the output file is coded in EBCDIC. In this case, if you do not use the hex representation for the non-default delimiters, the results can be unpredictable.
  • You cannot specify HEADER OBID and ROWID for output fields in delimited output format. Because a header is not allowed, output must be from a single table.
  • When you specify the DELIMITED option, the utility ignores the POSITION keyword. The utility overrides field data type specifications according to the specifications of the delimited format. (For example, length values for CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, CLOB, DBCLOB, and BLOB data are the delimited lengths of each field in the output data set, and the utility unloads all numeric types in external format.)
  • You cannot specify a binary 0 (zero) for any delimiter.
  • No null byte is present for a delimited output file. A null value is indicated by the absence of a cell value where one would normally occur. For example, two successive column delimiters or a missing column at the end of a record indicate a null value.
  • You cannot use the default decimal point as a character string delimiter (CHARDEL) or a column string delimiter (COLDEL).
  • Shift-in and shift-out characters cannot be specified as EBCDIC MBCS delimiters.
  • In the DBCS environment, the pipe character ( | ) is not supported.
  • If the output is coded in ASCII or Unicode, you cannot specify any of the following values for any delimiter: X'0A', X'0D', X'2E'.
  • If the output is coded in EBCDIC, you cannot specify any of the following values for any delimiter: X'15', X'0D', X'25'.
  • If the output is coded in EBCDIC DBCS or MBCS, you cannot specify any of the following values for character string delimiters: X'0D', X'15', X'25', X'4B'.

The following table lists by encoding scheme the default hex values for the delimiter characters.

Table 1. Default delimiter values for different encoding schemes
Character EBCDIC SBCS EBCDIC DBCS/MBCS ASCII/Unicode SBCS ASCII/Unicode MBCS
Character string delimiter X'7F' X'7F' X'22' X'22'
Decimal point character X'4B' X'4B' X'2E' X'2E'
Column delimiter X'6B' X'6B' X'2C' X'2C'

In most EBCDIC code pages, the hex values in the previous table represent a double quotation mark(") for the character string delimiter, a period(.) for the decimal point character, and a comma(,) for the column delimiter.

The following table lists by encoding scheme the maximum allowable hex values for any delimiter character.

Table 2. Maximum delimiter values for different encoding schemes
Encoding scheme Maximum allowable value
EBCDIC SBCS None
EBCDIC DBCS/MBCS X'3F'
Start of change(X'7F', X'4B', X'6B' are also allowed.)End of change
ASCII/Unicode SBCS None
ASCII/Unicode MBCS X'7F'

The following table identifies the acceptable data type forms for the delimited file format that the LOAD and UNLOAD utilities use.

Table 3. Acceptable data type forms for delimited files
Data type Acceptable form for loading a delimited file Form that is created by unloading a delimited file
CHAR, VARCHAR A delimited or non-delimited character string Character data that is enclosed by character delimiters. For VARCHAR, length bytes do not precede the data in the string.
GRAPHIC (any type) A delimited or non-delimited character stream Data that is unloaded as a delimited character string. For VARGRAPHIC, length bytes do not precede the data in the string.
INTEGER (any type) A stream of characters that represents a number in EXTERNAL format Numeric data in external format.
Decimal (any type) A character stream that represents a number in EXTERNAL format A string of characters that represents a number.
FLOAT Representation of a number in the range -7.2E + 75 to 7.2E + 75in EXTERNAL format A string of characters that represents a number in floating-point notation.
BLOB, CLOB A delimited or non-delimited character string Character data that is enclosed by character delimiters. Length bytes do not precede the data in the string.
DBCLOB A delimited or non-delimited character string Character data that is enclosed by character delimiters. Length bytes do not precede the data in the string.
DATE A delimited or non-delimited character string that contains a date value in EXTERNAL format A string of characters that represents a date.
TIME A delimited or non-delimited character string that contains a time value in EXTERNAL format A string of characters that represents a time.
TIMESTAMP A delimited or non-delimited character string that contains a timestamp value in EXTERNAL format A string of characters that represents a timestamp.
XML A delimited or non-delimited XML character string A string of characters that represents an XML document.