Naming conventions in SQL
The rules for forming a name depend on the type of the object designated by the name.
Many database objects have a schema qualified name. A schema qualified name may consist of a single SQL identifier (in which case the schema-name is implicit) or a schema-name followed by a period and an SQL identifier. For more information about the rules for SQL identifiers, see Identifiers in SQL.
- accelerator-name
- A name that identifies an accelerator-only table. An accelerator name is 1 to 8 uppercase characters or digits. The name must be unique within the Db2 subsystem or data sharing group.
- alias-name
A qualified or unqualified name that designates an alias. A fully qualified alias name is a three-part name. The first part is a location name that designates the DBMS at which the alias is defined. The second part is a schema name. The third part is an SQL identifier. A period must separate each of the parts.A two-part sequence is implicitly qualified by the location name of the current server. The first part is a schema name. The second part is an SQL identifier. A period must separate the two parts.
A one-part or unqualified alias name is an SQL identifier with two implicit qualifiers. The first implicit qualifier is the location name of the current server. The second is a schema name, which is determined by the rules specified in Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names.
For more information, see Aliases.
- array-type-name
- A qualified or unqualified name that designates an array type.
A qualified array type name is a two-part name. The first part is the schema name of the array type. The second part is an SQL identifier. A period must separate each of the parts.
An unqualified array type name is an SQL identifier with an implicit qualifier. The implicit qualifier is the schema name, which is determined by the context in which the array type appears, as described by the rules in Unqualified object name resolution.
- authorization-name
- An SQL identifier that designates a set of privileges. It can also designate a user, a group of users, or a role. For a user or a group of users, Db2 does not control this property. For a role, Db2 does control this property. See Authorization IDs, roles, and authorization names for the distinction between an authorization name and an authorization ID.
- aux-table-name
- A qualified or unqualified name that designates an auxiliary table. The rules for the name are the same as the rules for table-name. See table-name.
- bpname
- A
name that identifies a buffer pool. The following list shows the names
of the different buffer pool sizes.
- 4KB
- BP0, BP1, BP2, …, BP49
- 8KB
- BP8K0, BP8K1, BP8K2, …, BP8K9
- 16KB
- BP16K0, BP16K1, BP16K2, …, BP16K9
- 32KB
- BP32K, BP32K1, BP32K2, …, BP32K9
- built-in-type
- A qualified or unqualified name that identifies an IBM®-supplied data type. A qualified name is SYSIBM followed by a period and the name of the built-in data type. An unqualified name has an implicit qualifier, the schema name, which is determined by the rules in Unqualified object name resolution.
- catalog-name
- An SQL identifier that designates an integrated catalog facility (ICF) catalog. The identifier must start with a letter and must not include special characters, or the alphabetic extenders for national languages ($, #, and @ in the United States). These three code points (X'5B', X'7B', and X'7C') should be avoided because they represent different characters depending on the CCSID.
- clone-table-name
- A qualified or unqualified name that designates the name of a clone table. See the definition of table-name for more information about qualification of table names.
- collection-id
- An SQL identifier that identifies a collection of packages, such as a collection ID as a qualifier for a package ID.
- column-name
- A qualified or unqualified name that designates a column of a table or view.
A qualified column name is a qualifier followed by a period and an SQL identifier. The qualifier is a table name, a view name, a synonym, an alias, or a correlation name. The unqualified column name is an SQL identifier.
- constraint-name
- An SQL identifier that designates a primary key, check, referential, or unique constraint on a table.
- context-name
- An SQL identifier that designates a trusted context.
- correlation-name
- An SQL identifier that designates a table, a view, or individual rows of a table or view.
- cursor-name
- An SQL identifier that designates an SQL cursor. In SQLJ, cursor-name is a host variable (with no indicator variable) that identifies an instance of an iterator.
- database-name
- An SQL identifier that designates a database. The identifier must start with a letter and must not include special characters.
- descriptor-name
- A host identifier that designates an SQL descriptor area (SQLDA). See Host variables for a description of a host identifier. A descriptor name never includes an indicator variable.
- distinct-type-name
- A qualified or unqualified name that designates a distinct type.
A qualified distinct type name is a two-part name. The first part is the schema name of the distinct type. The second part is an SQL identifier. A period must separate each of the parts.
An unqualified distinct type name is an SQL identifier with an implicit qualifier. The implicit qualifier is the schema name, which is determined by the context in which the distinct type appears as described by the rules in Unqualified type, function, procedure, global variable, and specific names.
- external-program-name
- A name that specifies the program that runs when the function is invoked or the procedure name is specified in a CALL statement.
- function-name
- A
qualified or unqualified name that designates a user-defined function,
a cast function that was generated when a distinct type was created,
or a built-in function.
A qualified function name is a two-part name. The first part is the schema name of the function. The second part is an SQL identifier. A period must separate each of the parts.
An unqualified function name is an SQL identifier with an implicit qualifier. The implicit qualifier is the schema name, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified type, function, procedure, global variable, and specific names.
- global-variable-name
- A qualified or
unqualified name that designates a global variable.
A qualified global variable name is a two-part name. The first part is the schema name of the global variable. The second part is an SQL identifier. A period must separate each of the parts.
An unqualified global variable name is an SQL identifier with an implicit qualifier. The implicit qualifier is the schema name, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified type, function, procedure, global variable, and specific names.
- host-label
- A token that designates a label in a host program.
- host-variable
- A sequence of tokens that designates a host variable. A host variable includes at least one host identifier, as explained in Host variables.
- index-name
- A qualified or unqualified name that designates an index.
A qualified index name is an authorization ID or schema name followed by a period and an SQL identifier.
An unqualified index name is an SQL identifier with an implicit qualifier. The implicit qualifier is an authorization ID, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified object name resolution.
For an index on a declared temporary table, the qualifier must be SESSION.
- FL 502 key-label-name
- An SQL identifier that corresponds to the value of the Integrated Cryptographic Service Facility (ICSF) key label. A keylabel-name can consist of up to 64 characters. The first character must be a letter or national character (#, $, @). It is recommended that the name not include national characters (
@(X'7C'),#(X'7B'), or$(X'5B')).The identifier must not include the underscore (_) character. The characters allowed in the delimited form are the same as those allowed in the ordinary form, except that it can contain a period (.), but the period cannot be the first character. For more information, see Key label (z/OS ICSF). - location-name
- An SQL identifier that designates the name of a location. A location name is 1 to 16 bytes, does not include alphabetic extenders (national characters), lowercase letters, or Katakana characters. The characters allowed in the delimited form are the same as those allowed in the ordinary form.
- mask-name
- A qualified or unqualified name that designates a mask.
A qualified mask name is a two-part name. The first part is the schema name. The second part is an SQL identifier. A period must separate each of the parts.
A one-part or unqualified mask name is an SQL identifier with an implicit qualifier. The implicit qualifier is an authorization ID, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified object name resolution.
- package-name
- A qualified or unqualified name that designates a package. The unqualified form of a package-name is an SQL identifier. A package-name must not be a delimited identifier that includes lowercase letters or special characters. A package-name in an SQL statement must be qualified. In some contexts outside of SQL, a package name can be specified as an unqualified name.
- parameter-name
- An SQL identifier that designates a parameter in an SQL procedure or SQL function.
- permission-name
- A qualified or unqualified name that designates a permission.
A qualified permission name is a two-part name. The first part is the schema name. The second part is an SQL identifier. A period must separate each of the parts.
A one-part or unqualified permission name is an SQL identifier with an implicit qualifier. The implicit qualifier is an authorization ID, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified object name resolution.
- plan-name
- An SQL identifier that designates an application plan. The identifier must not be a delimited identifier that includes lowercase letters or special characters.
- procedure-name
- A qualified or unqualified name that designates a stored procedure.
A fully qualified procedure name is a three-part name. The first part is a location name that identifies the DBMS at which the procedure is stored. The second part is the schema name of the stored procedure. The third part is an SQL identifier. A period must separate each of the parts in a qualified name.
A two-part procedure name is implicitly qualified with the location name of the current server. The first part is the schema name of the stored procedure. The second part is an SQL identifier. A period must separate the two parts.
A one part, or unqualified, procedure name is an SQL identifier with two implicit qualifiers. The first implicit qualifier is the location name of the current server. The second implicit qualifier is the schema name, which is determined by the context in which the unqualified name appears, as described by the rules in Unqualified object name resolution.
The SQL identifier in a qualified or unqualified name must not be an asterisk (*).
- profile-name
- An SQL identifier that corresponds to a RACF® profile name.
- program-name
- An SQL identifier that designates an exit routine.
- role-name
- An SQL identifier that designates a role. The identifier cannot begin with the characters SYS and cannot be ACCESSCTRL, DATAACCESS, DBADM, DBCTRL, DBMAINT, NONE, NULL, PACKADM, PUBLIC, SECADM, or SQLADM.
- routine-version-id
- An SQL identifier of up to 64 EBCDIC bytes that designates a version of a routine. The UTF-8 representation of the identifier must not exceed 122 bytes.
- savepoint-name
- An SQL identifier that designates a savepoint.
- schema-name
- An SQL identifier that provides a logical grouping for SQL objects. A schema-name is used as a qualifier of the name of SQL objects.
- seclabel-name
- An SQL identifier that corresponds to the value of the RACF security label. It is recommended that the name not include national characters (@ (X'7C'), # (X'7B'), or $ (X'5B')). If the table is a Unicode table and the security label name does include national characters, an error might be issued if substitution occurs when Db2 converts the value from EBCDIC to Unicode.
- sequence-name
- A qualified or unqualified name that designates a sequence.
A qualified sequence name is a two-part name. The first part is the schema name. The second part is an SQL identifier. A period must separate each of the parts.
A one-part or unqualified sequence name is an SQL identifier with an implicit qualifier. The implicit qualifier is an authorization ID, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names.
- server-name
- An SQL identifier that designates an application server. The identifier must start with a letter and must not include lowercase letters or special characters.
- specific-name
- A
qualified or unqualified name that designates a unique name for a
user-defined function.
A qualified specific name is a two-part name. The first part is the schema name. The second part is an SQL identifier, and it must not be an asterisk (*). A period must separate each of the parts.
An unqualified specific name is an SQL identifier with an implicit qualifier. The implicit qualifier is the schema name, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified type, function, procedure, global variable, and specific names.
A specific name can be used to identify a function to alter, comment on, drop, grant privileges on, revoke privileges from, or be the source function for another function. A specific name cannot be used to invoke a function. In addition to being used in certain SQL statements, a specific name must be used in Db2 commands to uniquely identify a function.
- SQL-condition-name
- An SQL identifier that designates
a condition in an SQL routine or trigger.
- SQL-label
- An SQL identifier
that designates a label in an SQL routine or trigger.
- SQL-parameter-name
- A qualified
or unqualified name that designates a parameter in an SQL routine
body. The unqualified form of an SQL-parameter-name is
an SQL identifier. The qualified form is a function-name or procedure-name followed
by a period and an SQL identifier.
- SQL-variable-name
- A qualified
or unqualified name that designates a variable in an SQL routine or
trigger body. The unqualified form of an SQL-variable-name is
an SQL identifier. The qualified form is an SQL-label followed
by a period (.) and an SQL identifier.
- statement-name
- An SQL identifier that designates a prepared SQL statement.
- stogroup-name
- An SQL identifier that designates a storage group.
- synonym
- An SQL identifier that designates a synonym, a table, or a view. The table or view must exist at the current server. A qualified name is never interpreted as a synonym.
- table-name
- A qualified or unqualified name that designates a table.
A fully qualified table name is a three-part name. The first part is a location name that designates the DBMS at which the table is stored. The second part is a schema name. The third part is an SQL identifier. A period must separate each of the parts.
A two-part table name is implicitly qualified by the location name of the current server. The first part is a schema name. The second part is an SQL identifier. A period must separate the two parts.
A one-part or unqualified table name is an SQL identifier with two implicit qualifiers. The first implicit qualifier is the location name of the current server. The second is a schema name, which is determined by the rules set forth in Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names. For a declared temporary table, the qualifier (the second part in a three-part name and the first part in a two-part name) must be SESSION. For complete details on specifying a name when a declared temporary table is defined and then later referring to that declared temporary table in other SQL statements, see DECLARE GLOBAL TEMPORARY TABLE statement.
- table-space-name
- An SQL identifier that designates a table space of an identified database. The identifier must start with a letter and must not include special characters. If a database is not identified, DSNDB04 is implicit.
- trigger-name
- A qualified or unqualified name that designates a trigger.
A qualified trigger name is a two-part name. The first part is the schema name of the trigger. The second part is an SQL identifier. A period must separate each of the parts.
An unqualified trigger name is an SQL identifier with an implicit qualifier. The implicit qualifier is the schema name, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names.
- trigger-version-id
- An SQL identifier
of up to 64 EBCDIC bytes that designates a version of a trigger. The UTF-8 representation of the
identifier must not exceed 122 bytes.
- view-name
- A qualified or unqualified name that designates a view.
A fully qualified view name is a three-part name. The first part is a location name that designates the DBMS where the view is defined. The second part is a schema name. The third part is an SQL identifier. A period must separate each of the parts.
A two-part view name is implicitly qualified by the location name of the current server. The first part is a schema name. The second part is an SQL identifier. A period must separate the two parts.
A one-part or unqualified view name is an SQL identifier with two implicit qualifiers. The first implicit qualifier is the location name of the current server. The second is a schema name, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names.
- XML-attribute-name
- An identifier that is used as an XML attribute name.
- XML-element-name
- An identifier that is used as an XML element name.