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 collection-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form 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.

authorization-name

A system identifier that designates a user or group of users. An authorization name is a user profile name on the AS/400. 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.

collection-name

A qualified or unqualified name that provides a logical grouping for SQL objects. A collection name is used as a qualifier of the name of a table, view, index, procedure, or package. The unqualified form of a collection-name is a system identifier. The qualified form of a collection-name depends on the naming option.

For SQL names, the unqualified collection 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. The server-name must identify the current server.

For system names, the unqualified collection name in an SQL statement is implicitly qualified by the server-name. The qualified form is a server-name followed by a slash (/) and a system identifier. The server-name must identify the current server.

Note:

Collection-name refers to either a collection created by the CREATE COLLECTION or CREATE SCHEMA statement or to an OS/400 library.

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.

Column names cannot be qualified with system names in the form collection-name/table-name.column-name, except in the COMMENT ON and LABEL ON statements. If column names need to be qualified, and correlation names are allowed in the statement, a correlation name must be used to qualify the column.

A column-name can specify either the column name or the system column name of a column of a table or view.

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 collection-name followed by a period (.) and a system identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of Unqualified Object Names.

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.

data-type-name

A qualified or unqualified name that designates a data type. The qualified form of a data-type-name depends upon the naming option. For SQL naming, the qualified form is a collection-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of Unqualified Object Names.

descriptor-name

A colon followed by a host-identifier that designates an SQL descriptor area (SQLDA). See References to Host Variables for a description of a host identifier. A host variable that designates an SQL descriptor area must not have an indicator variable. The form :host-variable:indicator-variable is not allowed.

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 collection-name followed by a period (.) and a system identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by a system identifier.

The unqualified form is a system identifier. 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:

• An OS/400 qualified program name ('library-name/program-name').
• An OS/400 qualified source file name, followed by a left parenthesis, followed by an OS/400 member name, and a right parenthesis ('library-name/source-file-name(member-name)'). This form is only valid when calling a REXX procedure.
• An OS/400 qualified service program name, followed by a left parenthesis, followed by an OS/400 entry-point-name, followed by a right parenthesis ('library-name/service-program-name(entry-point-name)'). This form is only valid for functions.
• A class identifier, followed by an exclamation point or period, followed by a method identifier ('class-id!method-id' or 'class-id.method-id').

  The class-id identifies the class identifier of the Java object. If the class is part of a package, the class identifier must include the complete 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/ProdData/OS400/SQLLib/
       Function/myPackage/StoredProcs/'
  

  The method-id identifies the method name of the Java object to be invoked.

  This form is only valid for Java Procedures.

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. The qualified form of a function-name depends upon the naming option. For SQL naming, the qualified form is a collection-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form 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 collection-name/function-name when the name is used in a CREATE, COMMENT ON, DROP, GRANT, or REVOKE statement.

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 collection-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of Unqualified Object Names.

nodegroup-name

A qualified or unqualified name that designates a nodegroup. A nodegroup is a group of AS/400's across which a table will be distributed. For more information about distributed tables and nodegroups, see the DB2 Multisystem book.

The qualified form of a nodegroup-name depends on the naming option. For SQL naming, the qualified form is a collection-name followed by a period (.) and a system identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by a system identifier.

The unqualified form 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 collection-name followed by a period (.) and a system identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by a system identifier.

The unqualified form is a system identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of Unqualified Object Names.

parameter-name

An ordinary identifier that designates a parameter for a function or procedure. If the parameter is for a procedure, the identifier may be preceded by a colon.

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 collection-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of Unqualified Object Names.

server-name

An SQL identifier that designates an application server. The identifier must not include lowercase letters or special characters.

specific-name

A qualified or unqualified name that uniquely identifies a procedure. The qualified form of a specific-name depends upon the naming option. For SQL naming, the qualified form is a collection-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form is an SQL identifier. The unqualified form is implicitly qualified based on the rules specified in Qualification of Unqualified Object Names.

SQL-label

An unqualified name that designates a label in an SQL procedure or SQL function body. An SQL label name is an ordinary 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. It must not be a delimited identifier that includes lowercase letters or special characters. 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. It must not be a delimited identifier that includes lowercase letters or special characters. 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 OS/400 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 OS/400 name of a table, view, index, or alias. A system-object-name is a system identifier.

If the unqualified name of the table, view, index, or alias is a valid system identifier, the system-object-name of the table, view, index, or alias is the unqualified name of the table, view, index, or alias.

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 collection-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form 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.

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 collection-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a collection-name followed by a slash (/) followed by an SQL identifier.

