Identifiers

Identifiers are the smallest lexical units used in entity names. The following entities referenced in SQL statements have identifiers:
idmscu19
Identifiers are the smallest lexical units used in entity names. The following entities referenced in SQL statements have identifiers:
  • Access modules
  • Areas
  • Columns
  • Constraints
  • Cursors
  • Groups
  • Indexes
  • Keys
  • Procedures
  • RCMs (SQL statement modules)
  • Referential constraints
  • Schemas
  • Segments
  • Statement names
  • Tables
  • Table procedures
  • User-defined functions
  • Users
  • Views
This article describes the following information:
 
 
2
 
2
 
 
Qualifying Identifiers
Identifiers for some entities can be qualified by other identifiers. For example, table identifiers can be qualified by schema names.
Sometimes an identifier by itself does not uniquely identify an entity. For instance, a SELECT statement may include two columns with the same identifier, each from a different table. To uniquely identify each of these columns, you 
must
 qualify each column identifier with the associated table name or alias.
To qualify an identifier, specify the qualifier first, followed by a period (.), followed by the identifier you are qualifying. The qualified identifier in the following example identifies the EMPLOYEE table associated with the DEMOEMPL schema:
demoempl.employee
Authorization-identifier and table-name
 
Authorization-identifier
 and 
table-name
 are syntactic elements representing identifiers that occur in multiple SQL statements. For expanded syntax for these elements, see Expansion of Authorization-identifier and Expansion of Table-name.
Forming Identifiers
Valid characters
An identifier consists of a combination of:
  • Letters (A through Z and a through z)
  • Digits (0 through 9)
  • At sign (@)
  • Dollar sign ($)
  • Pound sign (#)
  • Underscore (_)
The first character of an identifier must be a letter, @, $, or #.
Maximum length
Identifiers for all entities except columns, access modules, segments, RCMs, and external names of SQL-invoked routines can be as many as 18-characters long.
Identifiers for access modules, segments, RCMs, and external names of SQL-invoked routines can be as many as eight-characters long.
An identifier for a column can be as many as 32-characters long.
Delimited Identifiers
Why delimit identifiers
You can delimit an identifier in double quotation marks to:
  •  
    Allow the use of special characters and blanks
    . An identifier enclosed in quotation marks can consist of any combination of characters. For example, the following is a valid identifier:
    "&ATM*F(0517). MA"
    To include a double quotation mark as part of the identifier itself, use two consecutive double quotation marks. For example:
    "M1K""L9&ZZ".
  •  
    Make case significant
    . When you enclose an identifier in quotation marks, CA IDMS does not convert lowercase letters to uppercase.
    Lowercase letters in quotation marks are not equal to uppercase letters or to lowercase letters that are not in quotation marks. In the example below, the identifiers on the left all identify the same table; the identifier on the right identifies a different table:
    employee "employee" EMPLOYEE "EMPLOYEE"
Placement of quotation marks
If one or more parts of a qualified identifier require quotation marks, place the quotation marks only around the individual parts. Do not include two identifiers in one set of quotation marks. For example, both parts of the following qualified identifier require quotation marks:
"temp-tab-1"."Commission to Date"
When you calculate the length of an identifier, do not include delimiting quotation marks.
Avoiding Keywords as Identifiers
Eliminating ambiguity
You should avoid issuing an SQL statement which specifies an identifier that matches a keyword in the syntax for the statement. This eliminates potential ambiguity that could cause CA IDMS to read the statement in a way that is not meant.
If you must use a keyword as an identifier, delimit the identifier with double quotation marks as described in Delimited Identifiers.
Recognizing keywords
The syntax diagrams in this documentation present keywords in roman (that is, nonitalicized) type.
For example, in the following syntax for CREATE VIEW, the keywords are CREATE VIEW, AS, and WITH CHECK OPTION:
►►─── CREATE VIEW ─┬────────────────┬─
view-identifier
───────────────────────► └─
schema-name
. ─┘ ►─┬────────────────────────────────┬─────────────────────────────────────────► │ ┌──────── , ─────────┐ │ └─ ( ─▼─
view-column-name
─┴─ ) ─┘ ►─── AS
query-specification
─────────────────────────────────────────────────► ►─┬──────────────────────────┬───────────────────────────────────────────────► └─
order-by-specification
─┘ ►─┬─────────────────────┬────────────────────────────────────────────────────►◄ └─ WITH CHECK OPTION ─┘
Delimited example identifier
If it were necessary to create a view called AS, you should identify the view as follows:
create view "AS" (col1, col2, col3) as (select...