Informix Guide to GLS Functionality GLS Fundamentals

For the database server and the client application to communicate successfully, you must establish the appropriate GLS locales for your environment. A GLS locale name identifies the language, territory, and code set that you want your Informix product to use. For the syntax of the locale names, see GLS-Related Environment Variables.

An Informix product uses the locale name to find the corresponding GLS locale file. A locale file is a runtime version of the locale information. The locale name must correspond to a GLS locale file in a subdirectory of the Informix installation directory (which the INFORMIXDIR environment variable indicates) called gls. For more information on GLS locale files, see Appendix A, Managing GLS Files.

This section describes the following topics:

• Locales that you must establish in the client/server environment
• The default locale
• How to establish a nondefault locale

When a database application runs in a client/server environment, the client application, database server, and one or more databases might reside on different computers. Figure 1-1 shows a sample database server connection between an ESQL/C client application and the acctng database through an Informix database server.

Figure 1-1 Example of a Client/Server Environment

These computers might have different operating systems or different language support. To ensure that these three parts of the database application communicate locale information successfully, Informix products support the following locales:

• The client locale identifies the locale that the client application uses.
• The database locale identifies the locale of the data in a database.
• The server locale identifies the locale that the database server uses for its server-specific files.

Figure 1-2 shows the client locale, database locale, and server locale that the sample ESQL/C application (from Figure 1-1 on page 1-22) establishes.

Figure 1-2 The Client Locale, Database Locale, and Server Locale

When you set the same or compatible GLS locales for each of these locales, your client application is not dependent on how the operating system of each computer implements language-specific features.

The following sections describe each of these locales in more detail.

The client locale specifies the language, territory, and code set that the client application uses to perform read and write (I/O) operations. In a client application, I/O operations include reading a keyboard entry or a file for data to be sent to the database and writing data that the database server retrieves from the database to the screen, a file, or a printer. In addition, an SQL API client uses the client locale for literal strings (end-user formats), embedded SQL (ESQL) statements, and host variables.

Informix products use the CLIENT_LOCALE environment variable for the following purposes:

• When the preprocessor for ESQL/C processes a source file, it accepts C source code that is written in the code set of the CLIENT_LOCALE.

The C compiler and the operating system that you use might impose limitations on the ESQL/C program. For more information, see Generating Non-ASCII Filenames.

• When an ESQL/C client application executes, it checks CLIENT_LOCALE for the name of the client locale, which affects operating-system filenames, contents of text files, and formats of date, time, and numeric data.

For more information, see Handling Non-ASCII Characters.

• When a client application and a database server exchange character data, the client application performs code-set conversion when the code set of the CLIENT_LOCALE environment variable is different from the code set of DB_LOCALE (on the client computer).

Code-set conversion prevents data corruption when these two code sets are different. For more information, see Performing Code-Set Conversion.

• When the client application requests a connection, it sends information, including the CLIENT_LOCALE, to the database server.

The database server uses CLIENT_LOCALE when it determines how to set the client-application information of the server-processing locale. For more information, see Establishing a Database Connection.

• When database utilities create files, the filenames and file contents are in the code set that CLIENT_LOCALE specifies.
• When a client application looks for product-specific message files, it checks the message directory that is associated with the name of the client locale (CLIENT_LOCALE).

For more information, see Locating Message Files.

In the sample connection that Figure 1-2 on page 1-23 shows, if the client locale is German with the Windows Code Page 1252 (de_de.1252), the German locale-specific information that the ESQL/C client application uses includes the following:

• Valid date end-user formats support the following format for the U.S. English date of Tuesday, 02/11/1997:
Tu., 11. Feb 1997
• Valid monetary end-user formats support the following format for the U.S. English amount of $354,446.02:
DM354.446,02

Tip: To provide this information for the client locale, the locale file contains the following locale categories: COLLATION, CTYPE, TIME, MONETARY, and NUMERIC. For more information, see Locale Categories.

To determine the client locale, Informix client applications use environment variables set on the client computer. To obtain the localized order and end-user formats of the client locale, a client application uses the following precedence:

1. DBDATE and DBTIME environment variables for the end-user formats of date and time data and DBMONEY for the end-user format of monetary data (if one of these is set)
2. GL_DATE and GL_DATETIME environment variables for the end-user formats of date and time data (if one of these is set)
3. The information that the client locale defines (CLIENT_LOCALE, if it is set)
4. The default locale (U.S. English)

