Data translation
Before writing the input record to the table, LOADPLUS translates the data from the specified encoding scheme of the input data.
LOADPLUS determines the encoding scheme based on the values of the EBCDIC, ASCII, or UNICODE command option and the CCSID command option.
Translation processing
LOADPLUS handles translation processing in the follow manner:
- Based on the CCSID that you specify or that LOADPLUS retrieves from DSNHDECP, LOADPLUS searches the SYSIBM.SYSSTRINGS catalog table for a row that identifies how to translate the data. (To review the values that are assigned to the CCSID, see the LOADPLUS parameter listing under message number BMC50471I.)
If a row exists, LOADPLUS searches the row for a value, first in the TRANSPROC field and then in the TRANSTAB field. LOADPLUS invokes DSNUTILB when either of the following conditions exists:
- The TRANSPROC field contains DSNXVJPC or DSNXVTWC.
- The TRANSTAB field is empty, and the TRANSPROC field contains a value.
In all other cases, LOADPLUS performs the data translation providing the translation is supported. For a list of supported translations, see the supported translations table.
- If a row does not exist in SYSIBM.SYSSTRINGS, LOADPLUS passes the data to the z/OS Unicode Conversion Services for translation.
Supported character conversions
The following table identifies the character conversions that LOADPLUS supports for translation processing. For more restrictions on translation processing, see Restrictions.
This table indicates the supported translations with the following identifiers:
- X = allowable translation
- D = allowable translation by invoking DSNUTILB
Supported translations
Target | ASCII SBCS | ASCII MIXED | ASCII DBCS | EBCDIC SBCS | EBCDIC MIXED | EBCDIC DBCS | UNICODE SBCS | UNICODE MIXED | UNICODE DBCS |
|---|---|---|---|---|---|---|---|---|---|
Source | |||||||||
ASCII SBCS | X | X, D 1 |
| X | D |
| X | X |
|
ASCII MIXED | D | X, D 2 |
| D | D |
| D | D |
|
ASCII DBCS |
|
| X |
|
| D |
|
| D |
EBCDIC SBCS | X | D |
| X | X, D 1 |
| X | X |
|
EBCDIC MIXED | D | D |
| D | X, D 2 |
| D | D |
|
EBCDIC DBCS |
|
| D |
|
| X |
|
| D |
UNICODE SBCS | X | X |
| X | X |
| X | X |
|
UNICODE MIXED | X 3 | D |
| X 3 | D |
| X 3 | X |
|
UNICODE DBCS |
|
| D |
|
| D |
|
| X |
1 If the CCSIDs of the input do not match the CCSIDs of the Db2 subsystem, LOADPLUS invokes DSNUTILB.
2 If you are loading data from a referenced file, LOADPLUS invokes DSNUTILB.
3 If the input value is greater than the column length, LOADPLUS trims any trailing blanks to fit.
Expansion and contraction
The translation process might cause the length of the resultant data to change based on the CCSID type that you specified. To determine the resultant length of the data based on each CCSID conversion, see the IBM Db2 SQL Reference.
If an expansion error occurs during translation, LOADPLUS writes the record to the discard data set (SYSDISC).
Translations that invoke DSNUTILB
If you request a translation that requires LOADPLUS to invoke DSNUTILB, ensure that you supply the correct options and environment for invoking DSNUTILB. Otherwise, LOADPLUS issues BMC50178E and terminates.
- For the requirements for a basic DSNUTILB load job, see Load-jobs-that-invoke-DSNUTILB.
- In addition, the UNICODE installation option must be set to YES. This value must be in effect for any translation job that invokes DSNUTILB, regardless of whether you are loading Unicode data.
Restrictions
The following table describes the restrictions on data translation for LOADPLUS:
Conditions | Result |
|---|---|
Your job uses both of the following specifications:
| LOADPLUS ignores the data translation options. |
Columns have one of the following definitions:
| LOADPLUS does not translate these columns. |
Constants
For comparisons between predicate block character constants and input data, LOADPLUS translates constants from EBCDIC (using the EBCDIC SBCS system default CCSID) to the encoding scheme of the input data.
For constants that are placed in the table data, LOADPLUS translates the constants from EBCDIC (using the EBCDIC SBCS system default CCSID) to the table encoding scheme. LOADPLUS translates the following character constants for loading:
- VALUE constants
- DEFAULTIF VALUE constants
SUBBYTE and ERRORBYTE fields
LOADPLUS uses SUBBYTE and ERRORBYTE fields in the same way that the IBM Db2 LOAD utility uses them.
Order of data type conversion and data translation
The following tables detail when data translation occurs with respect to data type conversion. The character combinations in the tables indicate the order in which data type conversion, data translation, and character padding occur during data translation:
- x indicates supported conversion, no translation required.
- c indicates conversion.
- t indicates translation.
- p indicates padding with blanks.
The numbers represent the type of data translation that occurs during data type conversion:
- 1 indicates translation of the input CCSID to EBCDIC SBCS (system default).
- 2 indicates translation of EBCDIC SBCS (system default) to output CCSID.
- 3 indicates translation of input CCSID to output CCSID.
Conversion and translation processing for numeric output columns
Output column | SMALLINT | INTEGER | BIGINT | DECIMAL | FLOAT | DECFLOAT |
|---|---|---|---|---|---|---|
Input field | ||||||
SMALLINT | x | x | x | x | x | x |
INTEGER | x | x | x | x | x | x |
INTEGER EXTERNAL | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 |
BIGINT | x | x | x | x | x | x |
DECIMAL | x | x | x | x | x | x |
DECIMAL EXTERNAL | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 |
DECIMAL ZONED |
|
|
|
|
|
|
FLOAT | x | x | x | x | x | x |
FLOAT EXTERNAL | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 |
BINARY |
|
|
|
|
|
|
VARBINARY |
|
|
|
|
|
|
CHAR | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 |
VARCHAR | tc 1 | tc 1 | tc 1 | tc 1 | tc 1 |
|
GRAPHIC |
|
|
|
|
|
|
GRAPHIC EXTERNAL |
|
|
|
|
|
|
VARGRAPHIC |
|
|
|
|
|
|
DATE |
|
|
|
|
|
|
DATE EXTERNAL |
|
|
|
|
|
|
DATE-format EXTERNAL |
|
|
|
|
|
|
TIME |
|
|
|
|
|
|
TIME EXTERNAL |
|
|
|
|
|
|
TIME-format EXTERNAL |
|
|
|
|
|
|
TIMESTAMP |
|
|
|
|
|
|
TIMESTAMP EXTERNAL |
|
|
|
|
|
|
TIMESTAMP-format EXTERNAL |
|
|
|
|
|
|
TIMESTAMP WITH TIME ZONE |
|
|
|
|
|
|
TIMESTAMP WITH TIME ZONE EXTERNAL |
|
|
|
|
|
|
BLOB |
|
|
|
|
|
|
CLOB |
|
|
|
|
|
|
DBCLOB |
|
|
|
|
|
|
XML |
|
|
|
|
|
|
EXIT | x | x | x | x | x |
|
Conversion and translation processing for binary, character, and graphic output columns
Output column | BINARY | VARBINARY | CHARACTER | VARCHAR | GRAPHIC | VARGRAPHIC |
|---|---|---|---|---|---|---|
Input field | ||||||
SMALLINT |
|
| cpt 2 | cpt 2 |
|
|
INTEGER |
|
| cpt 2 | cpt 2 |
|
|
INTEGER EXTERNAL |
|
| tcpt 1,2 | tcpt 1,2 |
|
|
BIGINT |
|
| cpt 2 | cpt 2 |
|
|
DECIMAL |
|
| cpt 2 | cpt 2 |
|
|
DECIMAL EXTERNAL |
|
| tcpt 1,2 | tcpt 1,2 |
|
|
DECIMAL ZONED |
|
| tcpt 1,2 | tcpt 1,2 |
|
|
FLOAT |
|
| cpt 2 | cpt 2 |
|
|
FLOAT EXTERNAL |
|
| tcpt 1,2 | tcpt 1,2 |
|
|
BINARY | x | x |
|
|
|
|
VARBINARY | x | x |
|
|
|
|
CHAR |
|
| cpt 3 | cpt 3 |
|
|
VARCHAR |
|
| cpt 3 | cpt 3 |
|
|
GRAPHIC |
|
|
|
| x | x |
GRAPHIC EXTERNAL |
|
|
|
| x | x |
VARGRAPHIC |
|
|
|
| x | x |
DATE |
|
| cpt 2 | cpt 2 |
|
|
DATE EXTERNAL |
|
|
|
|
|
|
DATE-format EXTERNAL |
|
| tcpt 1,2 | tcpt 1,2 |
|
|
TIME |
|
| ctp 2 | ctp 2 |
|
|
TIME EXTERNAL |
|
|
|
|
|
|
TIME-format EXTERNAL |
|
| tcpt 1,2 | tcpt 1,2 |
|
|
TIMESTAMP |
|
| ctp 2 | ctp 2 |
|
|
TIMESTAMP EXTERNAL |
|
|
|
|
|
|
TIMESTAMP-format EXTERNAL |
|
| tcpt 1,2 | tcpt 1,2 |
|
|
TIMESTAMP WITH TIME ZONE |
|
| ctp 2 | ctp 2 |
|
|
TIMESTAMP WITH TIME ZONE EXTERNAL |
|
|
|
|
|
|
BLOB |
|
|
|
|
|
|
CLOB |
|
|
|
|
|
|
DBCLOB |
|
|
|
|
|
|
XML |
|
|
|
|
|
|
EXIT | x | x | x | x | x | x |
Conversion and translation processing for date, time, and timestamp output columns
Output column | DATE | TIME | TIMESTAMP | TIMESTAMP WITH TIME ZONE |
|---|---|---|---|---|
Input field | ||||
SMALLINT |
|
|
|
|
INTEGER | x | x |
|
|
INTEGER EXTERNAL | tc 1 | tc 1 |
|
|
BIGINT |
|
|
|
|
DECIMAL | x | x |
|
|
DECIMAL EXTERNAL | tc 1 | tc 1 |
|
|
DECIMAL ZONED | tc 1 | tc 1 |
|
|
FLOAT |
|
|
|
|
FLOAT EXTERNAL |
|
|
|
|
BINARY |
|
|
|
|
VARBINARY |
|
|
|
|
CHAR | tc 1 | tc 1 | tc 1 | tc 1 |
VARCHAR | tc 1 | tc 1 | tc 1 | tc 1 |
GRAPHIC |
|
|
|
|
GRAPHIC EXTERNAL |
|
|
|
|
VARGRAPHIC |
|
|
|
|
DATE | x |
|
|
|
DATE EXTERNAL | tc 1 |
|
|
|
DATE-format EXTERNAL | tc 1 |
|
|
|
TIME |
| x |
|
|
TIME EXTERNAL |
| tc 1 |
|
|
TIME-format EXTERNAL |
| tc 1 |
|
|
TIMESTAMP |
|
| x |
|
TIMESTAMP EXTERNAL | tc 1 | tc 1 | tc 1 |
|
TIMESTAMP-format EXTERNAL | tc 1 | tc 1 | tc 1 |
|
TIMESTAMP WITH TIME ZONE |
|
|
| x |
TIMESTAMP WITH TIME ZONE EXTERNAL | tc 1 | tc 1 | tc 1 | tc 1 |
BLOB |
|
|
|
|
CLOB |
|
|
|
|
DBCLOB |
|
|
|
|
XML |
|
|
|
|
EXIT | x | x | x |
|
Conversion and translation processing for LOB and XML output columns
Output column | BLOB | CLOB | DBCLOB | XML |
|---|---|---|---|---|
Input field | ||||
SMALLINT |
|
|
|
|
INTEGER |
|
|
|
|
INTEGER EXTERNAL |
|
|
|
|
BIGINT |
|
|
|
|
DECIMAL |
|
|
|
|
DECIMAL EXTERNAL |
|
|
|
|
DECIMAL ZONED |
|
|
|
|
FLOAT |
|
|
|
|
FLOAT EXTERNAL |
|
|
|
|
BINARY |
|
|
|
|
VARBINARY |
|
|
|
|
CHAR |
|
|
|
|
VARCHAR |
|
|
|
|
GRAPHIC |
|
|
|
|
GRAPHIC EXTERNAL |
|
|
|
|
VARGRAPHIC |
|
|
|
|
DATE |
|
|
|
|
DATE EXTERNAL |
|
|
|
|
DATE-format EXTERNAL |
|
|
|
|
TIME |
|
|
|
|
TIME EXTERNAL |
|
|
|
|
TIME-format EXTERNAL |
|
|
|
|
TIMESTAMP |
|
|
|
|
TIMESTAMP EXTERNAL |
|
|
|
|
TIMESTAMP-format EXTERNAL |
|
|
|
|
TIMESTAMP WITH TIME ZONE |
|
|
|
|
TIMESTAMP WITH TIME ZONE EXTERNAL |
|
|
|
|
BLOB | x |
|
|
|
CLOB |
| x |
|
|
DBCLOB |
|
| x |
|
XML |
|
|
| x |
EXIT |
|
|
|
|
Related topic