Informix Guide to GLS Functionality SQL Features

You need to assign names to database objects when you use data definition statements such as CREATE TABLE and CREATE INDEX. This section describes considerations for naming database objects when you use a nondefault locale. In particular, this section explains which SQL identifiers and delimited identifiers accept non-ASCII characters.

Important: To use a nondefault locale, you must set the appropriate locale environment variables for Informix products. For more information, see Setting a Nondefault Locale.

Rules for Identifiers

An SQL identifier is a sequence of letters, digits, and underscores that represents the name of a database object such as a table, column, index, or view. The following table summarizes the rules for SQL identifiers.

When you use multibyte characters in identifiers, you must ensure that the identifier does not exceed the size requirement. For example, the following CREATE SYNONYM statement creates a synonym name of 8 multibyte characters:

CREATE SYNONYM A1A2A3B1B2C1C2C3D1D2E1E2F1F2G1G2H1H2 FOR A1A2B1B2

The synonym name in the preceding example is 18 bytes long (six 2-byte multibyte characters and two 3-byte multibyte characters), so it does not exceed the maximum length for identifiers on Enterprise Decision Server. However, the following CREATE SYNONYM statement generates an error because the total number of bytes in this synonym name is 20:

CREATE SYNOMYM A1A2A3B1B2B3C1C2C3D1D2D3E1E2F1F2G1G2H1H2 FOR A1A2B1B2

This statement specifies four 3-byte characters and four 2-byte characters for the synonym name. Even though the synonym name has only eight characters, the total number of bytes in the synonym name is 20 bytes, which exceeds the maximum length for an identifier.

Informix database servers permit non-ASCII (8-bit and multibyte) characters in many common identifiers such as names of columns, connections, constraints, databases, indexes, roles, SPL routines, synonyms, tables, triggers, and views.

On Enterprise Decision Server, use only single-byte characters in the following identifiers:

• Dbslice
• Logslice
• Coserver
• Cogroup

Use only ASCII alphanumeric 7-bit names for the following identifiers:

• Chunk name
• Filename
• Message-log filename
• Pathname

On Dynamic Server, you can use non-ASCII characters (8-bit and multibyte characters) when you create or refer to any of the following database server names:

• Chunk name
• Message-log filename
• Pathname

The following restrictions affect the ability of the database server to generate filenames that contain non-ASCII characters:

• The database server must know whether the operating system is 8-bit clean.
• The server code set must support these non-ASCII characters.

If you use a nondefault locale that supports a code set with non-ASCII characters, you can use these non-ASCII characters to form most SQL identifiers. In the following table, the SQL Identifier column lists the name of each database object. The SQL Segment column shows the segment that provides the complete syntax of the identifier in the Informix Guide to SQL: Syntax. The Example column describes any special considerations for the identifier and also provides an example of an SQL statement that declares or uses the identifier.

SQL Segments

The SQL Segment column in Figure 3-1 on page 3-6 refers to the segment in the Informix Guide to SQL: Syntax that provides the complete syntax of the identifier. In many cases, the complete syntax of an SQL segment can include other identifiers. For example, the Index Name segment in the Informix Guide to SQL: Syntax shows that the syntax of an index name can include a database name, a database server name, and an owner name as well as the simple name of the index.

When you look up a particular object that is in the table, keep in mind that the simple name of the object accepts multibyte characters, but the other identifiers in the syntax for that object accept multibyte characters only if they also appear in the table. For example, the database name identifier within the Index Name segment accepts multibyte characters, but the identifier for the database server name within the Index Name segment does not accept multibyte characters.

The owner name provides further identification of a database object within a database.

The ANSI term for an owner name is a schema name.

The ability to put non-ASCII characters in the owner-name portion of an identifier depends on whether your operating system supports multibyte characters in user names.

If your database server is on a computer with the UNIX operating system, the owner-name qualifier defaults to the UNIX login ID. However, most versions of UNIX do not support multibyte characters in the UNIX login IDs.