Client applications that are based on Informix Dynamic Server 2000 use the precedence of steps 2, 3, and 4 in the preceding list. You do not need to set the other environment variables for Dynamic Server client applications.

Support for DBDATE and DBTIME provides backward compatibility for client applications that are based on earlier versions of Informix products. Informix recommends that you use GL_DATE and GL_DATETIME for new applications.

The database locale, which is set with the DB_LOCALE environment variable, specifies the language, territory, and code set that the database server needs to correctly interpret locale-sensitive data types (NCHAR and NVARCHAR) in a particular database. The code set specified in DB_LOCALE determines which characters are valid in any character column, as well as the names of database objects such as databases, tables, columns, and views. For more information, see Naming Database Objects.

Informix products use the DB_LOCALE environment variable for the following purposes:

• When a client application and a database server exchange character data, the client application performs code-set conversion when the value of the DB_LOCALE environment variable (on the client computer) is different from the value of CLIENT_LOCALE.

Code-set conversion prevents data corruption when these two code sets are different. For more information, see Performing Code-Set Conversion.

• When the client application requests a connection, it sends information, including the DB_LOCALE (if it is set), to the database server.

The database server uses DB_LOCALE when it determines how to set the database information of the server-processing locale. For more information, see Establishing a Database Connection.

• When a client application tries to open a database, the database server compares the value of the DB_LOCALE environment variable that the client application passes with the database locale that is stored in the database.

When a database server accesses data in columns with locale-specific data types (NCHAR, NVARCHAR), it uses the locale that is saved in the database. For more information, see Verifying the Database Locale.

• When the database server creates a new database, it examines the database locale (DB_LOCALE) to determine how to store character information in the system catalog of the database. This information includes operations such as how to handle regular expressions, compare character strings, and ensure proper use of code sets.

The database server stores a condensed version of the database locale in the systables system catalog table of the database. When the database server stores the database locale information directly in the system catalog, it permanently attaches the locale to the database. This information is used throughout the lifetime of the database. In this way, the database server can always determine the locale that it needs to interpret the locale-sensitive data correctly.

The condensed version of the database locale is stored in the following two rows of the system catalog, which store the condensed locale name in the site column:

• The row with tabid 90 stores the COLLATION category of the database locale.

The collation order determines the order in which the characters of the code set collate. If the database locale defines only a code-set order for collation (as does the default locale, U.S. English), the database server creates CHAR and VARCHAR columns to store the character information. However, if the database locale defines a localized order for collation, the database server creates NCHAR and NVARCHAR columns to store this character information.The tabname value for this row is GLS_CollatE.

• The row with tabid 91 stores the CTYPE category of the database locale.

The CTYPE category of a locale determines how characters of the code set are classified. The database server uses character classification for case conversion and some regular-expression evaluation.The tabname value for this row is GLS_CTYPE.

The database server uses the value of the DB_LOCALE environment variable that the client application sends. However, if you do not set DB_LOCALE on the client computer, the database server uses the value of DB_LOCALE on the server computer as the database locale.

In the sample connection shown in Figure 1-2 on page 1-23, the database server references the database locale when the client application requests sorted information for an NCHAR column in the acctng database. If the database locale is German with the Windows Code Page 1252 (de_de.1252), the database server uses a localized order that sorts accented characters, such as ö, after their unaccented counterparts. This order means that the string öff sorts after ord but before pre. For the syntax to set the database locale, see DB_LOCALE.

The server locale, which is set with the SERVER_LOCALE environment variable, specifies the language, territory, and code set that the database server uses to perform read and write (I/O) operations on the server computer (the computer on which the database server runs). These I/O operations include reading or writing the following files:

• Diagnostic files that the database server generates to provide additional diagnostic information
• Log files that the database server generates to record events
• Explain file, sqexplain.out, that the SQL statement SET EXPLAIN generates

However, the database server does not use the server locale to write files that are in an Informix proprietary format (database and table files). For a more detailed description of the files that the database server writes using the server locale, see Chapter 4, Database Server Features.

When a database server looks for product-specific message files, it looks in the message directory that is associated with the locale specified in SERVER_LOCALE. For more information, see Locating Message Files.

