DB2 Version 9.7 for Linux, UNIX, and Windows

Locale names for SQL and XQuery

When used in an SQL or XQuery statement, a locale name consists of one or more ordered pieces of information. The Unicode version prefix, language code, script code, country/region code, and variant codes must be separated with the underscore character (_). Keywords are introduced with the commercial at symbol (@) and multiple keywords are separated by the semicolon character (;).

CLDR version prefix

The behaviors associated with a locale are defined in the Common Locale Data Repository (CLDR). CLDR is a set of standards for locale data, including the formatting of dates, times, time zones, numbers, and currency values; the sorting of text; the translations for country, language, and territory names; and other variables. For more information about CLDR, see http://www.unicode.org/cldr.

As international standards evolve, the behavior associated with a locale may change. Future versions of DB2® may implement newer versions of the CLDR, which might have unexpected effects on applications or database objects. To avoid unexpected changes to locale behavior, the locale name may be prefixed with CLDR 1.5: to force future versions of DB2 to maintain CLDR version 1.5 behavior. If the CLDR version prefix is specified, at least one locale code must also be specified.

DB2 also supports CLDR version 1.2 which can be requested with the CLDR 1.2: prefix. (For compatibility with previous versions of DB2, U400_ can be used as a prefix instead of CLDR 1.2:).

As international standards evolve, the behavior associated with a locale may change. Future versions of DB2 may implement newer versions of the Unicode standard, which may have unexpected effects on applications or database objects.

Note: If the locale is specified as part of a database object (such as an index or constraint), use the CLDR version prefix to avoid unexpected changes in behavior when upgrading to future releases of the DB2 database.

Language code

The languages are specified using a two- or three-letter lowercase code for a particular language. For example, Spanish is "es", English is "en" and French is "fr". The two-letter language code uses the ISO 639 standard. See the ISO 639-2 Registration Authority web page from the Library of Congress at http://www.loc.gov/standards/iso639-2/ for more information.

Script code

The optional four-letter script code follows the language code. If specified, it should be a valid script code as listed on the Unicode ISO 15924 Registry. See the ISO 15924 Alphabetical Code List web page at http://www.unicode.org/iso15924/iso15924-codes.html for more information.

Country/region code

There are often different language conventions within the same language. For example, Spanish is spoken in many countries in Central and South America but the currencies are different in each country. To allow for these differences among specific geographical, political, or cultural regions, locales are specified by two-letter, uppercase codes. For example, "ES" represents Spain and "MX" represents Mexico. The two letter country/region code uses the ISO 3166 standard. See the ISO 3166 code lists from the International Organization for Standardization at http://www.iso.org/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/index.html for more information.

Keywords

The final element of a locale is an optional list of keywords together with their values. Keywords must be unique. Their order is not significant. Unknown keywords are ignored. The handling of keywords depends on the specific services that use them. Currently, the following keywords are recognized:

calendar: A calendar specifier such as "gregorian", "arabic", "chinese", "civil-arabic", "hebrew", "japanese", or "thai-buddhist".
collation: A collation specifier such as "phonebook", "pinyin", "traditional", "stroke", "direct", or "posix".
currency: Any standard three-letter currency code, such as "USD" or "JPY".

Examples

Table 1. Locale name examples
Locale name	Language	Script	Country/region	Keywords	Definition
en_US	en		US		English, United States of America
CLDR 1.5:en	en				English, fixed to CLDR version 1.5 behavior
en_IE@currency=IEP	en		IE	currency= IEP	English, Ireland with Irish Pound
eo	eo				Esperanto
fr@collation=phonebook;calendar=islamic-civil	fr			collation=phonebook calendar=islamic-civil	French (Calendar=Islamic-Civil Calendar, Collation=Phonebook Order)
sr_Latn_YU@currency=USD	sr	Latn	YU	currency=USD	Serbian (Latin, Yugoslavia, Currency=US Dollar)