You can use multibyte characters in owner names if you explicitly specify an owner name (in single quotes) when you create database objects. For example, you can assign an owner name that contains multibyte characters when you put the owner-name portion of the index name in quotes in a CREATE INDEX statement.

Warning: If you specify multibyte characters in an owner name on a UNIX system, you do so at your own risk. If a UNIX login ID is used to match the owner name, the match might fail.

The following example shows a CREATE INDEX statement that specifies a multibyte owner name. In this example, the owner name consists of three 2-byte characters:

CREATE INDEX 'A1A2B1B2C1C2'.myidx ON mytable (mycol)

The preceding example assumes that the client locale supports a multibyte code set and that A1A2, B1B2, and C1C2 are valid characters in this code set.

A delimited identifier is an identifier that is enclosed in double quotes. When the DELIMIDENT environment variable is set, the database server interprets sequences of characters in double quotes as delimited identifiers and sequences of characters in single quotes as strings. This interpretation of quotes is compliant with the ANSI standard.

When you use a nondefault locale, you can specify non-ASCII characters in most delimited identifiers. You can put non-ASCII characters in a delimited identifier if you can put non-ASCII characters in the undelimited form of the same identifier. For example, Figure 3-1 on page 3-6 shows that you can put non-ASCII characters in an undelimited index name. Thus you can put non-ASCII characters in an index name that you have enclosed in double quotes to make it a delimited identifier, as follows:

CREATE INDEX "A1A2#B1B2" ON mytable (mycol)

For the complete description of delimited identifiers, see the Identifier segment in the Informix Guide to SQL: Syntax.

In the syntax of an SQL identifier, a letter can be any character that the alpha class of the locale defines. The alpha class lists all characters that are classified as alphabetic. For more information about character classification, see The CTYPE Category. In the default locale, the alpha class of the code set includes the ASCII characters in the ranges a to z and A to Z. When Informix products use the default locale, SQL identifiers can use these ASCII characters wherever letter appears in the syntax of an SQL identifier.

In a nondefault locale, the alpha class of the locale also lists the ASCII characters in the ranges a to z and A to Z. It might also include non-ASCII characters such as non-ASCII digits or ideographic characters. For example, the alpha class of the Japanese UJIS code set (in the Japanese UJIS locale) contains Kanji characters. When Informix products use a nondefault locale, SQL identifiers can use non-ASCII characters wherever letter is valid in the syntax of an SQL identifier. A non-ASCII character is also valid for letter as long as this character is listed in the alpha class of the locale.

The SQL statements in the following example use non-ASCII characters as letters in SQL identifiers:

CREATE DATABASE marché;



CREATE TABLE équipement

	(

	code NCHAR(6),

	description NVARCHAR(128,10),

	prix_courant MONEY(6,2)

	 );



CREATE VIEW çà_va AS

	SELECT numéro,nom FROM abonnés;

In this example, the user creates the following database, table, and view with French-language character names in a French locale (such as fr_fr.8859-1):

• The CREATE DATABASE statement uses the identifier marché, which includes the 8-bit character é, to name the database.
• The CREATE TABLE statement uses the identifier équipement, which includes the 8-bit character é, to name the table, and the identifiers code, description, and prix_courant to name the columns.
• The CREATE VIEW statement uses the identifier çà_va, which includes the 8-bit characters ç and à, to name the view.
• The SELECT clause within the CREATE VIEW statement uses the identifiers numéro and nom for the columns in the select list and the identifier abonnés for the table in the FROM clause. Both numéro and abonnés include the 8-bit character é.

All of the identifiers in this example conform to the rules for specifying identifiers. For these names to be valid, the client locale must support a code set with these French characters.

For the complete syntax and usage of identifiers in SQL statements, see the Identifier segment in the Informix Guide to SQL: Syntax.