D National Language Support

Overview of NLS Interactions

Figure D-1 illustrates NLS interactions within your system, including each component of your system and the parameters of each component that affect NLS processing in a distributed environment. Table D-1 describes the architecture illustrated in Figure D-1.

Figure D-1 Architecture of NLS Interactions with Your System Components

Description of "Figure D-1 Architecture of NLS Interactions with Your System Components"

Table D-1 describes in detail the parameters and variables needed for NLS processing within each of your system environments: the client environment, the Oracle integrating server, the gateway, and the DRDA Server.

Client and Oracle Integrating Server Configuration

A number of NLS parameters control NLS processing between the Oracle Database server and client. You can set language-dependent action defaults for the server, and you can set language-dependent action for the client that overrides these defaults. For a complete description of NLS parameters, refer to the NLS chapter in the Oracle Database Administrator's Guide. These parameters do not directly affect gateway processing. However, you must ensure that the client character set (which is specified by the Oracle Database server NLS_LANG environment variable) is compatible with the character sets that you specify on the gateway and on the DRDA Server.

When you create the Oracle Database, the character set that is used to store data is specified by the CHARACTER SET clause of the CREATE DATABASE statement. After the database is created, the database character set cannot be changed unless you reEcreate the database.

Normally, the default for CHARACTER SET is US7ASCII, which supports only the 26 Latin alphabetic characters. If you have specified 8Ebit character sets on the gateway and DRDA Server, then you must have a compatible 8Ebit character set defined on your database. To check the character set of an existing database, run the command:

SELECT USERENV('LANGUAGE') FROM DUAL;

For more information, refer to "Specifying Character Sets" in the Oracle Database Administrator's Guide.

Note that this does not mean that the gateway character set must be the same as the Oracle server character set. The Oracle Net facility will be performing implicit conversion between the Oracle server character set and the gateway character set.

Gateway Language Interaction with DRDA Server

During logon of the gateway to the DRDA Server, initial language information is exchanged between the Gateway and the server. First, the gateway sends to the DRDA Server the CCSID it will be conversing in. In the following example, the Oracle character set "WE8ISO8859P1" is mapped to CCSID 819 (an ASCII Code Page). This CCSID is sent to the DRDA Server. The DRDA Server responds with the CCSID that it will be conversing in. This will be the CCSID with which the DB2 database was generated. Also, in the following example, this is CCSID 500, an EBCDIC code page. Figure D-2, "Gateway Language Interaction with DRDA Server", illustrates this process.

A DB2 instance will map unknown CCSIDs using the SYSIBM.SYSSTRINGS table (this table has different names for the various DB2 versions). It is possible to add additional character set mappings to this table using DB2 utilities. Please refer to the DB2 Installation documentation for details.

The setting of the HS_LANGUAGE parameter in the gateway initsid.ora determines which CCSID is used by the gateway for the conversation. Similarly, the setting of the HS_NLS_NCHAR parameter determines which CCSID will be used by the gateway for GRAPHIC data interchange. For the list of supported ASCIIEbased Oracle character sets that are mapped to CCSIDs, refer to "Gateway Codepage Map Facility".

Note again that the gateway character set need not be the same as the Oracle Database server character set. In many cases, it is not feasible to set the gateway character set equal to the Oracle Database server character set because the DRDA Server will not have a valid translation for it. Instead, choose a character set that will have the most complete intersection with the character set that is used by the DRDA Server. The Oracle Net facility will do any translation between the gateway character set and the Oracle server character set.

Figure D-2 Gateway Language Interaction with DRDA Server

Description of "Figure D-2 Gateway Language Interaction with DRDA Server"

HS_LANGUAGE=language[_territory.character_set]

HS_NLS_DATE_FORMAT=date_format

Gateway Codepage Map Facility

The gateway now has a user-specifiable facility to map IBM Coded Character Set Identifiers (CCSIDs) to Oracle Character Sets for the purpose of data translation.

The map name defaults to codepage.map and is located in the directory ORACLE_HOME\tg4drda\admin. Refer to Appendix C, "DRDAESpecific Parameters" for more detailed information about the DRDA_CODEPAGE_MAP parameter.

