This appendix documents the National Language Support (NLS) information for the Oracle Transparent Gateway for DRDA. This supplements the general Oracle NLS information found in the Oracle Database Application Developer's Guide - Fundamentals.
National Language Support enables users to interact with Oracle applications in their native language, using their conventions for displaying data. The Oracle NLS architecture is dataEdriven, enabling support for specific languages and character encoding schemes to be added without any changes in source code.
There are a number of different settings in the gateway, DRDA Server, Oracle Database 10g server, and client that affect NLS processing. In order for translations to take place correctly, character settings of these components must be compatible.
This appendix contains the following sections:
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
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.
Table D-1 Parameters Needed for NLS Processing in Your System Environments
Environment | Parameter or Variable | Description |
---|---|---|
Client |
An environmental variable. |
|
Oracle integrating server |
This option is set during creation of the database. |
|
Oracle Transparent Gateway for DRDA |
An environmental variable. |
|
Oracle Transparent Gateway for DRDA |
An environmental variable. |
|
Oracle Transparent Gateway for DRDA |
|
An initialization parameter |
Oracle Transparent Gateway for DRDA |
|
An initialization parameter. |
Oracle Transparent Gateway for DRDA |
|
An initialization parameter. |
Oracle Transparent Gateway for DRDA |
|
An initialization parameter. |
DRDA Server |
|
|
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.
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
After the gateway is installed, you must change several parameters to customize for NLS support.
Four parameters in the gateway initialization file (initsid.ora) affect NLS:
HS_LANGUAGE
HS_NLS_NCHAR
HS_NLS_DATE_FORMAT
HS_NLS_DATE_LANGUAGE
HS_LANGUAGE
HS_LANGUAGE
defines the character set that is used for communication between the gateway and the DRDA Server. It specifies the conventions such as: the language used for messages from the target system; names of days and months; symbols for AD, BC, AM, and PM; and default language sorting mechanism.
The syntax of the HS_LANGUAGE
parameter is:
HS_LANGUAGE=language[_territory.character_set]
where:
language
can be any valid language.
territory
is optional, and defaults to AMERICA
.
character_set
is optional and defaults to US7ASCII.
This must be an ASCII base character set name, and it should match a character set listed in the gateway code page map. Refer to "Gateway Codepage Map Facility" for the list of supplied character set mappings.
If you omit the HS_LANGUAGE parameter from initsid.ora, then the default setting is AMERICAN_AMERICA.US7ASCII.
EBCDIC character sets are not supported. The values for language
and territory
(such as AMERICAN_AMERICA
) must be valid, but they have no effect on translations.
HS_NLS_NCHAR
specifies the character set that is used by the gateway to interchange GRAPHIC data. For correct compatibility, set it to the same character set name that is specified in the HS_LANGUAGE
parameter. If it is set to a character set other than that specified in HS_LANGUAGE,
or if it is omitted, then translation errors will occur.
HS_NLS_DATE_FORMAT
HS_NLS_DATE_FORMAT
specifies the format for dates used by the DRDA Server.
The syntax of the NLS_DATE_FORMAT
parameter is:
HS_NLS_DATE_FORMAT=date_format
where date_format
must be YYYY-MM-DD
, the ISO date format. If this parameter is set to any other value or is omitted, then you receive an error when updating, deleting from, selecting from, or inserting into, a table with date columns.
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.
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.
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.
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:
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 HS_NLS_DATE_FORMAT=YYYY-MM-DD
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