Conversion of input data

The LOAD utility converts data between compatible data types. The source type is used for user-defined distinct types.

The tables shown below identify the compatibility of data types for assignments and comparisons. Y indicates that the data types are compatible. N indicates that the data types are not compatible. D indicates the defaults that are used when you do not specify the input data type in a field specification of the INTO TABLE statement.

The following table shows the compatibility of numeric data types.

Table 1. Compatibility of converting numeric data types.
Input data types Output data types
SMALLINT BIGINT INTEGER DECIMAL FLOAT DECFLOAT
SMALLINT D Y Y Y Y Y
BIGINT Y D Y Y Y Y
INTEGER Y Y D Y Y Y
DECIMAL Y Y Y D Y1 Y1
FLOAT Y Y Y Y D Y
DECFLOAT Y Y Y Y Y D
Notes:
  1. Loading a DECFLOAT or FLOAT column from a DECIMAL PACKED input field can produce unpredictable results. Instead, use the DECIMAL EXTERNAL format for the input field.

The following table shows the compatibility of character data types.

Table 2. Compatibility of converting character data types.
Input data types Output data types
BLOB CHAR
VAR-
CHAR
 CLOB GRAPHIC
VAR-
GRAPHIC
 DBCLOB ROWID BINARY
VAR-
BINARY
CHAR Y D Y Y Y1 Y1 Y1 Y Y Y
CHAR MIXED Y D Y Y Y1 Y1 Y1 N Y Y
VARCHAR Y Y D Y Y1 Y1 Y1 Y Y Y
VARCHAR MIXED Y Y D Y Y1 Y1 Y1 N Y Y
GRAPHIC N Y1 Y1 Y1 D Y Y N N N
VAR-GRAPHIC N Y1 Y1 Y1 Y D Y N N N
ROWID N N N N N N N D N N
BINARY Y N N N N N N N D Y
VAR-BINARY Y N N N N N N N Y D
Note:
  1. Conversion applies when either the input data or the target table is Unicode.

The following table shows the compatibility of time data types.

Start of change
Table 3. Compatibility of converting time data types.
Input data types Output data types
DATE TIME TIMESTAMP TIMESTAMP WITH TIME ZONE
DATE EXTERNAL D N N N
TIME EXTERNAL N D N N
TIMESTAMP EXTERNAL Y Y D Y1
TIMESTAMP WITH TIME ZONE EXTERNAL Y Y Y D
Note: Start of change
  1. If the data type of the target column is TIMESTAMP WITH TIME ZONE and the timestamp value that is being loaded does not contain a time zone, the LOAD utility uses the value that you specify for the IMPLICIT_TZ option. If you do not specify this option, DB2® uses the value from the IMPLICIT_TIMEZONE DECP value. For more information about this DECP value, see IMPLICIT TIME ZONE field (IMPLICIT_TIMEZONE DECP value).
End of change
End of change

Input fields with data types CHAR, CHAR MIXED, CLOB, DBCLOB, VARCHAR, VARCHAR MIXED, GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC are converted from the CCSIDs of the input file to the CCSIDs of the table space when they do not match. For example:

  • You specify the ASCII or UNICODE option for the input data, and the table space is EBCDIC.
  • You specify the EBCDIC or UNICODE option, and the table space is ASCII.
  • You specify the ASCII or EBCDIC option, and the table space is Unicode.
  • The CCSID option is specified, and the CCSIDs of the input data are not the same as the CCSIDs of the table space.

CLOB, BLOB, and DBCLOB input field types cannot be converted to any other field type.

Conversion errors cause LOAD:

  • To abend, if no discard data set is provided or if the discard limit is exceeded.
  • To map the input record for subsequent discarding and continue (if a discard data set is provided)

Truncation of the decimal part of numeric data is not considered a conversion error.