The map has two different forms of syntax. The first form of syntax defines a mapping between a CCSID and an Oracle Database character set:

[S|D|M] CCSID direction Oracle_CharacterSet {shift}

where:

S designates a singleEbyte character set

D designates a doubleEbyte character set

M designates a multibyte character set

CCSID  is the IBM coded character set identifier

direction is one of the following:

• =  means mapping is bidirectional

• <  means mapping is one-way, Oracle character set to CCSID

• >  means mapping is one-way, CCSID to Oracle character set

Oracle_CharacterSet  is the name of a valid Oracle character set.

shift indicates a character set that requires Shift OUT/IN processing. Set this attribute only for EBCDICEbased doubleEbyte and multibyte mappings.

The second form of syntax defines a mapping of a multibyte CCSID to its singleEbyte and doubleEbyte CCSID equivalents:

MBC multi = single double

where:

multi is the multibyte CCSID

single is the single-byte CCSID

double is the double-byte CCSID

This facility is intended as a way of mapping CCSIDs which were not previously mapped as shipped with the gateway. You must contact Oracle Support Services before modifying this map.

The following are the contents of the map as shipped with the Oracle Transparent Gateway for DRDA;

# Copyright (c) 2001, 2003, Oracle Corporation.  All rights reserved.
# Transparent Gateway for IBM DRDA - CodePage/Oracle CharacterSet Map
# S==Single-byte, D==Double-byte, M==Multi-byte, MBC==SBC DBC mapping
#
# Single-byte codepage mappings
#
S    37 = WE8EBCDIC37   # United States/Canada                          EBCDIC
S   273 = D8EBCDIC273   # Austria/Germany                               EBCDIC
S   277 = DK8EBCDIC277  # Denmark/Norway                                EBCDIC
S   278 = S8EBCDIC278   # Finland/Sweden                                EBCDIC
S   280 = I8EBCDIC280   # Italy                                         EBCDIC
S   284 = WE8EBCDIC284  # Latin America/Spain                           EBCDIC
S   285 = WE8EBCDIC285  # United Kingdom                                EBCDIC
S   297 = F8EBCDIC297   # France                                        EBCDIC
#S   420 = AR8EBCDICX    # Arabic Bilingual (USA English)                EBCDIC
S   420 = AR8XBASIC     # Arabic Bilingual (USA English)                EBCDIC
S   424 = IW8EBCDIC424  # Israel (Hebrew)                               EBCDIC
S   437 = US8PC437      # Personal Computer,USA                         ASCII
S   500 = WE8EBCDIC500  # International                                 EBCDIC
S   813 = EL8ISO8859P7  # Greek                                         ASCII
S   819 = WE8ISO8859P1  # ISO/ANSI Multilingual                         ASCII
S   838 = TH8TISEBCDIC  # Thai w/Low-Tone Marks & Ancient Chars         EBCDIC
S   850 < US7ASCII      # Multilingual Page - Personal Computer         ASCII
S   850 = WE8PC850      # Multilingual Page - Personal Computer         ASCII
S   864 = AR8ISO8859P6  # Arabic - Personal Computer                    ASCII
S   870 = EE8EBCDIC870  # Latin 2, Multilingual/ROECE                   EBCDIC
S   871 = WE8EBCDIC871  # Iceland - CECP                                EBCDIC
S   875 = EL8EBCDIC875  # Greece                                        EBCDIC
S   904 > US7ASCII      # Traditional Chinese - PC-Data                 ASCII
S   912 = EE8ISO8859P2  # Latin 2 8-bit                                 ASCII
S   916 = IW8ISO8859P8  # Israel (Hebrew)                               ASCII
S  1025 = CL8EBCDIC1025 # Cyrillic, Multiling                           EBCDIC
S  1086 = IW8EBCDIC1086 # Israel                                        EBCDIC
S  1252 = WE8MSWIN1252  # Latin 1 - MS-Windows                          ASCII
S  1253 = EL8MSWIN1253  # Greek - MS-Windows                            ASCII
S 28709 > WE8EBCDIC37   # United States/Canada (CP28709==CP37)          EBCDIC
#
# Multibyte codepage mappings
#
#S   833 > KO16DBCS       # Korean Extended single-byte                  EBCDIC
#D   834 > KO16DBCS shift # Korean double-byte                           EBCDIC
#M   933 = KO16DBCS shift # Korean Mixed multi-byte                      EBCDIC

