Character Formats

Optim™ uses the Unicode character set in dialogs and to process data.

Optim supports the following DBMS character sets:

Table 1. Oracle - Character Set Support
AL16UTF16 JA16SJIS
AL32UTF8 NEE8ISO8859P4
AR8ISO8859P6 N8PC865
AR8MSWIN1256 TR8MSWIN1254
BLT8MSWIN1257 US7ASCII
CDN8PC863 US8PC437
CL8ISO8859P5 UTF8
CL8MSWIN1251 UTF16
EE8ISO8859P2 VN8MSWIN1258
EE8MSWIN1250 WE8DEC
EL8ISO8859P7 WE8ISO8859P1
EL8MSWIN1253 WE8ISO8859P9
IW8ISO8859P8 WE8ISO8859P15
IW8MSWIN1255 WE8MSWIN1252
WE8PC850 WE8PC863
WE8PC860  
Table 2. Sybase ASE - Character Set Support
cp437 cp1257
cp850 iso_1
cp1250 iso_2
cp1251 iso_4
cp1252 iso_5
cp1253 iso_6
cp1254 iso_7
cp1255 iso_8
cp1256 iso_9
roman8 UTF16
UTF8  
Table 3. DB2 z/OS - Character Set Support
437 865
850 1252
860 UTF8
863 UTF16
Table 4. DB2 Linux, UNIX, Windows - Character Set Support
437 964
850 970
860 1252
863 1363
865 1370
936 1383
949 1386
950 UTF8
UTF16  
Table 5. SQL Server - Character Set Support
1252
UTF8
UTF16
Table 6. Informix - Character Set Support
1252
UTF8

Directories and Files

The names of all directories and files referenced by, generated, or used with Optim must consist of ASCII characters. This requirement applies to the installation directories for Optim, as well as the Optim directories (for example, Temporary Work Directory, Data Directory, and other directories that are identified in Personal and Product Options or when configuring the Server).

All text files generated by Optim are in Unicode format and you can edit them with a Unicode-compatible text editor such as Microsoft NotePad. Optim recognizes Byte Order Mark headers in externally generated files and the following encodings:

Note:
  • You cannot compare Archive Files created before Archive for Servers version 6.0 with files created using a current version of Optim
  • You can convert early Archive Files and compare data in the resulting Extract Files
  • Report Files created with earlier versions of Optim are not accessible using version 6.x.

Optim Server

Every locale (or its translation) that the Server is required to handle must reside on the Server machine. In other words, the Server must have access to the locale of the delegating workstation. A utility, pr0locl.exe, is provided to tell you the locales that are installed on a machine and the locales with which it is compatible. As an example of the output in a Windows environment, see the following:

Current operating system: Microsoft Windows XP
C runtime locales are:
   LC_CTYPE    = English_United States.1252
   LC_COLLATE  = English_United States.1252
   LC_NUMERIC  = English_United States.1252
   LC_MONETARY = English_United States.1252
   LC_TIME     = English_United States.1252
Language Environment Variables:
   LC_ALL =
   LANG   = 
Windows Locale is:
   LCID      = 1033 (409)
   Code Page = 1252 (4E4)
RT Server requests can run on or from a UNIX
system that has these locales or their derived locales installed
   C
   en_US.ISO8859-1

Optim Directories and DB Aliases

Optim supports storing data in single-byte (ASCII), Unicode, and multi-byte character sets. The default character set is single byte. When you create an Optim Directory or DB Alias using a database for which Optim supports Unicode or multi-byte characters, you are prompted to indicate the character format used for storing data. To use DB Aliases with different character sets, the Optim Directory must be in Unicode format. If you indicate that the DB Alias for the Optim Directory database should share connection information with the Optim Directory, the DB Alias must use the same character set as the Directory.

Unicode Support

The Optim Directory and DB Aliases can be configured to support universal character encoding (Unicode), if character data in your Unicode-enabled database is kept in Unicode format.