In the sample connection that Figure 1-2 on page 1-23 shows, the Informix database server uses the locale specified in SERVER_LOCALE to determine the code set to use when it writes a message-log file. For the syntax to set the server locale, see SERVER_LOCALE.

Tip: The database server is the only Informix product that needs to know the server locale. Any database server utilities that you run on the server computer use the client locale to read from and write to files and the database locale (on the server computer) to access databases that are set on the server computer.

The server locale and the server-processing locale are two different locales. For more information about the server-processing locale, see Determining the Server-Processing Locale.

The Default Locale

Informix products use U.S. English as the default locale. This locale specifies the following information:

• The U.S. English language and an English-language code set
• Standard U.S. formats for monetary, numeric, date, and time data

To use the default locale for your Informix database applications, you do not need to perform any special steps. However, if you want to use a customized version of U.S. English, British English, or another language, you must set environment variables to identify the appropriate locale. For information on how to specify a GLS locale, see Setting a Nondefault Locale.

The default locale, U.S. English, has the following locale name, where en indicates the English language, us indicates the United States territory, and the numbers indicate the platform-specific name of the default code set.

The default code set is the code set that the default locale supports. When you use the default locale, the default code set supports both the ASCII code set and some set of 8-bit characters. For a chart of ASCII values, see the Relational Operator segment in the Informix Guide to SQL: Syntax. The following table describes the default code set for each platform.

In a locale name, you can specify the code set as either the code-set name or the condensed form of the code-set name. For example, the following locale names both identify the U.S. English locale with the ISO8859-1 code set:

• The locale name en_us.8859-1 uses the code-set name to identify the ISO8859-1 code set.
• The locale name en_us.0333 uses the condensed form of the code-set name to identify the ISO8859-1 code set.

For more information on the condensed form of a code-set name, see Code-Set-Conversion Filenames.

When you use the default locale, Informix products use the following end-user formats for date and time values:

• For DATE values: %m/%d/%iy
• For DATETIME values: %iY-%m-%d %H:%M:%S

For information about these formatting directives, see GL_DATE and GL_DATETIME. For an introduction to date and time end-user formats, see Date and Time Formats. For information about how to customize these end-user formats, see Customizing Date and Time End-User Formats.

When you use the default locale, Informix products use the following end-user formats for numeric and monetary values:

• The thousands separator is the comma (,).
• The decimal separator is the period (.).
• Three digits appear between each thousands separator.
• The positive and negative signs are plus (+) and minus (-), respectively.

For monetary values, Informix products also use the currency symbol, which is the dollar sign ($) and which appears in front of a monetary value.

For an introduction to numeric and monetary end-user formats, see Numeric and Monetary Formats. For information about how to customize these end-user formats, see Customizing Monetary Values.

By default, Informix products use the U.S. English locale. However, Informix products support many other locales. To use a nondefault locale, you must set the following locale environment variables:

• Set the CLIENT_LOCALE environment variable to specify the appropriate client locale.

If you do not set CLIENT_LOCALE, the client locale is the default locale, U.S. English.

• Set DB_LOCALE on each client computer to specify the appropriate database locale for a client application to use when it connects to a database.

If you do not set DB_LOCALE on the client computer, the client application sets the database locale to the client locale. This default value keeps the client application from having to perform code-set conversion.

You might also want to set DB_LOCALE on the server computer so that the database server can perform operations such as the creation of databases (when the client does not specify its own DB_LOCALE).

• Set the SERVER_LOCALE environment variable to specify the appropriate server locale.

If you do not set SERVER_LOCALE, the server locale is the default locale, U.S. English.

When you want to access a database locale with a nondefault locale, the client and database locales on your client computer must support this nondefault locale. Make sure that these two locales are the same or that their code sets are convertible. For information about convertible code sets, see Performing Code-Set Conversion.

For example, to access a database with a Japanese SJIS locale, set both the DB_LOCALE and CLIENT_LOCALE environment variables to the ja_jp.sjis locale name. If you set DB_LOCALE but do not set CLIENT_LOCALE, the client application returns an error because it cannot set up code-set conversion between SJIS (the database code set) and the default code set (the code set of the default locale).

When a client application requests a connection, the database server uses information in the client, database, and server locales to create the server-processing locale. For more information, see Establishing a Database Connection.