B National Language Support

B.1 Overview of National Language Support

NLS is a technology that enables Oracle applications to interact with users in their native language, using the conventions of that language for displaying data. The Oracle NLS architecture is datadriven, enabling support for specific languages and character encoding schemes to be added without requiring any changes in source code.

There are a number of settings in the gateway, DB2/400, Oracle server, and the client that affect NLS processing. In order for translations to take place correctly, character settings of these components must be compatible. Each character in one encoding scheme must have a matching character in another encoding scheme.

B.2 Gateway Configuration

After the gateway is installed, you must use the CHGORATUN command if you need to change language settings.

language[_territory.character_set]

AMERICAN_AMERICA.WE8EBCDIC37

B.3 DB2/400 Coded Character Set Considerations

If the coded character set identifier (CCSID) of the AS/400 data field differs from 65535, then the Oracle language parameters must correspond to the CCSID of the AS/400 data field that is being accessed. For example, if the CCSID of the data field is 280 for Italy, then the Oracle character set must be set to I8EBCDIC280. The exception to this is for columns with a CCSID of 13488 (UCS-2). The data in these columns will always be converted to the character set that is determined by NLS_LANG. Contact your DBA or refer to the IBM manual for AS/400 National Language Support for additional information about AS/400 CCSID codes.

B.4 Oracle Server and Client Configuration

A number of NLS parameters control NLS processing between the Oracle Database server and the client. You can set language-dependent behavior defaults for the server and set language-dependent behavior for the client to override these defaults. For a complete description of NLS parameters, refer to the NLS chapter in the Oracle Database Globalization Support Guide. These parameters do not affect gateway processing. However, you must ensure that the character set is compatible with the character set that you specify on the gateway and DB2/400. In other words, each character in one encoding scheme must have a matching character in another encoding scheme.

When you create your Oracle Database, the character set that is used to store data is specified by the CHARACTER SET parameter. After the database is created, the database character set cannot be changed unless you re-create the database.

The Oracle database default value for CHARACTER SET is platform dependent and version dependent. The value needs to be compatible with the CHARACTER SET that is used by the gateway. To check the character set of an existing database, issue the following command in SQL*Plus:

SELECT userenv('language') FROM DUAL

Normally, WE8ISO8859P1 is the default CHARACTER SET for Western European languages (including English) on non-EBCDIC platforms. Other languages may get a different default character set, for example, KO16KSC5601 for Korean.

B.5 Message Availability

Availability of the supported language message modules depends on which modules are installed in the Oracle product set that is running on the server. If you do not have message modules installed for a particular language set, then specifying that language with a language parameter results in no messages being displayed for that module in the requested language. Only a generalized (and rather uninformative) message will be provided.

B.6 DB2/400 GRAPHIC Support

When converting DB2/400 data types to Oracle data types, if support for DB2/400 GRAPHIC data types (GRAPHIC, VARGRAPHIC, or LONG VARGRAPHIC) is required, then special consideration must be given to the selection of the NLS_LANG character set. Refer to "DB2/400 GRAPHIC Support" for more information.

B.7 Character Set Expansions

If you use a CREATE TABLE x AS SELECT command in Oracle and the source-table of the CREATE TABLE is a table in DB2/400, you may be surprised as to the width of the character-type columns of the new table in the Oracle DB. This column "expansion" is due to the fact that all codepoints in the CCSID of the DB2/400 column need to be representable in the Oracle column. Problems like this occur when the CHARACTER SET of the Oracle DB is something like UTF8. In this case, no matter what CCSID the DB2/400 column has, there is going to be a character set expansion. This is because there are at least 192 actual code-points in an SBCS code-page on the AS/400, but there are only 94 usable codepoints when that character is to be represented as a single-byte UTF8 character.

Here is an example:

The Japanese Yen character appears at codepoint 0xB2 in the code page represent by CCSID 37 (which corresponds to Oracle Characterset WE8EBCDIC37). But the codepoint for this character is 0xC2A5 in UTF8. The cent-sign appears at codepoint 0x4A in the code page represented by CCSID 37, but is represented by the codepoint 0xC2A2 in UTF8. So even though your data in DB2/400 does not contain either of a Yen or Cent-Sign symbol, the general case must hold that to represent either of these single byte SBCS characters in the Oracle DB, one needs two bytes. Now it actually turns out that there is one, and only one, character in the CCSID 37 codepage that cannot be represented by ANY sequence in the UTF8 characterset. That SBCS character is 0xFF and that is represented by the UTF8 sequence 0xEFBFBD. So, if one does a CREATE TABLE x AS SELECT, then one is always going to get at least a triple expansion of the columns. That is, a column represented by a CHAR(5) in DB2/400 and where that column has a default SBCS CCSID of perhaps 37, the corresponding column in the Oracle DB will be a CHAR(15).