D National Language Support

D.1 Overview of NLS Interactions

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

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

Description of the illustration nls_01.gif

Table D-1 describes the architecture illustrated in Figure D-1.

The table discusses describes in detail the parameters and variables needed for NLS processing within each of your computer's environments: the client environment, the Oracle integrating server, the gateway and the DRDA Server.

Parameters Needed for NLS Processing in Your System's Environments 

D.2 Client and Oracle Integrating Server Configuration

A number of NLS parameters control NLS processing between the Oracle Database and client. You can set language-dependent behavior defaults for the server, as well as 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 an 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 re-create the database.

Normally, the default for CHARACTER SET is US7ASCII, which supports only the 26 Latin alphabetic characters. If you have specified 8-bit character sets on the gateway and the DRDA Server, then you must have a compatible 8-bit character set defined on your database. To check the character set of an existing database, issue 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 Database server character set. The Oracle Net facility performs implicit conversion between the Oracle Database character set and the gateway character set.

D.3 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 in which it will be conversing. 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 in which it will be conversing. 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 maps 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 by using DB2 utilities. 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 is used by the gateway for GRAPHIC data interchange. For the list of supported ASCII-based Oracle Character Sets that are mapped to CCSIDs, refer to "Gateway Codepage Map Facility" .

Note that it is not necessary for the gateway character set to 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 character set because the DRDA Server does not have a valid translation for it. Instead, choose a character set that haves the complete intersection with the character set that is used by the DRDA Server. The Oracle Net facility do as any translation between the gateway character set and the Oracle Database server character set.

Figure D-2 Gateway Language Interaction with DRDA Server

Description of the illustration nls_02.gif

HS_LANGUAGE=language[_territory.character_set] 

HS_NLS_DATE_FORMAT=date_format 

D.4 Gateway Codepage Map Facility

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

The default map name is codepage.map and is located in the $ORACLE_HOME/tg4drda/admin directory. Refer to Appendix C, "DRDA-Specific 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 single-byte character set

D designates a double-byte character set

M designates a multi-byte character set

CCSID is the IBM coded character set identifier

direction is one of the following:

• =  mapping is bidirectional

• <  mapping is one-way, Oracle Character Set to CCSID

• >  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 EBCDIC-based double-byte and multi-byte mappings.

The second form of syntax defines a mapping of a multi-byte CCSID to its single-byte and double-byte CCSID equivalents:

MBC multi = single double

Where:

multi is the multi-byte CCSID

single is the single-byte CCSID

double is the double-byte CCSID

This facility is intended for 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
#
# Multi-byte 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: Issue the command DSPSYSVAL SYSVAL(QCCSID)

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

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

D.5 Multi-Byte and Double-Byte Support in the Gateway

To enable the gateway to properly handle double-byte and multi-byte data, you must configure the codepage map facility with proper multi-byte maps, and (as an option) 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, "DRDA-Specific Parameters", for the values of these parameters.

Configuring the codepage map requires knowledge of the codepages 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 codepage is 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. Two transforms are therefore used for two given CCSIDs.

Multi-byte codepages comprise a single-byte codepage and a double-byte codepage. For example, the Korean EBCDIC multi-byte codepage, CCSID 933, is composed of two codepages: codepage 833 (for single-byte) and codepage 834 (for double-byte). The DRDA Server, therefore, can send data to the gateway in any of these three codepages, and the gateway must translate appropriately depending upon which codepage the data is associated with. Because CCSID 933 is an EBCDIC-based codepage, and the gateway must use an ASCII-based codepage, we identify an equivalent set of codepages, which are ASCII-based. For example the Korean multi-byte codepage, CCSID 949, which is composed of two codepages: codepage 1088 (for single-byte) and codepage 951 (for double-byte).

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 single-byte and double-byte character sets have been combined into one set) and are 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.

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 multi-byte set is a bidirectional map to KO16DBCS. However 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, you directly map the multi-byte sets. Because the single-byte and double-byte CCSIDs are ostensibly subsets of KO16DBCS, you map them as one-way entries. Note that double-byte and multi-byte maps are tagged with the shift attribute. This is required for EBCDIC double-byte and multi-byte codepages as part of the shift out/in encapsulation of data. The single-byte map is not marked because single-byte sets are not permitted to contain double-byte data and thus will never use shift encapsulation. 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 multi-byte set is a bidirectional map to KO16MSWIN949. However the single and double codepages are mapped one-way to KO16MSWIN949. Because only one bidirectional CCSID to Oracle Database Character Set entry for a given pair can exist, we directly map the multi-byte sets. And because the single-byte and double-byte CCSIDs are ostensibly subsets of KO16MSWIN949, we map them as one-way entries. Note that there is no "shift" attribute in any of these mappings. This is because ASCII-based sets do not use shift out/in encapsulation. Instead, ASCII-based sets use a different method (which does not use a shift out/in protocol) to identify double-byte 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 ASCII-based.

This takes care of the 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 ASCII-to-EBCDIC transforms, because 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 receives the EBCDIC data and translate as needed. This one-sided data transfer methodology is called receiver-makes-right, meaning that the receiver must translate whatever character set the sender uses. In our example, the DRDA Server is EBCDIC-based, so it will send all data in EBCDIC. The server, therefore, does not need to have an EBCDIC-to-ASCII transform because the server 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 run on ASCII-based computers, they will likely be created with ASCII-based codepages. In such cases, the gateway needs to have only one set of codepage map definitions, which are 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.

Be aware that some multi-byte codepages may be composed of single-byte 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 multi-byte 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 and quite confusing (given the IBM documentation of codepage definitions and Oracle Database Character Set definitions), please 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.

D.6 Message Availability

Whether a language message module is available depends on which modules are installed in the Oracle product set that is 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.

D.7 Example of NLS Configuration

Following is an example of all the settings that are needed to configure the gateway, DRDA Server, Oracle Database, 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 Database server and Client Setting for Database:

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

Oracle Database server and Client Environment Variables:

NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1