Using the auto-codepage feature of IBM Lotus Connector for Oracle 8.x
Abhijeet V Sange
Systems Software Engineer
IBM Software Group
Pune, MH India
October 2009
Summary: The purpose of this article is to explain how to build an application using the IBM® Lotus® Connector for Oracle’s auto-codepage feature, using IBM Lotus Enterprise Integrator® (LEI) for Domino® 8.x and Lotus Connector LotusScript® Extension (LCLSX) for Notes/Domino 8.x.
Contents
1 Overview
1.1 Lotus Enterprise Integrator
1.2 Lotus Connector LotusScript Extension
1.2 What is a codepage?
2 Character set and the Oracle database
2.1 Oracle’s NLS_LANG parameter
2.2 Checking the current NLS_LANG settings
3 New features in Lotus Connector for Oracle 8.x
4 Specifying codepage information
5 Employee application example
6 Conclusion
7 Resources
About the author
1 Overview
Let’s begin with an overview of the relevant applications.
1.1 Lotus Enterprise Integrator
LEI software is an enterprise application integration (EAI) tool that provides industrial-strength, bidirectional data movement, exchange, transformation, and synchronization across a wide range of applications and data sources.
Connectors such as SAP, Open Database Connectivity (ODBC), and Oracle are useful for data movement in LEI, with LEI release 8.0 supporting Oracle 9i and 10g databases.
1.2 Lotus Connector LotusScript Extension
The LCLSX gives you the power to access a range of database management systems (DBMS), ODBC, various platform, text files, ERP systems, and file systems using LotusScript.
LCLSX can be used to write scripted solutions for data access, which can be used for data management solutions (batch transfer) as well as for real-time solutions. The use of LCLSX gives you more granular control over data, enabling you to build application logic that cannot be defined in LEI form. Furthermore, using LCLSX, you can access data exactly where and when it is required.
Since LCLSX is included in both the Domino Enterprise Server as well as in the Lotus Notes Client, applications can be deployed in both server and clients. LCLSX supports advanced features like connection pooling, support for advanced data type, easy handling of arrays, and working with multiple rows.
1.2 What is a codepage?
A codepage (or character set) is an internal table that the operating system uses to map symbols (letters, numerals, and punctuation characters) to a character number. Different codepages provide support for the character sets used in different countries.
Codepages can be referred to by number; for example, codepage 932 represents the Japanese character set, and codepage 950 represents one of the Chinese character sets.
Basically, the character set configuration settings of various information technology components like databases and middleware controls how individual data characters, both visible and control characters, are encoded for transmission, processing, display, and storage.
2 Character set and the Oracle database
Character set is information that addresses social and cultural requirements of the country and its language. Associated information influences the formatting and parsing of dates, times, numbers, and currencies. The database supports various characters set to help database users for internationalization of their operations and expand service to all around the world.
2.1 Oracle’s NLS_LANG parameter
Oracle software uses the NLS_LANG environmental parameter to specify codepage information. You can set language and territory by using NLS_LANG for client applications as well for the database server.
The client’s application character set matches the character set used by the application for interacting with native users (data entering/displaying).
The NLS_LANG parameter contains three sub-parameters: language, territory, and character set. These parameters can be specified as follows:
NLS_LANG = language_territory.charset
The sub-parameters of NLS_LANG represent components of character set.
Language. Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names. Each supported language has a unique name; for example, AMERICAN, FRENCH, or GERMAN.
Territory. Specifies conventions such as the default date, monetary formats, and numeric formats. Each supported territory has a unique name; for example, AMERICA, FRANCE, or CANADA.
Charset. Specifies the character set used by the client application (normally the Oracle character set that corresponds to the user's terminal character set or the OS character set).
Each supported character set has a unique acronym, for example, US7ASCII, WE8ISO8859P1, WE8DEC, WE8MSWIN1252, or JA16EUC. Each language has a default character set associated with it.
The remainder of this article focuses on the proper use of the NLS_LANG parameter with LEI and LCLSX, as it is the least understood and most important piece to set correctly. Improperly setting the parameter could lead to loss of data either as garbage or complete loss of data while transforming.
2.2 Checking the current NLS_LANG settings
NLS_LANG is set during the installation of Oracle and can be later modified. The following are some tips that might be useful when dealing with NLS_LANG:
On Microsoft Windows®, NLS_LANG is represented either through the Registry or as an environment variable. The value set in the environment takes precedence over the Registry NLS_LANG value. The environment NLS_LANG is used by all the Oracle Home on machine.
To view the current NLS_LANG settings, use the SQL*Plus prompt as follows:
1. To check if it’s set in the environment, use
SQL> HOST ECHO %NLS_LANG%
This will return only %NLS_LANG% if the variable is not set in the environment. A valid value might look like this:
RUSSIAN_RUSSIA.CL8MSWIN1251
2. To check if it’s set in the Registry, use
SQL>@.[%NLS_LANG%]
(a) If the output is something like:
“Unable to open file.[AMERICAN_AMERICA.WE8MSWIN1252].”
Then the "file name" between the brackets is the value of the registry parameter.
(b) If you get this as result:
“Unable to open file ".[%NLS_LANG%]." then the parameter NLS_LANG is also not set in the registry.”
Then on UNIX you may want to try the following, to get value of NLS_LANG using SQL*Plus using:
SQL> HOST ECHO $NLS_LANG
There are other techniques available to view NLS_LANG; however, we will not discuss them here.
3 New features in Lotus Connector for Oracle 8.x
Oracle 9 and later supports the ability to choose a codepage to connect to the Oracle server (instead of it always being NLS_LANG). Lotus Domino versions 8 and later provide an improved Lotus Connector for Oracle, including this added advantage: For cases in which there are multiple databases with different codepages, they can now be accessed from a single client.
For example, suppose you have two Oracle database servers, Database X with a codepage set to Simplified Chinese (AMERICAN_AMERICA.ZHS16GBK) and Database Y with a codepage set to Unicode (AMERICAN_AMERICA.AL32UTF8).
With previous releases of Lotus Connector for Oracle, which depend on the NLS_LANG being set by the user, if you want to pull data from these two Oracle databases to Notes databases, the Direct Transfer activity between these databases might be the best choice.
The success or failure of Direct Transfer activities totally depends on the value of NLS_LANG that’s set by the user. If NLS_LANG is not set or is set to the AMERICAN_AMERICA.ZHS16GBK, then data loss may occur during the Direct Transfer activity. Thus the user must always be careful when using NLS_LANG setting.
With the latest version of Lotus Connector for Oracle, you don’t need to worry about properly setting NLS_LANG. Lotus Connector for Oracle now identifies the character set used by the Oracle Database Server while connecting to the database and, depending on the Notes.ini setting in Lotus Domino, uses appropriate character set.
The feature is based on a new interface provided by Oracle 9i Databases and later, in which codepage information is used while connecting to the Oracle Database instead of always depending on the NLS_LANG parameter. The interface also facilitates the detection of codepages of the Oracle Database Server.
When LEI or LCLSX connects to the Oracle Database using Lotus Connector for Oracle, it retrieves the codepage information of the server. Lotus Connector for Oracle then attempts to retrieve local codepage information (from the host machine on which the Oracle Client along with Lotus Domino or LEI is installed).
Lotus Connector for Oracle uses a combination of the Notes.ini setting
EIOracleCheckCharset and predefined precedents, to determine which codepage setting to use.
Lotus Connector for Oracle can use any one of four methods to get the current codepage setting. Users can specify a codepage explicitly by setting a property in the Connection document or setting a property in LCLSX and the Notes.ini parameter.
Table 1 lists the various methods and their order of importance for Lotus Connector for Oracle (the higher the number, the higher the importance).
Table 1. Methods to specify codepage
|
|
|
|
|
|
|
|
|
|
|
Environment parameter to specify locale behavior for Oracle software
|
|
|
Notes INI parameter
EIOracle8NativeText
|
For example, EIOracle8NativeText= IN8ISCII
INI parameter takes only character set.
|
|
|
Oracle Server codepage property
|
From Oracle Connection document and using LCLSX connection object property.
|
Once Lotus Connector for Oracle has determined the codepage of the local system, it then attempts to match that codepage with the Oracle server’s codepage. This comparison is called a Conversion Test. Lotus Connector for Oracle performs conversion tests depending on the value of the Notes.ini setting
EIOracleCheckCharset.
· If this INI parameter is set to -1, then no conversion test will be performed.
· The default value of
EIOracleCheckCharset is zero, which means codepage override, causing Lotus Connector for Oracle to perform a conversion test.
· If the codepages match, then LEI/LCLSX will perform normal operations; however, if they don’t match, then Lotus Connector for Oracle reconnects to the Oracle Database using a codepage specified by the user.
Thus if the codepage specified by the user does not match that of Oracle’s Database Server, then Lotus Connector for Oracle must perform a reconnect operation.
· When
EIOracleCheckCharset is set to 1, then upon failure of the conversion test, Lotus Connector for Oracle will log a warning message indicating that the codepages do not match.
· On the other hand, when
EIOracleCheckCharset is set to 2, Lotus Connector for Oracle will log an error message indication the conversion test failed.
Table 2 summarizes the above parameter settings.
Table 2. EIOracleCheckCharset settings
|
|
|
|
|
Nothing. No conversion test will be performed. (will use NLS_LANG)
|
|
|
Override. This will perform a conversion test to make sure it’s a match and reconnect using specified codepage if not.
|
|
|
Warning. This will perform a conversion test and will provide output to the activity log if it isn't match, but it will continue without changing anything (will use NLS_LANG).
|
|
|
Failure. This will perform conversion test; if codepages do not match, activity will fail.
|
Figure 1 is a flow chart showing how the latest version of Lotus Connector for Oracle connects to the database when used with an LEI data management activity.
Figure 1. Connection process flow chart
4 Specifying codepage information
There are multiple ways to set the Oracle codepage information when using Lotus Connector for Oracle; for example, you can specify this information using an LEI Connection document or using a property in LCLSX, if required for an individual connection.
Notes.ini settings can be used to apply a common codepage for more than one connection to the Oracle database. Lotus Connector for Oracle is by default configured to override the codepage information (only if the conversion test fails), removing the worry of setting the correct codepage information, so you need only to specify the character set information.
For setting codepage information in an LEI Connection document to Oracle, you can set the Oracle Server Codepage property to the required character set, as shown in figure 2.
Figure 2. Setting codepage from Oracle Connection document
In the case of LCLSX, LCConnection class’s OracleTextFormat property can be used to set codepage information. Setting the Lotus Connector for Oracle’s property via the above two methods is the most preferable.
There are also methods to facilitate setting codepage information individually for a connection to Oracle Database. You can set a common codepage for all the connections to Oracle Database on a Domino server by using the Notes.ini setting,
EIOracle8NativeText. Codepage information can be assigned to this Notes.ini setting directly, for example,
EIOracle8NativeText= IN8ISCII.
This method is less desirable than the property method; however, it’s more preferable than that of NLS_LANG. For cases in which LCLSX script is executed on a Notes client, the .INI changes can be added to Notes.ini of the client.
Figure 3. Setting codepage from LCLSX
5 Employee application example
To demonstrate the above functionality, let’s consider the example of the employee application. Let’s assume that the Notes employee application stores data from different Oracle databases of different codepages. The Oracle databases are named UNICODE, SIMPLIFIEDCHINESE, and ENGLISH (located in different geographies) with codepages such as ALU32UTF8, ZHS16GBK, and WE8MSWIN1252, respectively.
From different tables in these Oracle databases, data is pulled into a single Notes database (application) for further processing, using the Direct Transfer Activity of LEI. The Oracle Connection documents are created for each database as shown in figures 4, 5, and 6.
Also, the value of
EIOracleCheckCharset in the Notes.ini of the Domino server is set to zero. This is default setting and ensures that a conversion test is performed and, if codepages don’t match, a reconnection using a specified codepage will be done.
Figure 4. Connection to ENGLISH database
Figure 5. Connection to SIMPLIFIEDCHINESE database
Figure 6. Connection to UNICODE database
As the above figures show, the Oracle Server Codepage field is left blank in all the Connection documents. The
EIOracleCheckCharset setting and the Connection document’s Oracle Server Codepage property allow LEI to use the correct codepage to connect to the Oracle database.
Figure 7 shows a Direct Transfer Activity for transforming data from the SIMPLIFIEDCHINESE Oracle Database to the Notes database. Similarly, Activities can be created to transfer data from the UNICODE Oracle Database and the ENGLISH Oracle Database.
Figure 7. Sample Direct Transfer Activity
After the Activities are run, data from Oracle systems with different codepages are pulled into the Notes database, the contents of which will look like that shown in figure 8.
Figure 8. Notes database content after Activity finishes
The Activity Log document shows the result of the Conversion Test and the steps taken to prevent data loss during the transfer, for example, the information about the Oracle Database codepage and the codepage set using NLS_LANG.
As shown in figure 9, with the default settings of NLS_LANG, for example, WE8MSWIN1252, there will be a mismatch between the codepage of the Oracle server and the settings on the machine where LEI is installed.
Another Conversion Test would simply fail; in such cases, LEI will reconnect to the Oracle database using the codepage value of AL32UTF8.
Figure 9. Activity Log information
Consider an example of the employee application in which we want to find information about an employee and his manager, using Lotus Connector for Oracle in LCLSX script. We assume the names of the Oracle Database are ENGLISH and UNICODE, having codepages of WE8MSWIN1252 and ALU32UTF8, respectively.
The ENGLISH database stores information about the employee, while the UNICODE database stores information about managers. To get complete information about the employee and his manager, data must be pulled from two different Oracle database with different codepage, into a single Notes database.
6 Conclusion
In this article we introduced the Lotus Connector for Oracle’s auto-codepage functionality, covering details about the feature’s usage scenarios and providing examples of the feature in use. Enabling the functionality will help you reduce data loss due to translation issues and incorrect codepage settings during the transfer of data via Lotus Connector for Oracle in LEI and
LCLSX.
7 Resources
· For more information about LEI, including demos, customer stories, and white papers, refer to the
Lotus Enterprise Integrator for Domino product page.
· Refer to the
Lotus Enterprise Integrator documentation for additional LEI information.
· More information about codepages can be find at
http://en.wikipedia.org/wiki/Code_page
· Find out more about Oracle by visiting the
Oracle Database Home Page.
About the author
Abhijeet Sange is a System Software Engineer based at IBM’s India Software Lab in Pune, India, currently working as a developer and Level 3 Support Engineer for LEI, LSXLC & DECS. Since 2007, he has been involved in developing product features offered by the LEI team and in developing the Oracle Connector’s auto-codepage feature. He has a Bachelor of Engineering in Information Technology from Pune University and is pursuing a Master of Science in Software Systems from BITS, Pilani.