SQL identifiers

An SQL identifier can be in one of these categories: short ordinary, medium ordinary, long ordinary, short delimited, medium ordinary, or long delimited.

Ordinary identifiers

An ordinary identifier is a letter that is followed by zero or more characters, each of which is a letter, a digit, or the underscore character. An ordinary identifier with an EBCDIC encoding scheme can include Katakana characters.

Double byte character set (DBCS) characters are allowed in SQL ordinary identifiers. You can specify an SQL ordinary identifier, when it is the name of a table, column, view, or stored procedure, by using either DBCS characters or single-byte character set (SBCS) characters. However, an SQL ordinary identifier cannot contain a mixture of SBCS and DBCS characters.

The following rules show how to form DBCS SQL ordinary identifiers. These rules are EBCDIC rules because all SQL statements are in EBCDIC.

• The identifier must start with a shift-out (X'0E') and end with a shift-in (X'0F'). An odd-numbered byte between those shifts must not be a shift-out.
• The maximum length is 8, 18, or 30 bytes including the shift-out and the shift-in depending upon the context of the identifier. In other words, there is a maximum of 28 bytes (14 double-byte characters) between the shift-out and the shift-in.
• There must be an even number of bytes between the shift-out and the shift-in. DBCS blanks (X'4040') are not acceptable between the shift-out and the shift-in.
• The identifiers are not folded to uppercase or changed in any other way.
• Continuation to the next line is not allowed.

An ordinary identifier must not be identical to a keyword that is a reserved word in any context in which the identifier is used.

The following example is an ordinary identifier:

SALARY

Delimited identifiers

A delimited identifier is a sequence of one or more characters that are enclosed within escape characters. The escape character is the quotation mark (").

You can use a delimited identifier when the sequence of characters does not qualify as an ordinary identifier. Such a sequence, for example, can be an SQL reserved word, or it can begin with a digit. Two consecutive escape characters represent one escape character within the delimited identifier. A delimited identifier that contains double-byte characters also must contain the necessary shift characters.

When the escape character is the quotation mark, the following example is a delimited identifier:

"VIEW"

Short, medium, and long identifiers

SQL identifiers are also classified according to their maximum length. A long identifier has a maximum length of 30 bytes. A medium identifier has a maximum length of 18 bytes. A short identifier has a maximum length of 8 bytes. These limits do not include the escape characters of a delimited identifier.

Whether an identifier is long, medium, or short depends on what it represents.

Parameter marker

Parameter markers represent values that are supplied for the SQL statement when it is run. The question mark (?) identifies a parameter marker in an SQL statement.

Native identifiers

Native identifiers exist only in CREATE TABLE and CREATE INDEX statements and refer to a native database object. For example, objects can be an MVS™ data set name, an IMS segment name, a CA-IDMS record name, and so on. Native identifiers can be ordinary or delimited identifiers. If the native identifier represents a reserved word, then you must supply a delimited identifier.

The length and allowable characters in a native identifier are DBMS-specific.