Identifiers

An identifier is a token used to form a name. An identifier in an SQL statement is either an SQL identifier, a system identifier, or a host identifier.

Note:

$, @, #, and all other variant characters should not be used in identifiers because the code points used to represent them vary depending on the CCSID of the string in which they are contained. If they are used, unpredictable results may occur. For more information on variant characters, see the book National Language Support, SC41-5101-01.

SQL Identifiers

There are two types of SQL identifiers: ordinary identifiers and delimited 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. Note that ordinary identifiers are converted to uppercase. An ordinary identifier should not be a reserved word.

  See Appendix D, Reserved Words for a list of reserved words. If a reserved word is used as an identifier in SQL, it should be specified in uppercase and enclosed within the SQL escape characters.

• A delimited identifier is a sequence of one or more characters enclosed within SQL escape characters. The sequence must consist of one or more characters. Leading blanks in the sequence are significant. Trailing blanks in the sequence are not significant. The length of a delimited identifier includes the two SQL escape characters for column names, but not for other SQL names. Note that delimited identifiers are not converted to uppercase.

  The escape character is the quotation mark (") except in the following cases where the escape character is the apostrophe ('):

  • Interactive SQL when the SQL string delimiter is set to the quotation mark in COBOL syntax checking statement mode
  • Dynamic SQL in a COBOL program when the CRTSQLCBL or CRTSQLCBLI parameter OPTION(*QUOTESQL) specifies that the string delimiter is the quotation mark (")
  • COBOL application program when the CRTSQLCBL or CRTSQLCBLI parameter OPTION(*QUOTESQL) specifies that the string delimiter is the quotation mark (")

  The following characters are not allowed within delimited identifiers:

  • X'00' through X'3F' and X'FF'

System identifiers

A system identifier is used to form the name of system objects in OS/400. There are two types of system identifiers: ordinary identifiers and delimited identifiers.

• The rules for forming a system ordinary identifier are identical to the rules for forming an SQL ordinary identifier.
• The rules for forming a system delimited identifier are identical to those for forming SQL delimited identifiers, except:
  • The following special characters are not allowed in a delimited system identifier:
    • A blank (X'40')
    • An asterisk (X'5C')
    • An apostrophe (X'7D')
    • A question mark (X'6F')
    • A quotation mark (X'7F')
  • The bytes required for the escape characters are included in the length of the identifier unless the characters within the delimiters would form an ordinary identifier.

    For example, "PRIVILEGES" is in uppercase and the characters within the delimiters form an ordinary identifier; therefore, it has a length of 10 bytes and is a valid system name for a column. On the other hand, "privileges" is in lowercase, has a length of 12 bytes, and is not a valid system name for a column because the bytes required for the delimiters must be included in the length of the identifier.

Examples

   WKLYSAL     WKLY_SAL     "WKLY_SAL"     "UNION"     "wkly_sal"

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; except that DBCS characters cannot be used. For example, the rules for forming a host-identifier in a COBOL program are the same as the rules for forming a user-defined word in COBOL. Names beginning with the characters 'SQ'¹¹, 'SQL', 'sql', 'RDI', or 'DSN' should not be used because precompilers generate host variables that begin with these characters.

¹¹

'SQ' is allowed in C, COBOL, and PL/I; it should not be used in RPG.