The unqualified form 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.

Table 2. Identifier Length Limits (in bytes)

Identifier Type	Maximum Length
Alias name	128
Authorization name	10
Correlation name	128
Cursor name	18
Host identifier	64
Server name	18
SQL label	64
Statement name	18
Unqualified collection name	10
Unqualified column name	30
Unqualified constraint name	128
Unqualified data type name	128
Unqualified external program name 12	10
Unqualified function name	128
Unqualified nodegroup name	10
Unqualified package name	10
Unqualified parameter name	128
Unqualified procedure name	128
Unqualified specific name	128
Unqualified SQL parameter name	64
Unqualified SQL variable name	64
Unqualified system column name	10
Unqualified system object name	10
Unqualified table, view, and index name	128

Qualification of Unqualified Object Names

Unqualified object names are implicitly qualified. The rules for qualifying a name differ depending on the type of object that the name identifies.

Unqualified Alias, Constraint, External Program, Index, Nodegroup, Package, Table, and View Names

Unqualified alias, constraint, external program, index, nodegroup, package, table, and view names are implicitly qualified as follows:

• For static SQL statements:
  • If the DFTRDBCOL parameter is specified on the CRTSQLxxx command (or with the SET OPTION statement), the implicit qualifier is the collection-name that is specified for that parameter.
  • In all other cases, the implicit qualifier is based on the naming convention.
    • For SQL naming, the implicit qualifier is the authorization identifier of the statement.
    • For system naming, the implicit qualifier is the job library list (*LIBL).
• For dynamic SQL statements the implicit qualifier depends on whether or not a default collection name has been explicitly specified. The mechanism for explicitly specifying this depends on the interface used to dynamically prepare and execute SQL statements.
  • If a default collection name is not explicitly specified:
    • For SQL naming, the implicit qualifier is the run-time authorization identifier.
    • For system naming, the implicit qualifier is the job library list (*LIBL).
  • If a default collection name is explicitly specified, the implicit qualifier is that default collection. The default collection name can be specified through the following interfaces:
    

    Table 3. Default Collection Interfaces
    

    SQL Interface	Specification
    Embedded SQL	DFTRDBCOL parameter and DYNDFTCOL(*YES) on the CRTSQLxxx commands. The SET OPTION statement can also be used to set the DFTRDBCOL and DYNDFTCOL values. (For more information on CRTSQLxxx commands, see the SQL Programming with Host Languages book.)
    Call Level Interface (CLI) on the server	SQL_ATTR_DEFAULT_LIB environment or connection variable (For more information on CLI, see the SQL Call Level Interfaces (ODBC) book.)
    JDBC or SQLJ on the server using AS/400 Developer Kit for Java	libraries property object (For more information on JDBC and SQLJ, see the Java topic in the AS/400e Information Center, SK3T-2027-02.)
    ODBC on a client using the Client Access ODBC Driver	Default Libraries List in ODBC Setup (For more information on ODBC, see the Client Access Express category in the AS/400 Information Center.)
    JDBC on a client using the AS/400 Toolbox for Java	Default Libraries List in ODBC Setup (For more information on ODBC, see the Client Access Express category in the AS/400 Information Center.) (For more information on the AS/400 Toolbox for Java, see the Java topic in the AS/400e Information Center, SK3T-2027-02.)

Unqualified Function, Procedure, Specific, and Data Type Names

The qualification of data type (both built-in and distinct type), function, procedure, and specific names depends on the SQL statement in which the unqualified name appears:

• If an unqualified name is the main object of a CREATE, COMMENT ON, DROP, GRANT, or REVOKE statement, the name is implicitly qualified using the same rules as for qualifying unqualified table names (See Unqualified Alias, Constraint, External Program, Index, Nodegroup, Package, Table, and View Names).
• Otherwise, the implicit collection name is determined as follows:
  • For data type names, the database manager searches the SQL path and selects the first collection in the path such that the data type exists in the collection.
  • For procedure names, the database manager searches the SQL path and selects the first collection in the path such that the collection contains a procedure with the same name and number of parameters.
  • For function names and for specific names specified for sourced functions, the database manager uses the SQL path in conjunction with function resolution, as described under Function Resolution.

SQL Names and System Names: Special Considerations

The CL command Override Database File (OVRDBF) can be specified to override an SQL or system name with another object name for local data manipulation SQL statements. Overrides are ignored for data definition SQL statements and data manipulation SQL statements executing at a remote relational database. See the File Management book for more information about the override function.

¹²

For REXX procedures, the limit is 33.