The database locale

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 and the names of database objects such as databases, tables, columns, and views. For more information, see Name database objects.

The database locale also specifies the writing direction. Most languages are written left-to-right, but some are written right-to-left or top-to-bottom.

HCL OneDB™ 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 Perform 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 Establish 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 the database server accesses columns of locale-sensitive data types, it uses the locale that DB_LOCALE specifies. For more information, see Verify the database locale.

  • When the database server creates a 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 correct use of code sets.

The database server stores a condensed version of the database locale in the systables system catalog table.

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 SET COLLATION statement can specify the localized collation of a different locale to sort NCHAR and NVARCHAR data in the current session.

The condensed version of the database locale is stored in the following two rows of systables, 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. If the database locale defines a localized order for collation, however, 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. If you do not set DB_LOCALE on the client computer, however, the database server uses the value of DB_LOCALE on the server computer as the database locale.

In the connection shown in The client locale, database locale, and server locale, the database server references the database locale when the client application requests sorted information for an NCHAR column in the acctng database. If this 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 ouml, after their unaccented counterparts. Thus, the string ff sorts after ord but before pre. For the syntax to set the database locale, see DB_LOCALE environment variable.