Supported data type conversions
The following sections describe the allowable data conversions and default output lengths.
A blank cell in a table indicates that the data type conversion is not allowed.
Numeric output
The following table lists the conversions that UNLOAD PLUS supports to numeric output:
Output field | SMALLINT | INTEGER | BIGINT | SMALLINT, INTEGER, or BIGINT EXTERNAL | DECIMAL | DECIMAL ZONED | FLOAT | DECFLOAT | DECIMAL, FLOAT, or DECFLOAT EXTERNAL |
|---|---|---|---|---|---|---|---|---|---|
Input column | |||||||||
SMALLINT | D | X | X | X | X | X 1 | X 2 | X | X 3 |
INTEGER | X | D | X | X | X | X 1 | X 2 | X | X 3 |
BIGINT | X | X | D | X | X | X 1 | X 2 | X | X 3 |
DECIMAL | X | X | X | X | D | X 1 | X 2 | X | X 3 |
FLOAT | X | X | X | X | X | X | D | X | X 3 |
DECFLOAT | X | X | X | X | X |
| X | D | |
CHAR | X | X | X |
| X |
| X | X |
|
VARCHAR | X | X | X |
| X |
| X |
|
|
GRAPHIC |
|
|
|
|
|
|
|
|
|
VARGRAPHIC |
|
|
|
|
|
|
|
|
|
BINARY |
|
|
|
|
|
|
|
|
|
VARBINARY |
|
|
|
|
|
|
|
|
|
DATE |
|
|
|
|
|
|
|
|
|
TIME |
|
|
|
|
|
|
|
|
|
TIMESTAMP |
|
|
|
|
|
|
|
|
|
TIMESTAMP WITH TIME ZONE |
|
|
|
|
|
|
|
|
|
BLOB |
|
|
|
|
|
|
|
|
|
CLOB |
|
|
|
|
|
|
|
|
|
DBCLOB |
|
|
|
|
|
|
|
|
|
XML |
|
|
|
|
|
|
|
|
|
The column values are: X = Allowable conversion D = Allowable conversion and indicates the default output data type, which is the data type of the column N = Allowable conversion but not supported with DIRECT NO | |||||||||
1 For integer and decimal columns, you can assign positive or negative overpunch values to decimal-zoned numeric output.
2 For integer and decimal columns, conversion to FLOAT is inexact, and the value in the floating-point output might not be identical to the value in the input column.
3 When making an allowable conversion from a numeric column to a CHAR, VARCHAR, DECIMAL EXTERNAL, FLOAT EXTERNAL, or DECFLOAT EXTERNAL field, you must allow space for punctuation. Punctuation includes the minus sign, decimal point, exponent marker, exponent sign, and exponent value.
4 When converting from DECFLOAT to DECFLOAT EXTERNAL, if the length that you specify is not long enough to contain the significant digits of the input value without rounding, UNLOAD PLUS discards the row.
The following considerations apply to the default output length information for these fields:
- With FORMAT EXTERNAL, the length defaults to the following value unless EXTERNAL is specified on the field:
- SMALLINT defaults to 6.
- INTEGER defaults to 11.
- BIGINT defaults to 20.
- DECIMAL defaults to 17.
- For DECFLOAT columns converting to DECFLOAT EXTERNAL output fields, the default length depends on the source column:
- If the source column is defined with a length of 8 bytes, the default is 23.
- If the source column is defined with a length of 16 bytes, the default is 42.
The following table lists the default output lengths for the conversions:
Output field | SMALLINT | INTEGER | BIGINT | SMALLINT, INTEGER, or BIGINT EXTERNAL | DECIMAL | DECIMAL ZONED | FLOAT | DECIMAL or FLOAT EXTERNAL | DECFLOAT | DECFLOAT EXTERNAL |
|---|---|---|---|---|---|---|---|---|---|---|
Input column | ||||||||||
SMALLINT | 2 | 4 | 8 | X | X | X | X | X | 16 | 42 |
INTEGER | 2 | 4 | 8 | X | X | X | X | X | 16 | 42 |
BIGINT | 2 | 4 | 8 | X | X | X | X | X | 16 | 42 |
DECIMAL | 2 | 4 | 8 | X | C | C | X | X | 16 | 42 |
FLOAT | 2 | 4 | 8 | X | X | X | C | X | 16 | 42 |
DECFLOAT | 2 | 4 | 8 | X | X |
| X | X | C | C |
CHAR | 2 | 4 | 8 |
| X |
| X |
| 16 |
|
VARCHAR | 2 | 4 | 8 |
| X |
| X |
|
|
|
GRAPHIC |
|
|
|
|
|
|
|
|
|
|
VARGRAPHIC |
|
|
|
|
|
|
|
|
|
|
BINARY |
|
|
|
|
|
|
|
|
|
|
VARBINARY |
|
|
|
|
|
|
|
|
|
|
DATE |
|
|
|
|
|
|
|
|
|
|
TIME |
|
|
|
|
|
|
|
|
|
|
TIMESTAMP |
|
|
|
|
|
|
|
|
|
|
TIMESTAMP WITH TIME ZONE |
|
|
|
|
|
|
|
|
|
|
BLOB |
|
|
|
|
|
|
|
|
|
|
CLOB |
|
|
|
|
|
|
|
|
|
|
DBCLOB |
|
|
|
|
|
|
|
|
|
|
XML |
|
|
|
|
|
|
|
|
|
|
Numeric values in a column indicate the required output length. X = Explicit length is required. C = The default length is defined by the column. For DECIMAL and DECFLOAT, the precision is defined by the column. N= The length is defined by the input value. L = If your DSNHDECP format is LOCAL, the length is the local DATE and TIME length. If your DSNHDECP format is ISO, USA, EUR, or JIS, the default length for DATE is 10 and the default length for TIME is 8. T = See Supported-DATE-TIME-and-TIMESTAMP-formats for length information. | ||||||||||
Character, graphic, and binary output
The following table lists the conversions that UNLOAD PLUS supports to character, graphic, and binary output. When you specify FORMAT CSV or FORMAT XML, UNLOAD PLUS does not support conversion to VARCHAR, VARGRAPHIC, BINARY, VARBINARY, or ROWID data types.
Output field | CHAR | VARCHAR | CHAR or VARCHAR BLOBF | CHAR or VARCHAR CLOBF | CHAR or VARCHAR DBCLOBF | GRAPHIC | GRAPHIC EXTERNAL | VARGRAPHIC | BINARY | VARBINARY |
|---|---|---|---|---|---|---|---|---|---|---|
Input column | ||||||||||
SMALLINT | X 1 | X 1 |
|
|
|
|
|
|
|
|
INTEGER | X 1 | X 1 |
|
|
|
|
|
|
|
|
BIGINT | X 1 | X 1 |
|
|
|
|
|
|
|
|
DECIMAL | X 1 | X 1 |
|
|
|
|
|
|
|
|
FLOAT | X 1 | X 1 |
|
|
|
|
|
|
|
|
DECFLOAT |
|
|
|
|
|
|
|
|
|
|
CHAR | D | X |
|
|
|
|
|
|
|
|
VARCHAR | X | D |
|
|
|
|
|
|
|
|
GRAPHIC |
|
|
|
|
| D | X | X |
|
|
VARGRAPHIC |
|
|
|
|
| X | X | D |
|
|
BINARY |
|
|
|
|
|
|
|
| D | X |
VARBINARY |
|
|
|
|
|
|
|
| X | D |
DATE | X | X |
|
|
|
|
|
|
|
|
TIME | X | X |
|
|
|
|
|
|
|
|
TIMESTAMP | X | X |
|
|
|
|
|
|
|
|
TIMESTAMP WITH TIME ZONE | X | X |
|
|
|
|
|
|
|
|
BLOB |
|
| X |
|
|
|
|
|
|
|
CLOB |
|
|
| X |
|
|
|
|
|
|
DBCLOB |
|
|
|
| X |
|
|
|
|
|
XML |
|
| X 2 | X |
|
|
|
|
|
|
The column values are: X = Allowable conversion D = Allowable conversion and indicates the default output data type, which is the data type of the column N = Allowable conversion but not supported with DIRECT NO | ||||||||||
1 When making an allowable conversion from a numeric column to a CHAR, VARCHAR, DECIMAL EXTERNAL, FLOAT EXTERNAL, or DECFLOAT EXTERNAL field, you must allow space for punctuation. Punctuation includes the minus sign, decimal point, exponent marker, exponent sign, and exponent value.
2 When unloading XML data to a CHAR BLOBF or VARCHAR BLOBF field, you must specify BINARYXML.
The following table lists the default output lengths for the conversions:
Output field | CHAR | VARCHAR | GRAPHIC | GRAPHIC EXTERNAL | VARGRAPHIC | BINARY | VARBINARY |
|---|---|---|---|---|---|---|---|
Input column | |||||||
SMALLINT | X | N |
|
|
|
|
|
INTEGER | X | N |
|
|
|
|
|
BIGINT | X | N |
|
|
|
|
|
DECIMAL | X | N |
|
|
|
|
|
FLOAT | X | N |
|
|
|
|
|
DECFLOAT |
|
|
|
|
|
|
|
CHAR | C 1 | N |
|
|
|
|
|
VARCHAR | C | N 1 |
|
|
|
|
|
GRAPHIC |
|
| C | C | N |
|
|
VARGRAPHIC |
|
| C | C | N |
|
|
BINARY |
|
|
|
|
| C | N |
VARBINARY |
|
|
|
|
| C | N |
DATE | 10 | N |
|
|
|
|
|
TIME | 8 | N |
|
|
|
|
|
TIMESTAMP | 26 2 | N |
|
|
|
|
|
TIMESTAMP WITH TIME ZONE | 26b | N |
|
|
|
|
|
BLOB |
|
|
|
|
|
|
|
CLOB |
|
|
|
|
|
|
|
DBCLOB |
|
|
|
|
|
|
|
XML |
|
|
|
|
|
|
|
Numeric values in a column indicate the required output length. X = Explicit length is required. C = The default length is defined by the column. For DECIMAL and DECFLOAT, the precision is defined by the column. N= The length is defined by the input value. L = If your DSNHDECP format is LOCAL, the length is the local DATE and TIME length. If your DSNHDECP format is ISO, USA, EUR, or JIS, the default length for DATE is 10 and the default length for TIME is 8. T = See Supported-DATE-TIME-and-TIMESTAMP-formats for length information. | |||||||
1 When unloading from CHAR to CHAR or VARCHAR to VARCHAR, and translating from EBCDIC or ASCII to Unicode using DIRECT YES, the default length is three times the length of the column.
2 For timestamp columns that are defined with precision, the default length of the output field is defined by the input value.
Date, time, and timestamp output
The following table lists the conversions that UNLOAD PLUS supports to date, time, and timestamp output.
The following considerations apply to these data type conversions:
- The default data format depends on the FORMAT option that is in effect. For example, when FORMAT STANDARD (the default) is in effect, the default data format for date/time columns is external format.
- For output date/time EXTERNAL data types with a format number, the format number describes the representation of the column date/time value, not the format of the output field.
- When using DIRECT NO, specifying formats for EXTERNAL data types (for example, DATE- format EXTERNAL) might require extra consideration due to the various ways in which the data is returned from Db2. Often, you should not specify the same format when using DIRECT NO versus DIRECT YES.
Output field | DATE | DATE EXTERNAL | DATE- format EXTERNAL | TIME | TIME EXTERNAL | TIME- format EXTERNAL | TIMESTAMP or TIMESTAMP WITH TIME ZONE | TIMESTAMP EXTERNAL | TIMESTAMP -format EXTERNAL | TIMESTAMP WITH TIME ZONE EXTERNAL |
|---|---|---|---|---|---|---|---|---|---|---|
Input column | ||||||||||
SMALLINT |
|
|
|
|
|
|
|
|
|
|
INTEGER |
|
| X |
|
| X |
|
|
|
|
BIGINT |
|
|
|
|
|
|
|
|
|
|
DECIMAL |
|
| X |
|
| X |
|
|
|
|
FLOAT |
|
|
|
|
|
|
|
|
|
|
DECFLOAT |
|
|
|
|
|
|
|
|
|
|
CHAR | X |
| X | X |
| X | X |
| X |
|
VARCHAR | X |
|
| X |
|
| X |
|
|
|
GRAPHIC |
|
|
|
|
|
|
|
|
|
|
VARGRAPHIC |
|
|
|
|
|
|
|
|
|
|
BINARY |
|
|
|
|
|
|
|
|
|
|
VARBINARY |
|
|
|
|
|
|
|
|
|
|
DATE | X | D |
|
|
|
|
|
|
|
|
TIME |
|
|
| X | D |
|
|
|
|
|
TIMESTAMP | X | N |
| X | N |
| X | D |
| X |
TIMESTAMP WITH TIME ZONE | X | N |
| X | N |
| X | X |
| D |
BLOB |
|
|
|
|
|
|
|
|
|
|
CLOB |
|
|
|
|
|
|
|
|
|
|
DBCLOB |
|
|
|
|
|
|
|
|
|
|
XML |
|
|
|
|
|
|
|
|
|
|
The column values are: X = Allowable conversion D = Allowable conversion and indicates the default output data type, which is the data type of the column N = Allowable conversion but not supported with DIRECT NO | ||||||||||
The following table lists the default output lengths for these conversions:
Output field | DATE | DATE EXTERNAL | DATE -format EXTERNAL | TIME | TIME EXTERNAL | TIME -format EXTERNAL | TIMESTAMP or TIMESTAMP WITH TIME ZONE | TIMESTAMP EXTERNAL | TIMESTAMP -format EXTERNAL | TIMESTAMP WITH TIME ZONE EXTERNAL |
|---|---|---|---|---|---|---|---|---|---|---|
Input column | ||||||||||
SMALLINT |
|
|
|
|
|
|
|
|
|
|
INTEGER |
|
| T |
|
| T |
|
|
|
|
BIGINT |
|
|
|
|
|
|
|
|
|
|
DECIMAL |
|
| T |
|
| T |
|
|
|
|
FLOAT |
|
|
|
|
|
|
|
|
|
|
DECFLOAT |
|
|
|
|
|
|
|
|
|
|
CHAR | 4 |
| T | 3 |
| T | 10 |
| T |
|
VARCHAR | 4 |
|
| 3 |
|
| 10 |
|
|
|
GRAPHIC |
|
|
|
|
|
|
|
|
|
|
VARGRAPHIC |
|
|
|
|
|
|
|
|
|
|
BINARY |
|
|
|
|
|
|
|
|
|
|
VARBINARY |
|
|
|
|
|
|
|
|
|
|
DATE | 4 | L 1 |
|
|
|
|
|
|
|
|
TIME |
|
|
| 3 | L 1 |
|
|
|
|
|
TIMESTAMP | 4 | L 1 |
| 3 | L 1 |
| 10 2 |
| ||
TIMESTAMP WITH TIME ZONE | 4 |
|
| 3 |
|
| 10 2 |
| ||
BLOB |
|
|
|
|
|
|
|
|
|
|
CLOB |
|
|
|
|
|
|
|
|
|
|
DBCLOB |
|
|
|
|
|
|
|
|
|
|
XML |
|
|
|
|
|
|
|
|
|
|
Numeric values in a column indicate the required output length. X = Explicit length is required. C = The default length is defined by the column. For DECIMAL and DECFLOAT, the precision is defined by the column. N= The length is defined by the input value. L = If your DSNHDECP format is LOCAL, the length is the local DATE and TIME length. If your DSNHDECP format is ISO, USA, EUR, or JIS, the default length for DATE is 10 and the default length for TIME is 8. T = See Supported-DATE-TIME-and-TIMESTAMP-formats for length information. | ||||||||||
1 For EXTERNAL date/time fields, if you also specified DATEFMT, TIMEFMT, or TSFMT, the length defaults to the format element string length.
2 For timestamp columns that are defined with precision, the default length of the output field is defined by the input value.
LOB and XML output
The following table lists the conversions that UNLOAD PLUS supports to LOB and XML output:
Output field | BLOB | CLOB | DBCLOB | XML |
|---|---|---|---|---|
Input column | ||||
SMALLINT |
|
|
|
|
INTEGER |
|
|
|
|
BIGINT |
|
|
|
|
DECIMAL |
|
|
|
|
FLOAT |
|
|
|
|
DECFLOAT |
|
|
|
|
CHAR |
|
|
|
|
VARCHAR |
|
|
|
|
GRAPHIC |
|
|
|
|
VARGRAPHIC |
|
|
|
|
BINARY |
|
|
|
|
VARBINARY |
|
|
|
|
DATE |
|
|
|
|
TIME |
|
|
|
|
TIMESTAMP |
|
|
|
|
TIMESTAMP WITH TIME ZONE |
|
|
|
|
BLOB | D |
|
|
|
CLOB |
| D |
|
|
DBCLOB |
|
| D |
|
XML |
|
|
| D |
The column values are: X = Allowable conversion D = Allowable conversion and indicates the default output data type, which is the data type of the column N = Allowable conversion but not supported with DIRECT NO | ||||
The following table lists the default output lengths for the conversions:
Output field | BLOB | CLOB | DBCLOB | XML |
|---|---|---|---|---|
Input column | ||||
SMALLINT |
|
|
|
|
INTEGER |
|
|
|
|
BIGINT |
|
|
|
|
DECIMAL |
|
|
|
|
FLOAT |
|
|
|
|
DECFLOAT |
|
|
|
|
CHAR |
|
|
|
|
VARCHAR |
|
|
|
|
GRAPHIC |
|
|
|
|
VARGRAPHIC |
|
|
|
|
BINARY |
|
|
|
|
VARBINARY |
|
|
|
|
DATE |
|
|
|
|
TIME |
|
|
|
|
TIMESTAMP |
|
|
|
|
TIMESTAMP WITH TIME ZONE |
|
|
|
|
BLOB | N |
|
|
|
CLOB |
| N |
|
|
DBCLOB |
|
| N |
|
XML |
|
|
| N |
Numeric values in a column indicate the required output length. X = Explicit length is required. C = The default length is defined by the column. For DECIMAL and DECFLOAT, the precision is defined by the column. N= The length is defined by the input value. L = If your DSNHDECP format is LOCAL, the length is the local DATE and TIME length. If your DSNHDECP format is ISO, USA, EUR, or JIS, the default length for DATE is 10 and the default length for TIME is 8. T = See Supported-DATE-TIME-and-TIMESTAMP-formats for length information. | ||||
Related topic