#MBC 933 = 833 834        # Korean Mixed multi-byte                      EBCDIC
#
#S  1088 > KO16MSWIN949 # Korean KS single-byte PC-Data                  ASCII
#D   951 > KO16MSWIN949 # Korean KS double-byte PC-Data                  ASCII
#M   949 = KO16MSWIN949 # Korean KS multi-byte PC-Data                   ASCII
#MBC 949 = 1088 951     # Korean KS multi-byte PC-Data                   ASCII
#
#S   891 > KO16KSC5601 # Korean single-byte                              ASCII
#S  1040 > KO16KSC5601 # Korean single-byte                              ASCII
#D   926 > KO16KSC5601 # Korean double-byte                              ASCII
#M   934 = KO16KSC5601 # Korean multi-byte                               ASCII
#M   944 > KO16KSC5601 # Korean multi-byte                               ASCII
#MBC 934 = 891 926     # Korean multi-byte                               ASCII
#MBC 944 = 1040 926    # Korean multi-byte Extended                      ASCII
#
#S 28709 > ZHT16DBCS       # Traditional Chinese single-byte             EBCDIC
#D   835 > ZHT16DBCS shift # Traditional Chinese double-byte             EBCDIC
#M   937 = ZHT16DBCS shift # Traditional Chinese multi-byte              EBCDIC
#MBC 937 = 28709 835       # Traditional Chinese multi-byte              EBCDIC
#
#S  1114 > ZHT16MSWIN950 # Traditional Chinese single-byte               ASCII
#D   947 > ZHT16MSWIN950 # Traditional Chinese double-byte               ASCII
#M   950 = ZHT16MSWIN950 # Traditional Chinese multi-byte                ASCII
#MBC 950 = 1114 947      # Traditional Chinese multi-byte                ASCII
#

#S   836 > ZHS16DBCS       # Simplified Chinese single-byte              EBCDIC
#D   837 > ZHS16DBCS shift # Simplified Chinese double-byte              EBCDIC
#M   935 = ZHS16DBCS shift # Simplified Chinese multi-byte               EBCDIC
#MBC 935 = 836 837         # Simplified Chinese multi-byte               EBCDIC
#
#S  1027 > JA16DBCS       # Japanese single-byte                         EBCDIC
#D   300 > JA16DBCS shift # Japanese double-byte                         EBCDIC
#D  4396 > JA16DBCS shift # Japanese double-byte                         EBCDIC
#M   939 = JA16DBCS shift # Japanese multi-byte                          EBCDIC
#M  5035 > JA16DBCS shift # Japanese multi-byte                          EBCDIC
#MBC  939 = 1027 300      # Japanese multi-byte                          EBCDIC
#MBC 5035 = 1027 4396     # Japanese multi-byte                          EBCDIC
#
#S   290 > JA16EBCDIC930       # Japanese single-byte                    EBCDIC
#D   300 > JA16EBCDIC930 shift # Japanese double-byte                    EBCDIC
#D  4396 > JA16EBCDIC930 shift # Japanese double-byte                    EBCDIC
#M   930 = JA16EBCDIC930 shift # Japanese multi-byte                     EBCDIC
#M  5026 > JA16EBCDIC930 shift # Japanese multi-byte                     EBCDIC
#MBC  930 = 290 300            # Japanese multi-byte                     EBCDIC
#MBC 5026 = 290 4396           # Japanese multi-byte                     EBCDIC
#

Refer to the following list to check the character set of an existing database:

• for DB2/OS390: Ask your system administrator. There is no single command you use.

• for DB2/400: Run the command DSPSYSVAL SYSVAL(QCCSID)

• for DB2/UDB: Ask your system administrator. There is no single command you use.