Optim supports the Unicode character set for Oracle, Sybase ASE, Microsoft SQL Server, DB2® Linux, UNIX, Windows, Informix, and DB2 z/OS® databases.

If Optim processes data in a Unicode-enabled database, the Optim Directory must also be in a Unicode-enabled database and the Optim Directory and DB Aliases for Unicode-enabled databases must be flagged during the configuration process.

Oracle

Unicode-enabled Oracle database servers commonly use UTF-8 but may use UTF-16. The Oracle client will typically use a single-byte character set.

Note: Using char semantics from Oracle Unicode Servers for char type columns (longer than 500) and varchar2 type columns (longer than 1000) is not supported in this release.

To prevent any loss of data, the character set used by the database client must be compatible with the character set of the database server.

Optim enforces this requirement as follows.

Version 8i Oracle clients

For release 8i, the character set for the Oracle client is set in the NLS_LANG environment variable, for example:

  • SET NLS_LANG=AMERICAN_AMERICA.UTF8

Restart Optim and/or the Configuration program after making any changes to the character set.

  1. If the client uses a Unicode character set, the database server must also use a Unicode character set. The Optim Directory must reside in a Unicode-enabled database and the Directory and DB Alias for the database must be configured for Unicode data.
  2. If the database server does not use a Unicode character set, the client cannot use one either. The DB Alias for the database must not be configured for Unicode data.
  3. If the database server uses a Unicode character set and the client does not, an error results.

Version 9.0 and Later Oracle Clients

For releases 9.0 and later, the character set for the Oracle client is set in the NLS_LANG environment variable, for example:

  • SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Restart Optim and/or the Configuration program after making any changes to the character set.

Version 9.2 and Later Oracle Clients.

  1. If the client uses a DB Alias configured for Unicode data to connect to a Unicode database, the client character set is automatically set to match the server character set.
  2. If the client uses a DB Alias that is not configured for Unicode data to connect to a Unicode database, an error results.
  3. If the client uses a DB Alias that is not configured for Unicode data to connect to a non-Unicode database, the client character set is automatically set to match that of the server. (See Character Formats for a list of supported character sets.)
  4. If the client uses a DB Alias that is configured for Unicode data to connect to a non-Unicode database, an error results.
  5. If the workstation for the Oracle client uses a non-Unicode character set that is not supported by Optim, an error results.
  6. If the character set for the database server is not supported, an error results.

Microsoft SQL Server

Because SQL Server does not differentiate based on Unicode characteristics, you need not indicate whether an SQL Server Optim Directory or DB Alias is kept in Unicode format. However, the following rules apply:

  1. An Optim Directory in an SQL Server database is kept in Unicode format. You must indicate whether any DB Aliases for Unicode-supported databases are to be kept in Unicode format.
  2. A DB Alias for an SQL Server database must use the same character format as the Optim Directory.

Sybase ASE

To prevent any loss of data, the character set used by the Sybase ASE database client must be compatible with the character set of the database server. Optim enforces this requirement as follows:

  1. If the client uses a DB Alias configured for Unicode data to connect to a Unicode database, the client character set is automatically set to match the server character set.
  2. If the client uses a DB Alias that is not configured for Unicode data to connect to a Unicode database, an error results.
  3. If the client uses a DB Alias that is configured for Unicode data to connect to a non-Unicode database, an error results.

DB2 Linux, UNIX, Windows

