Identifiers in SQL
An identifier is a token used to form a name. An identifier in an SQL statement is an SQL identifier or a host identifier. SQL identifiers can be ordinary identifiers or delimited identifiers.
Ordinary identifiers
An ordinary identifier is an uppercase letter followed by zero or more characters, each of which is an uppercase letter, a digit, or the underscore character. If an identifier that is not delimited contains lowercase characters, but otherwise follows all the rules for an ordinary identifier, Db2 folds the lowercase letters to uppercase, and the identifier becomes an ordinary identifier. For more information about ordinary identifiers, see Characters and tokens in SQL.
An ordinary identifier should not be a reserved word. If a reserved word is used as an identifier in SQL, it must be specified in uppercase and must be a delimited identifier or specified in a host variable. For a list of reserved words, see Reserved schema names and reserved words in Db2 for z/OS.
Identifiers that contain only DBCS characters can also be ordinary identifiers, unless otherwise specified.
The following list shows the rules for forming SQL ordinary identifiers:
- The UTF-8 representation of the name must not exceed 128 bytes.
- Continuation to the next line is not allowed.
If the SQL ordinary identifier contains DBCS characters, the following additional rules apply:
- The identifier, if encoded in EBCDIC, must start with a shift-out (X'0E') and end with a shift-in (X'0F'). There must be an even number of bytes between the shift-out and the shift-in. An odd-numbered byte between those shifts must not be a shift-out. DBCS blanks (X'4040' in EBCDIC) are not acceptable between the shift-out and the shift-in.
- The identifiers are not folded to uppercase or changed in any other way.
Delimited identifiers
"VIEW"- Dynamic SQL when the field SQL STRING DELIMITER on installation panel DSNTIPF is set to the quotation mark (") and either of these conditions is true:
- DYNAMICRULES run behavior applies. For a list of the DYNAMICRULES option values that specify run, bind, define, or invoke behavior, see Authorization IDs and dynamic SQL.
- DYNAMICRULES bind, define, or invoke behavior applies and installation panel field USE FOR DYNAMIC RULES is YES.
However, for COBOL application programs, if DYNAMICRULES run behavior does not apply and installation panel field USE FOR DYNAMICRULES is NO, a COBOL compiler option specifies whether the escape character is the quotation mark or apostrophe.
- Static SQL in COBOL application programs. A COBOL compiler option specifies whether the escape character is the quotation mark (") or the apostrophe (').
A delimited identifier can be used when the sequence of characters does not qualify as an ordinary identifier. Such a sequence, for example, could be an SQL reserved word, or it could begin with a digit. Two consecutive escape characters are used to represent one escape character within the delimited identifier. A delimited identifier that contains EBCDIC DBCS characters also must contain the necessary shift characters.
Leading and embedded blanks in the sequence are significant. Trailing blanks in the sequence are not significant. The length of a delimited identifier does not include the starting and ending escape characters. Embedded escape characters (that appear as two characters) are counted in the length as a single character.
Limits for identifiers
See Limits in Db2 for z/OS for length limits that Db2 imposes for identifiers.
Host identifiers
A host identifier is a name declared in the host program.
The rules for forming a host identifier are the rules of the host language. In non-Java™ programs, do not use names beginning with 'DB2', 'SQ'2, 'SQL', 'sql', 'RDI', or 'DSN' because precompilers generate host variable names that begin with these characters. In Java, do not use names beginning with '__sJT_'.
Identifier restrictions for distributed data access
The Db2 internal processing of distributed access must sometimes convert the identifiers for authorization-name, procedure-name, and schema-name between CCSIDs. If there is any possibility that these identifiers will be used in distributed access, restrict the identifiers to characters whose representation in Unicode UTF-8 have code points in the range 0–127. You do not need to enter the identifiers in Unicode; this restriction refers to conversion that Db2 performs internally.