Output field types

An output field can have a different data type from the one that is defined on a source table column if the data types are compatible. The UNLOAD utility follows the general Db2 rules and conventions for the data type attributes and the compatibility among the data types.

If you specify a data type in the UNLOAD control statement, the field type information is included in the generated LOAD utility statement. For specific data type compatibility information, refer to the following table. These tables show the compatibility of the data type of the source column (input data type) with the data type of the output field (output data type). A Y indicates that the input data type can be converted to the output data type.

The following table shows the compatibility of converting numeric data types.

Table 1. Compatibility of converting numeric data types
Input data types	Output data types
Input data types	SMALLINT	INTEGER (external)	BIGINT	DECIMAL (external)	FLOAT (external)	DOUBLE or REAL	FLOAT/REAL
SMALLINT	Y	Y¹	Y	Y¹	Y¹	Y	Y
INTEGER	Y²	Y¹	Y	Y¹	Y¹	Y	Y
BIGINT	Y²	Y²	Y²	Y	Y	N	Y
DECIMAL	Y²	Y^{1, 2}	Y²	Y¹	Y¹	Y	Y
FLOAT, DOUBLE, or REAL	Y²	Y^{1, 2}	Y²	Y^{1, 2}	Y¹	Y	Y
DECFLOAT	Y²	Y^{1, 2}	Y²	Y^{1, 2}	Y^{1, 2}	N²	Y³
Note: Subject to the CCSID conversion, if specified (EXTERNAL case). Potential overflow (conversion error). When converting from DECFLOAT(34) to DECFLOAT(16), you might encounter overflow, underflow, subnormal number, or inexact. However, there will be no conversion error.

The following table shows the compatibility of converting character data types.

Table 2. Compatibility of converting character data types
Input data types	Output data types
Input data types	BLOB	CHAR	VAR- CHAR	CLOB	GRAPHIC	GRAPHIC EXTER- NAL	VAR- GRAPHIC	DB- CLOB	BINARY	VAR- BINARY
BLOB	Y	N	N	N	N	N	N	N	N	N
CLOB	N	Y^{1, 2}	Y^{1, 2}	Y	N	N	N	N	N	N
DBCLOB	N	N	N	N	Y^{1, 2}	Y^{1, 2, 3}	Y^{1, 2}	Y¹	N	N
CHAR	N	Y¹	Y¹	Y^{1, 4}	N	N	N	N	Y	Y
VARCHAR or LONG VARCHAR	N	Y^1,2	Y¹	Y^{1, 4}	N	N	N	N	Y	Y
GRAPHIC	N	N	N	N	Y¹	Y^{1, 3}	Y¹	Y¹	N	N
VAR- GRAPHIC or LONG VAR- GRAPHIC	N	N	N	N	Y^{1, 2}	Y^{1, 2, 3}	Y¹	Y¹	N	N
BINARY	Y	N	N	N	N	N	N	N	Y	Y
VARBINARY	Y	N	N	N	N	N	N	N	Y	Y
Note: Subject to the CCSID conversion, if specified. Results in an error if the field length is too small for the data unless you specify the TRUNCATE option. Note that a LOB has a 4-byte length field; any other varying-length type has a 2-byte length field. Only in the EBCDIC output mode. Not applicable to BIT subtype data.

The following table shows the compatibility of converting time data types.

Table 3. Compatibility of converting time data types
Input data types	Output data types
Input data types	DATE EXTERNAL	TIME EXTERNAL	TIMESTAMP EXTERNAL	TIMESTAMP WITH TIME ZONE EXTERNAL
DATE	Y¹	N	Y^{1, 2}	Y^1,2
TIME	N	Y¹	N	N
TIMESTAMP	Y^{1, 3}	Y^{1, 3}	Y¹	Y^1,2
TIMESTAMP WITH TIME ZONE	Y^1,4	Y^1,4	Y^1,4	Y¹
Note: Subject to the CCSID conversion, if specified. Zeros in the time portion. IMPLICIT_TZ in time zone portion if the output data type is TIMESTAMP WITH TIME ZONE. DATE or TIME portion of the timestamp. DATE, TIME or TIMESTAMP portion of the timestamp with time zone.