• for DB2/VM: Run the statement ID. This shows you the default CCSIDs used at startup.

Multibyte and DoubleEByte Support in the Gateway

To enable the gateway to properly handle doubleEbyte and multibyte data, you must configure the code page map facility with proper multibyte maps and (as an option) you can set the following gateway configuration parameters:

• DRDA_GRAPHIC_LIT_CHECK

• DRDA_GRAPHIC_TO_MBCS

• DRDA_MBCS_TO_GRAPHIC

• DRDA_GRAPHIC_PAD_SIZE

• DRDA_GRAPHIC_CHAR_SIZE

Refer to Appendix C, "DRDAESpecific Parameters", for the values of these parameters.

Configuring the code page map requires knowledge of the code pages that have been configured in the DRDA Server database as well as knowledge of compatible Oracle Database character sets.

IBM coded character set identifiers (CCSIDs) are used to indicate which code pages are configured as the primary codepage for the database, as well as any translation character sets loaded into the database. Some DRDA Servers, such as with DB2, have a translation facility in which character set transforms are mapped between two compatible character sets. For DB2/OS390, these transforms are stored in the table SYSIBM.SYSSTRINGS and transform on the CCSID codepage to another CCSID codepage. In SYSSTRINGS, IN and OUT columns specify the CCSIDs that are used in the transform. Typical transforms are from ASCII to EBCDIC and back again. Two transforms are therefore used for two given CCSIDs.

Multibyte codepages are a composite of a singleEbyte codepage and a doubleEbyte codepage. As an example, the Korean EBCDIC multiEbyte codepage, CCSID 933, is composed of two codepages, codepage 833 (for singleEbyte) and codepage 834 (for doubleEbyte). The DRDA Server, therefore, can send data to the gateway in any of these three codepages, and the gateway must translate suitably depending on which codepage the data is associated with. Because CCSID 933 is an EBCDICEbased codepage, and the gateway must use an ASCIIEbased codepage, we identify an equivalent set of codepages, which are ASCIIEbased. An example would be the Korean multibyte codepage, CCSID 949, which is composed of two codepages, codepage 1088 (for singleEbyte) and codepage 951 (for doubleEbyte).

The codepage map facility is used to map these CCSIDs into the equivalent Oracle Database character sets. Unlike IBM CCSIDs, Oracle Database character sets are unified (in that singleEbyte and doubleEbyte character sets have been combined into one set) and are thus identified by one ID instead of three IDs. In our previous example, the equivalent Oracle Database Character Set for the ASCII Korean codepages would be KO16MSWIN949, and the EBCDIC Korean codepages would be KO16DBCS. These are identified to the gateway by using a set of mapping entries in the codepage.map file.

First, the EBCDIC Korean sets are:

S   833 > KO16DBCS       # Korean Extended single-byte                  EBCDIC
D   834 > KO16DBCS shift # Korean double-byte                           EBCDIC
M   933 = KO16DBCS shift # Korean Mixed multi-byte                      EBCDIC
MBC 933 = 833 834        # Korean Mixed multi-byte                      EBCDIC

Notice that the multibyte set is a bidirectional map to KO16DBCS, while the single and double codepages are mapped one-way to KO16DBCS. Because only one bidirectional CCSID to Oracle Database character set entry for a given pair can exist, we directly map the multibyte sets. And because the singleEbyte and doubleEbyte CCSIDs are ostensibly subsets of KO16DBCS, we map them as oneEway entries. Note that doubleEbyte and multibyte maps are tagged with the shift attribute. This is required for EBCDIC doubleEbyte and multibyte codepages as part of the shift out/in encapsulation of data. Note that the singleEbyte map is not marked because singleEbyte sets are not permitted to contain doubleEbyte data and thus will never use shift encapsulation. Also note that the MBC entry ties the codepages together.

The ASCII Korean sets are similarly mapped and are:

S  1088 > KO16MSWIN949 # Korean KS single-byte PC-Data                  ASCII
D   951 > KO16MSWIN949 # Korean KS double-byte PC-Data                  ASCII
M   949 = KO16MSWIN949 # Korean KS multi-byte PC-Data                   ASCII
MBC 949 = 1088 951     # Korean KS multi-byte PC-Data                   ASCII

