Naming conventions
The rules for forming a name depend on the type of the object designated by the name and the naming option (*SQL or *SYS). The naming option is specified on the CRTSQLxxx, RUNSQLSTM, and STRSQL commands. The SET OPTION statement can be used to specify the naming option within the source of a program containing embedded SQL. The syntax diagrams use different terms for different types of names.
The following list defines these terms.
- alias-name
- A
qualified or unqualified name that designates an alias. The qualified
form of an alias-name depends on the naming
option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of an alias-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
An alias-name can specify either the name of the alias or the system object name of the alias.
- array-type-name
- A
qualified or unqualified name that designates an array type. The qualified
form of a array-type-name depends upon the
naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a array-type-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
For system naming, array-type-names cannot be qualified when used in a parameter data type of an SQL routine or in an SQL variable declaration in an SQL procedure.
- authorization-name
- A system identifier that designates a user or group of users. An authorization-name is a user profile name on the server. It must not be a delimited identifier that includes lowercase letters or special characters. See Authorization IDs and authorization names for the distinction between an authorization-name and an authorization ID.
- column-name
- A
qualified or unqualified name that designates a column of a table
or a view. The unqualified form of a column-name is
an SQL identifier. The qualified form is a qualifier followed by a
period and an SQL identifier. The qualifier is a table name, a view
name, or a correlation name.
For system naming, column names can be qualified using the form schema-name/table-name.column-name when the name is used in the COMMENT and LABEL statements. If column names need to be qualified and correlation names are allowed in the statement, a correlation name can be used to qualify the column. The period form of qualification can also be used.
A column-name can specify either the column name or the system column name of a column of a table or view. If a column-name is delimited, the delimiters are considered to be part of the name when determining the length of the name.
- constraint-name
- A
qualified or unqualified name that designates a constraint on a table.
The qualified form of a constraint-name depends
on the naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a constraint-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
The implicit or explicit qualifier must be the same as the schema name of the table.
- 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.
- descriptor-name
- A variable name or string constant that designates an SQL descriptor area (SQLDA). A variable that designates an SQL descriptor area must not have an indicator variable. The form :host-variable:indicator-variable is not allowed. See References to host variables for a description of a variable.
- distinct-type-name
- A
qualified or unqualified name that designates a distinct type. The
qualified form of a distinct-type-name depends
upon the naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a distinct-type-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
For system naming, distinct-type-names cannot be qualified when used in a parameter data type of an SQL routine or in an SQL variable declaration in an SQL function, SQL procedure, or trigger.
- external-program-name
- A
qualified name, unqualified name, or a character string that designates
an external program. The qualified form of an external-program-name depends
on the naming option. For SQL naming, the qualified form is a system-schema-name followed by a period
(.) and a system identifier. For system naming, the qualified form
is a system-schema-name followed
by a slash (/) followed by a system identifier1.
The unqualified form of an external-program-name is a system identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
For a service program name, the qualified form depends on the naming option. For SQL naming the qualified form is a system-schema-name followed by a period (.), followed by a system identifier for the service program name, followed by a left parenthesis, followed by an IBM® i entry-point-name, followed by a right parenthesis (library-name.service-program-name(entry-point-name)). For system naming, the qualified form is a system-schema-name followed by a slash (/) followed by a system identifier for the service program name, followed by a left parenthesis, followed by an IBM i entry-point-name, followed by a right parenthesis (library-name/service-program-name(entry-point-name))1. If the entry point name contains lowercase characters, it must be enclosed in quotes.
The unqualified form of an service program name is a system identifier followed by a left parenthesis, followed by an IBM i entry-point-name, followed by a right parenthesis. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
The format of the character string form is either:
- A IBM i qualified program name ('library-name/program-name').
- A IBM i qualified source file name, followed by a left parenthesis, followed by an IBM i member name, and a right parenthesis ('library-name/source-file-name(member-name)'). This form is only valid when calling a REXX procedure.
- A IBM i qualified or unqualified service program name, followed by a left parenthesis, followed by an IBM i entry-point-name, followed by a right parenthesis ('library-name/service-program-name(entry-point-name)' or 'service-program-name(entry-point-name)').
- In Java, an optional jar-name,
followed by a class identifier, followed by an exclamation point or
period, followed by a method identifier ('class-id!method-id' or 'class-id.method-id').
- jar-name
- The jar-name is a case-sensitive string that identifies the jar schema when it was installed in the database. It can be either a simple identifier, or a schema qualified identifier. Examples are 'myJar' and 'myCollection.myJar'.
- class-id
-
The class-id identifies the class identifier of the Java object. If the class is part of a Java package, the class identifier must include the complete Java package prefix. For example, if the class identifier is 'myPackage.StoredProcs', the Java virtual machine will look in the following directory for the StoredProcs class:
'/QIBM/UserData/OS400/SQLLib/ Function/myPackage/StoredProcs/'
- method-id
-
The method-id identifies the method name of the public, static Java method to be invoked.
This form is only valid for Java procedures and Java functions.
- function-name
- A qualified or unqualified
name that designates a user-defined function, a cast function that was generated when a strongly typed distinct type was created, or a built-in function. The qualified
form of a function-name depends upon the naming option. For SQL naming,
the qualified form is a schema-name followed by a period (.) and an SQL
identifier. For system naming, the qualified form is a schema-name
followed by a slash (/) followed by an SQL identifier1.
In a CREATE, COMMENT, DROP, GRANT, or REVOKE statement, the schema-name can be qualified with a server-name. In all other contexts, a server-name is not allowed.
The unqualified form of a function-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
For system naming, functions names can only be qualified in the form schema-name/function-name when the name is used in a CREATE, COMMENT, DROP, GRANT, or REVOKE statement. The period form of qualification can be used in an expression.
- 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 References to host variables.
- index-name
- A
qualified or unqualified name that designates an index. The qualified
form of an index-name depends upon the naming
option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of an index-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
- mask-name
- A
qualified or unqualified name that designates a column mask. The qualified
form of a mask-name depends upon the naming
option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a mask-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
- member-name
- An unqualified identifier that designates a member of a database file. A member is also a partition of a partitioned table.
- nodegroup-name
- A qualified or unqualified
name that designates a nodegroup. A nodegroup is a group of IBM i products across which
a table will be distributed. For more information about distributed
tables and nodegroups, see DB2® Multisystem.
The qualified form of a nodegroup-name depends on the naming option. For SQL naming, the qualified form is a schema-name followed by a period (.) and a system identifier. For system naming, the qualified form is a schema-name followed by a slash (/) followed by a system identifier1.
The unqualified form of a nodegroup-name is a system identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
- package-name
- A qualified or unqualified
name that designates a package. The qualified form of a package-name depends
upon the naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and a system identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by a system identifier1.
The unqualified form of a package-name is a system identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
- parameter-name
- An SQL identifier that designates a parameter for a function or procedure. If the parameter-name is for a procedure, the identifier may be preceded by a colon.
- partition-name
- An unqualified identifier that designates a partition of a partitioned table.
- permission-name
- A
qualified or unqualified name that designates a row permission. The
qualified form of a permission-name depends
upon the naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a permission-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
- procedure-name
- A
qualified or unqualified name that designates a procedure. The qualified
form of a procedure-name depends upon the
naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a procedure-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
- savepoint-name
- An unqualified identifier that designates a savepoint.
- schema-name
- A
qualified or unqualified name that provides a logical grouping for
SQL objects. A schema name is used as a qualifier of the name of a
table, view, index, procedure, function, trigger, sequence, variable,
constraint, alias, type, or package. The unqualified form of a schema-name is
a system identifier. The qualified form of a schema-name depends
on the naming option.
For SQL naming, the unqualified schema name in an SQL statement is implicitly qualified by the server-name. The qualified form is a server-name followed by a (.) and a system identifier.
For system naming, the unqualified schema name in an SQL statement is implicitly qualified by the server-name. The qualified form is a server-name followed by a slash (/) and an SQL identifier1.
If the server-name is used to qualify the name of the schema, the server-name may identify any supported remote server. Otherwise, the schema name is implicitly qualified with the current server.
Note: schema-name refers to either a schema created by the CREATE SCHEMA statement or to an IBM i library. - sequence-name
- A
qualified or unqualified name that designates a sequence. The qualified
form of a sequence-name depends upon the
naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1. For system naming, a sequence-name cannot
be qualified with a slash when used in a NEXT VALUE or PREVIOUS VALUE
expression (the slash-qualified form is only allowed in SQL schema
statements). The period form of qualification can be used in a NEXT
VALUE or PREVIOUS VALUE expression.
The unqualified form of a sequence-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
A sequence-name can specify either the name of the sequence or the system object name of the sequence.
- 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.
A server-name may be the actual name of the relational database or a relational database alias. For more information see the Add RDB Directory Entry (ADDRDBDIRE) CL command. If a three-part name is specified directly in an SQL statement (other than the base table specified in a CREATE ALIAS statement) it can use either the actual relational database name or the relational database alias name.
For example, if the actual name of the relational database is ABC and a relational database alias name of MYABC also references ABC:SELECT * FROM ABC.SCHEMA1.T1 -- This is valid. SELECT * FROM MYABC.SCHEMA1.T1 -- This is also valid.
- specific-name
- A
qualified or unqualified name that uniquely identifies a procedure
or function. The qualified form of a specific-name depends
upon the naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a specific-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
- SQL-condition-name
- An SQL identifier that designates a condition in an SQL procedure, SQL function, or trigger body.
- SQL-descriptor-name
- A
variable name or character or graphic string constant that designates
an SQL descriptor that was allocated using the ALLOCATE DESCRIPTOR
statement.
If a variable is used to designate the SQL descriptor:
- The variable must not be a CLOB or DBCLOB.
- If the variable is a graphic string, it must be a Unicode graphic string.
- The length of the contents of the variable must not exceed the maximum length for an SQL-descriptor-name.
- An indicator variable must not be specified. The form :host-variable:indicator-variable is not allowed.
- The contents of the variable are case-sensitive and are not converted to uppercase.
Leading and trailing blanks are trimmed from the variable or string. See References to host variables for a description of a variable.
If a string constant is used to designate the SQL descriptor, the length of the constant must not exceed the maximum length for an SQL-descriptor-name.
- SQL-label
- An unqualified name that designates a label in an SQL procedure, SQL function, or trigger body. An SQL-label is an SQL identifier.
- 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 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 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.
- system-column-name
- An unqualified name that designates the IBM i column name of a table or a view. A system-column-name is a system identifier. System-column-names can be delimited identifiers, but the characters within the delimiters must not include lowercase letters or special characters.
- system-object-name
- An
unqualified name that designates the IBM i name of a table, view,
index, sequence, variable, or alias. A system-object-name is
a system identifier.
If the unqualified name of the table, view, index, sequence, variable, or alias is a valid system identifier, the system-object-name of the table, view, index, sequence, variable, or alias is the unqualified name of the table, view, index, sequence, or alias.
- system-schema-name
- An
unqualified name that designates the IBM i name of a schema.
A system-schema-name is a system identifier.
If the unqualified name of the schema is a valid system identifier, the system-schema-name of the schema is the unqualified name of the schema.
- table-name
- A
qualified or unqualified name that designates a table. The qualified
form of a table-name depends upon the naming
option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a table-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
A table-name can specify either the name of the table or the system object name of the table.
- trigger-name
- A
qualified or unqualified name that designates a trigger on a table.
The qualified form of a trigger-name depends
on the naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and a system identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a trigger-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
- variable-name
- A
qualified or unqualified name that designates a global variable. The
qualified form of a variable-name depends
upon the naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1. For system naming, a variable-name cannot
be qualified with a slash when used in an expression (the slash-qualified
form is only allowed in SQL schema statements). The period form of
qualification can be used in an expression.
The unqualified form of a variable-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
A variable-name can specify either the name of the variable or the system object name of the variable.
- version-id
- An identifier of 1 to 64 characters that is assigned to a package when the package is created. A version-id is only assigned when packages are created from a server other than Db2® for i.
- view-name
- A
qualified or unqualified name that designates a view. The qualified
form of a view-name depends upon the naming
option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of a view-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
A view-name can specify either the name of the view or the system object name of the view.
- xsrobject-name
- A
qualified or unqualified name that designates an object in the XML
schema repository. The qualified form of an xsrobject-name depends
upon the naming option. For SQL naming, the qualified form is a schema-name followed
by a period (.) and an SQL identifier. For system naming, the qualified
form is a schema-name followed by a slash
(/) followed by an SQL identifier1.
The unqualified form of an xsrobject-name is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of unqualified object names.
Identifier Type | Maximum Length |
---|---|
Longest authorization name2 | 10 |
Longest correlation name | 128 |
Longest cursor name | 128 |
Longest external program name (string form) | 279 |
Longest external program name (unqualified form)3 | 10 |
Longest host identifier | 64 |
Longest package version-id | 64 |
Longest partition name | 10 |
Longest savepoint name | 128 |
Longest schema name | 128 |
Longest server name | 18 |
Longest SQL condition name | 128 |
Longest SQL descriptor name | 128 |
Longest SQL label | 128 |
Longest statement name | 128 |
Longest unqualified alias name | 128 |
Longest unqualified array type name | 128 |
Longest unqualified column name | 128 |
Longest unqualified constraint name | 128 |
Longest unqualified distinct type name | 128 |
Longest unqualified function name | 128 |
Longest unqualified index name | 128 |
Longest unqualified mask name | 128 |
Longest unqualified nodegroup name | 10 |
Longest unqualified package name | 10 |
Longest unqualified permission name | 128 |
Longest unqualified procedure name | 128 |
Longest unqualified sequence name | 128 |
Longest unqualified specific name | 128 |
Longest unqualified SQL parameter name | 128 |
Longest unqualified SQL variable name | 128 |
Longest unqualified system column name | 10 |
Longest unqualified system object name | 10 |
Longest unqualified system schema name | 10 |
Longest unqualified table and view name | 128 |
Longest unqualified trigger name | 128 |
Longest unqualified variable name | 128 |
Longest unqualified XSR object name | 128 |