To prevent any loss of data, the character set used by the DB2 Linux, UNIX, Windows database client must be compatible with the character set of the database server. Optim enforces this requirement as follows:

  1. All DB2 Linux, UNIX, Windows DB Aliases in a DB2 Linux, UNIX, Windows Optim Directory must have the same Unicode format as the Directory.
  2. If the client uses a DB Alias configured for Unicode data to connect to a Unicode database, the client character set is automatically set to match the server character set.
  3. If the client uses a DB Alias that is not configured for Unicode data to connect to a Unicode database, an error results.
  4. If the client uses a DB Alias that is configured for Unicode data to connect to a non-Unicode database, an error results.
  5. DB2 Linux, UNIX, Windows DB Aliases in an Oracle, Sybase ASE, or MS SQL Server Optim Directory can have different Unicode formats; however, Optim cannot connect to both a Unicode-enabled DB2 Linux, UNIX, Windows database and a non-Unicode-enabled DB2 Linux, UNIX, Windows database during the same session.

DB2 z/OS

To prevent any loss of data, the character set used by the DB2 z/OS database client must be compatible with the character set of the database server. Optim enforces this requirement as follows:

  1. All DB2 z/OS DB Aliases in a DB2 Linux, UNIX, or Windows Optim Directory must have the same Unicode format as the Directory.
  2. If the client uses a DB Alias configured for Unicode data to connect to a Unicode database, the client character set is automatically set to match the server character set.
  3. If the client uses a DB Alias that is not configured for Unicode data to connect to a Unicode database, an error results.
  4. If the client uses a DB Alias that is configured for Unicode data to connect to a non-Unicode database, an error results.
  5. DB2 z/OS DB Aliases in an Oracle, Sybase ASE, or MS SQL Server Optim Directory can have different Unicode formats; however, Optim cannot connect to both a Unicode-enabled DB2 z/OS database and a non-Unicode-enabled DB2 z/OS database during the same session.

If a DB2 z/OS Tablespace includes both Unicode and non-Unicode tables, you must create a separate DB Alias for each table type, a Unicode DB Alias and a non-Unicode DB Alias.

During Load Processing, you can use only one connection, either Unicode or non-Unicode. You must exit Optim before switching between a Unicode or non-Unicode connection.

If the Load Process includes UTF-8 characters in table or column names, the Control File will be in UTF-8 format. Before transferring a UTF-8 Control File to a z/OS machine, the file must be converted to binary format. To browse a UTF-8 Control File on a z/OS machine, you must apply IBM® SPE APAR OA07685 - ISPF Browse Support for Unicode to the machine.

Informix

Unicode support is available for Informix®. If an Optim Directory is in an Informix Unicode database, all DB Aliases must be Unicode.

Multi-byte Support

The Optim Directory and DB Aliases can be configured to support multi-byte character encoding, if character data in your database is kept in a multi-byte character format.

For information about supported multi-byte character sets, see the link for character set support in the Detailed System Requirements document for your release of Optim.

If Optim processes data in a multi-byte-enabled database, the Optim Directory must be in a multi-byte or Unicode-enabled database. The Optim Directory and DB Aliases for multi-byte-enabled databases must be flagged during the configuration process. An Optim Directory in multi-byte format supports multi-byte DB Aliases only.

Optim uses the Unicode character set in dialogs and to process information. In some multi-byte character sets (such as Oracle JA16SJIS), multiple characters are mapped to the same Unicode character. When these characters are converted from Unicode back to multi-byte (a round trip), the original character may not be returned. Optim provides a Product Option (on the Database tab) and a Personal Option (on the Database) that determine how to handle round-trip conversion issues when processing data in a multi-byte database.

Compatible Character Sets

To prevent any loss of data, the character set used by the database client must be compatible with the character set of the database server. Optim enforces this requirement as follows:

  1. If the client uses a DB Alias configured for multi-byte data to connect to a multi-byte database, the client character set is automatically set to match the server character set.
  2. If the client uses a DB Alias that is not configured for multi-byte data to connect to a multi-byte database, an error results.
  3. When connecting to an Optim Directory, the client may establish a connection, check the database character set, drop the connection, and reestablish it with a new language setting.
Note: Because Oracle stores character LOBS in UCS2, a 16-byte Unicode format, multi-byte character LOBS may not be stored correctly in a multi-byte database. For more information, refer to your Oracle documentation.


Feedback