Reference Manual

10.4.0
- 10.5.6
- 10.5.5
- 10.5.4
- 10.5.3
- 10.5.2
- 10.5.1
- 10.5
- 10.4.1
- 10.4.0 HotFix 1

Back Next

DB2CODEPAGE Statement Examples

Review these examples to learn how to define and use the parameters in the DB2CODEPAGE statement of the DBMOVER configuration file. The DB2CODEPAGE statement is used for DB2 for z/OS bulk data movement sources and targets.

Example 1. Look Up the Code Pages Set for the DB2 DSNHDECP Module

Look up the EBCDIC code page values for Japanese Extended Katakana in the DB2 installation job for the DSNHDECP module. Then configure the DB2CODEPAGE statement to match these code page values.

The installation job contains the following EBCDIC CCSID values for the DB2 subsystem:

000273 //SYSIN    DD  *
000274    DSNHDECM    CHARSET=ALPHANUM,
000275                ASCCSID=1041,                                           X
000276                AMCCSID=942,                                            X
000277                AGCCSID=301,                                            X
000278                SCCSID=290,                                             X
000279                MCCSID=930,                                             X
000280                GCCSID=300,                                             X
000281                USCCSID=367,                                            X
000282                UMCCSID=1208,                                           X
000283                UGCCSID=1200,                                           X
000302      END
000303 //*

Use these CCSID values in the following PowerExchange DB2CODEPAGE parameters:

DB2CODEPAGE=(D91G
             ,EBCDIC_CCSID=(290,300,930)
             ,ASCII_CCSID=(1041,301,942)
             ,UNICODE_CCSID=(367,1200,1208)
)

Example 2: Use the Default DB2CODEPAGE Configuration

By default, DB2 translates single-byte CCSIDs for CHAR and VARCHAR character strings into the equivalent EBCDIC code pages in which the DB2 plan for PowerExchange was bound. If your DB2 subsystem contains no graphic or mixed data, you can use the default configuration.

In this case, PowerExchange uses the following DB2CODEPAGE parameters:

DB2CODEPAGE=(D91G
             ,DB2TRANS=P
             ,MIXED=N
             ,EBCDIC_CCSID=(037,037,037)
             ,PLAN_CCSID=(037,65534,65534)
             ,ASCII_CCSID=()
             ,UNICODE_CCSID=(367,1200,1208)
)

Note that this statement is equivalent to the following minimal configuration:

DB2CODEPAGE=(D91G
             ,DB2TRANS=P
             ,MIXED=N
             ,EBCDIC_CCSID=(037,037,037)
             ,UNICODE_CCSID=(367,1200,1208)
)

If you omit the DB2CODEPAGE statement from the DBMOVER file, PowerExchange automatically generates these code page parameter values for the DB2 subsystem.

The EBCDIC_CCSID parameter specifies a CCSID for single-byte data only. No code page is specified for graphic and mixed data, as indicated by the value 65534. Because the DB2 catalog tables store data with Unicode encoding, this default configuration also includes the UNICODE_CCSID parameter.

The EBCDIC_CCSID values must be consistent with the code pages defined for the DB2 plan for PowerExchange and with the default code pages defined in the DB2 installation job for the DB2 DSNHDECM load module.

Example 3. Redirect the Default EBCDIC Code Pages to the DB2 Plan Code Pages

The EBCDIC_CCSID code pages must match the code pages that were defined for the DB2 DSNHDECP load module when the DB2 subsystem was created. If these code pages are different from the code pages in which the DB2 plan for PowerExchange was bound, you can use the PLAN_CCSID parameter to direct DB2 to use the plan code pages.

Use the PLAN_CCSID parameter in the following situations:

The EBCDIC_CCSID code pages do not have an ICU conversion table that PowerExchange can use for ICU-based code page conversion.

The EBCDIC_CCSID code pages match the default code pages that were defined for the DB2 subsystem but differ from the EBCDIC code pages of the source or target table.

For example, specify the following DB2CODEPAGE parameters:

DB2CODEPAGE=(D91G
             ,DB2TRANS=P
             ,MIXED=N
             ,EBCDIC_CCSID=(290,300,930)
             ,PLAN_CCSID=(037,309,309)
)

The PLAN_CCSID values override the EBCDIC_CCSID values.

