Locale Categories

A locale file specifies behaviors for the locale categories. The CTYPE and COLLATION categories primarily affect how the database server stores and retrieves character data in a database. The NUMERIC, MONETARY, and TIME categories affect how a client application formats the internal values of the associated SQL data types. For more information about end-user formats, see End-User Formats and Customizing End-User Formats. The following table describes the locale categories and the behaviors for the default locale, U.S. English.

The CTYPE Category

The CTYPE category defines how to classify the characters of the code set that the locale supports. This category includes specifications for which characters the locale classifies as spaces, blanks, control characters, digits, uppercase letters, lowercase letters, and punctuation symbols.

This category might also include mappings between uppercase and lowercase letters. IBM Informix products access this category when they need to determine the validity of an identifier name, to shift the letter case of a character, or to compare characters.

The COLLATION Category

The COLLATION category can define a localized order. When an IBM Informix product needs to compare two strings, it first breaks up the strings into a series of collation elements. The database server compares each pair of collation elements according to the collation weights of each element. The COLLATION category supports the following capabilities:

• Multicharacter collation elements define sets of characters that the database server should collate as a single unit. For example, the localized collating order might treat the Spanish double-L (ll) as a single collation element instead of as a pair of l's.
• Equivalence classes assign the same collation weight to different elements. For example, the localized order might specify that a and A are an equivalence class (a and A are equivalent characters).

The difference in collation order is the only distinction between the CHAR and NCHAR data types and the VARCHAR and NVARCHAR data types. For more information, see Using Character Data Types.

If a locale does not contain a COLLATION category, IBM Informix products use code-set order for collation of all character data types:

• CHAR
• LVARCHAR
• NCHAR
• NVARCHAR
• TEXT
• VARCHAR

The NUMERIC Category

The NUMERIC category defines the following numeric notation for end-user formats of nonmonetary numeric values:

• The numeric decimal separator
• The numeric thousands separator
• The number of digits to group together before inserting a thousands separator
• The characters that indicate positive and negative numbers

This numeric notation applies to the end-user formats of data for numeric (DECIMAL, INTEGER, SMALLINT, FLOAT, SMALLFLOAT) columns within a client application.

The NUMERIC category also defines alternative digits for use in era-based dates and times. For information about alternative digits, see Alternative Date Formats and Alternative Time Formats.

The MONETARY Category

The MONETARY category defines the following currency notation for end-user formats of monetary values:

• The currency symbol, and whether it appears before or after a monetary value
• The monetary decimal separator
• The monetary thousands separator
• The number of digits to group between each appearance of a monetary thousands separator
• The characters that indicate positive and negative monetary values and the position of these characters (before or after)
• The scale (the number of fractional digits to the right of the decimal point) to display

This currency notation applies to the end-user formats of data from MONEY columns within a client application.

The MONETARY category also defines the default scale for a MONEY column. For the default locale (U.S. English), the database server stores values of the data type MONEY(precision) in the same internal format as the data type DECIMAL(precision,2). A nondefault locale can define a different default scale. For more information on default scales, see Specifying Values for the Scale Parameter.

The TIME Category

The TIME category lists characters and symbols that format date and time values. This information includes the names and abbreviations for days of the week and months of the year. It also includes special representations for dates, time (12-hour and 24-hour), and DATETIME values.

These representations can include the names of eras (as in the Japanese Imperial era system) and non-Gregorian calendars (such as the Arabic lunar calendar). The locale specifies the calendar (Gregorian, Hebrew, Arabic, Japanese Imperial, and so on) for reading or printing a month, day, or year.

If the locale supports era-based dates and times, the TIME category defines the full and abbreviated era names and special date and time representations. For more information, see Alternative Date Formats and Alternative Time Formats.

This date and time information applies to the end-user formats of data in DATE and DATETIME columns within a client application.

The MESSAGES Category

The MESSAGES category defines the format for affirmative and negative responses. This category is optional. IBM Informix products do not use the strings that the MESSAGES category defines.

To obtain the locale name for the MESSAGES category of the client locale, a client application uses the locale that CLIENT_LOCALE indicates. If CLIENT_LOCALE is not set, the client sets the category to the default locale.