Notice that the multibyte set is a bidirectional map to KO16MSWIN949, while the single and double codepages are mapped oneEway to KO16MSWIN949. Because only one bidirectional CCSID to Oracle Database character set entry for a given pair can exist, we directly map the multibyte sets. And because the singleEbyte and doubleEbyte CCSIDs are ostensibly subsets of KO16MSWIN949, we map them as oneEway entries. Note that there is no shift attribute in any of these mappings. This is because ASCIIEbased sets do not use shift out/in encapsulation. Instead, ASCIIEbased sets use a different method (which does not use a shift out/in protocol) to identify doubleEbyte characters.

The above entries supply the necessary codepage mappings for the gateway. To complete the example, we need to specify the correct character set in the HS_LANGUAGE and HS_NLS_NCHAR parameters in the gateway initialization file. The gateway initialization parameters would look as follows:

HS_LANGUAGE=AMERICAN_AMERICA.KO16MSWIN949
HS_NLS_NCHAR=KO16MSWIN949

Note that the specified character set must be ASCIIEbased.

This takes care of configuration of the gateway. The last step is to set up transforms between the EBCDIC codepages and the ASCII codepages in the DRDA Server database. Normally, the gateway would use a total of six transforms, one of each pair in both directions. You may save some table space by installing only the ASCIIEtoEEBCDIC transforms. The reasoning is that the DRDA Server needs to translate only the ASCII data that is sent by the gateway, but the DRDA Server does not need to send ASCII data. The gateway will receive the EBCDIC data and translate as needed. This oneEsided data transfer methodology is called "receiverEmakesEright", meaning that the receiver must translate whatever character set the sender uses. In our example, the DRDA Server is EBCDICEbased, so it will send all data in EBCDIC. The server, therefore, does not need to have an EBCDICEtoEASCII transform because the server will never use the transform.

In our previous example, the DRDA Server database is assumed to be EBCDIC, which is likely to be true for a DB2/OS390 database. For a DB2/UDB database, however, this is not likely to be true. Because most DB2/UDB databases are running on ASCIIEbased computers, they will likely be created with ASCIIEbased codepages. In such cases, the gateway needs to have only one set of codepage map definitions, which are those for the ASCII set. Also, because both the DRDA Server and the gateway will be using the same codepages, no character set transforms need to be loaded into the DB2 database. This can help reduce the amount of CPU overhead that is associated with character translation.

One final note concerning codepage map entries: Be aware that some multiEbyte codepages may be composed of singleEbyte CCSIDs that are already defined in the codepage.map file that is provided with the product. If you are adding a new set of entries to support a multibyte set, then comment out the provided entries so that your new entries will be used correctly.

Additional codepage mappings, which are not already provided, are possible. You may construct entries such as those in our examples, given knowledge of the IBM CCSIDs and the Oracle Database character sets. Because this can be complex (given the IBM documentation of codepage definitions and Oracle Database Character Set definitions), thoroughly test your definitions for all desired character data values before putting them into production.

If you are uncertain, then contact Oracle Support Services to request proper codepage mapping entries.

Message Availability

Whether a language message module is available depends on which modules are installed in the Oracle product set running on the server. If message modules for a particular language set are not installed, then specifying that language with a language parameter does not display messages in the requested language.

Example of NLS Configuration

Following is an example of all the settings needed to configure the gateway, DRDA Server, Oracle server, and client so that a language and character set are working compatibly across the system. In this example, the settings enable a customer in Germany to interact with the gateway in German:

Gateway initsid.ora file:

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_NLS_DATE_FORMAT=YYYY-MM-DD

DRDA Server CCSID:

273 (D8EBCDIC273)

Oracle server and client setting for database:

SELECT USERENV('language') FROM DUAL;
USERENV('LANGUAGE')
-----------------------------
AMERICAN_AMERICA.WE8ISO8859P1

Oracle server and client environment variables:

NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1