Example 4: Map ASCII and UNICODE Code Pages to EBCDIC Code Pages

The DB2 subystem "D91G" contains data with ASCII or Unicode encoding. You want DB2 to translate the ASCII and Unicode code pages of the data into the equivalent EBCDIC code pages the were defined in the DB2 plan that was bound for PowerExchange.

In this case, specify the following parameters in the DB2CODEPAGE statement:

DB2CODEPAGE=(D91G
             ,DB2TRANS=P
             ,MIXED=N
             ,EBCDIC_CCSID=(290,300,930)
             ,ASCII_CCSID=(1041,301,942)
             ,UNICODE_CCSID=(367,1200,1208)
)

Because DB2TRANS=P, DB2 translates the code pages. DB2 redirects the ASCII_CCSID and UNICODE_CCSID code pages to the EBCDIC_CCSID code pages.

Example 5. Use the Native Code Pages of the Data for Columns with Mixed Data

The DB2 subsystem "D91G" contains CHAR FOR MIXED DATA columns that use ASCII or Unicode encoding. These columns can contain a mix of single-byte and double-byte characters.

When DB2 translates mixed data to an EBCIDIC equivalent CCSID, DB2 uses the shift-in and shift-out characters (X'OE" and X'OF') to change between the single-byte and double-byte characters. As a result, the data might become too long to fit in some columns. In this situation, you can prevent DB2 from translating the data to the EBCIDIC equivalent code page and use the native ASCII or Unicode code page in which the data is stored instead.

Specify the following parameters in the DB2CODEPAGE statement:

DB2CODEPAGE=(D91G
             ,DB2TRANS=N
             ,MIXED=Y
)

The DB2TRANS=N parameter prevents DB2 from performing code page translation. The MIXED=Y parameter indicates that the column character strings can contain mixed data.

No *_CCSID parameters are required because DB2 performs no code page translation.

Example 6. Direct DB2 to Remap Code Pages That Do Not Have an ICU Conversion Table

If an ICU conversion table is not available for an ASCII or Unicode CCSID and you cannot remap the CCSID to an EBCDIC CCSID in which the DB2 plan for PowerExchange is bound, DB2 can remap the CCSID to the native CCSID of the column data. You must include at least one REMAPn parameter to identify the CCSID to remap and the data CCSID to use. You can define up to six REMAPn parameters, one per table.

In this example, DB2 remaps the Japanese CCSID 301 to the corresponding Unicode double-byte CCSID 1200, or UTF-16. An ICU conversion table is not available for CCSID 301 but is available for CCSID 1200.

Specify the following parameters in the DB2CODEPAGE statement:

DB2CODEPAGE=(D91G
             ,DB2TRANS=R
             ,REMAP1=(301,1200)
)

Example 7. Direct PowerExchange to Remap Incorrect Code Pages

In certain circumstances, you might need PowerExchange to remap an incorrect code page without any DB2 translation. For example, this type of remapping might be necessary in the following circumstances:

The DB2 data is not be in the code page that DB2 reports based on the DB2 catalog, possibly because the data was loaded incorrectly.

The current code page does not correctly translate certain characters in the data, such as square brackets or the Euro currency symbol.

In this case, use the DB2TRANS=N parameter and REMAPn parameter to specify the correct code page. PowerExchange substitutes the correct code page for the incorrect one.

Specify the following parameters in the DBMOVER file:

DB2CODEPAGE=(D91G
             ,DB2TRANS=N
             ,REMAP1=(incorrect_cp,correct_cp)
)

Rename Saved Search

Table of Contents

Reference Manual

Reference Manual

DB2CODEPAGE Statement Examples

DB2CODEPAGE Statement Examples

Example 1. Look Up the Code Pages Set for the DB2 DSNHDECP Module

Example 2: Use the Default DB2CODEPAGE Configuration

Example 3. Redirect the Default EBCDIC Code Pages to the DB2 Plan Code Pages

Example 4: Map ASCII and UNICODE Code Pages to EBCDIC Code Pages

Example 5. Use the Native Code Pages of the Data for Columns with Mixed Data

Example 6. Direct DB2 to Remap Code Pages That Do Not Have an ICU Conversion Table

Example 7. Direct PowerExchange to Remap Incorrect Code Pages