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.

Related topic

LOADPLUS-command-options

Translation processing

LOADPLUS handles translation processing in the following manner:

  1. 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.)

  2. 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.

  3. 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:

  • FORMAT BMCUNLOAD
  • One or more data translation options, such as CCSID

LOADPLUS ignores the data translation options.

Columns have one of the following definitions:

  • The FOR BIT DATA attribute
  • The BINARY or VARBINARY data type

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.

Important

With the Db2 LOAD utility, if you specify NOSUBS and discard processing is not active, the job fails when it encounters records that require substitution. LOADPLUS does not fail under these circumstances. However, LOADPLUS processes only the records that do not require substitution.

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 the 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 the 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






 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*