CREATE ALIAS statement

The CREATE ALIAS statement defines an alias for a table, a view, or a sequence. The definition is recorded in the Db2 catalog at the current server.

Invocation for CREATE ALIAS

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for CREATE ALIAS

To create an alias, the privilege set must include at least one of the listed authorities or privileges:

To create an alias for a table or a view:

For a table or a view:
- The CREATEALIAS privilege
- SYSADM or SYSCTRL authority
- System DBADM authority
- Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
For a table only:
- DBADM or DBCTRL authority on the database that contains the table, if the value of field DBADM CREATE AUTH on installation panel DSNTIPP is YES

To create an alias for a sequence:

The CREATEIN privilege on the schema
SYSADM or SYSCTRL authority
System DBADM authority
Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

Privilege set:

If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the owner of the plan or package is a role, this role must hold the privileges for the privilege set. If the specified alias name includes a qualifier that is not the same as this authorization ID, the privilege set must include one of the following authorities:

SYSADM or SYSCTRL authority
System DBADM authority
DBADM or DBCTRL authority on the database that contains the table, if the alias is for a table and the value of field DBADM CREATE AUTH on installation panel DSNTIPP is YES

If ROLE AS OBJECT OWNER is in effect, the schema qualifier must be the same as the role, unless the role has the CREATEIN privilege on the schema, SYSADM authority, SYSCTRL authority, or system DBADM authority.

If ROLE AS OBJECT OWNER is not in effect, one of the following rules applies:

If the privilege set lacks the CREATEIN privilege on the schema, SYSADM authority, SYSCTRL authority, or system DBADM authority, the schema qualifier (implicit or explicit) must be the same as one of the authorization ids of the process.
If the privilege set includes SYSADM authority, SYSCTRL authority, or system DBADM authority, the schema qualifier can be any valid schema name.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process unless the process is within a trusted context and the ROLE AS OBJECT OWNER clause is specified. If the process is not running in a trusted context that is defined with the ROLE AS OBJECT OWNER clause and the specified alias name includes a qualifier that is not the same as this authorization ID:

The privilege set must include SYSADM, SYSCTRL, or system DBADM authority.
The privilege set must include DBADM or DBCTRL authority on the database that contains the table, if the alias is for a table and the value of field DBADM CREATE AUTH on installation panel DSNTIPP is YES.
The qualifier must be the same as one of the authorization IDs of the process and the privileges that are held by that authorization ID must include the CREATEALIAS privilege. This is an exception to the rule that the privilege set is the privileges that are held by the SQL authorization ID of the process.

Syntax for CREATE ALIAS

table-alias

Notes:

¹ If alias-name2 is specified, it must not resolve to the fully-qualified form of alias-name, and alias-name2 must not be an alias that exists at the current server.

sequence-alias

Description for CREATE ALIAS

PUBLIC

Specifies that the alias is an object in the system schema SYSPUBLIC. Start of change

PUBLIC can be specified only for a sequence. End of change

The PUBLIC keyword is used to create a public alias. If the keyword PUBLIC is not specified, the alias that is created is a private alias.

alias-name

Names the alias.

For a table alias, the name, including the implicit or explicit qualifier, must not identify a table, view, or table alias that exists at the current server, or a table that exists in the SYSIBM.SYSPENDINGOBJECTS catalog table.

For a sequence alias, the name, including the implicit or explicit qualifier, must not identify a sequence or sequence alias that exists at the current server.

If a two-part name is specified, the schema name cannot begin with 'SYS', except if PUBLIC is specified, in which case the schema name must be SYSPUBLIC. The unqualified name must not be the same as an existing synonym.

If the name is qualified, the name can be a two-part or three-part name. If a three-part name is used, the first part must match the value of the field Db2 LOCATION NAME on installation panel DSNTIPR at the current server. (If the current server is not the local Db2, this name is not necessarily the name in the CURRENT SERVER special register.)

When an application uses three-part name aliases for remote objects and DRDA access, the application program must be bound at each location that is specified in the three-part names.

FOR TABLE table-name, view-name, or alias-name2

Identifies the table, view, or alias for which alias-name is defined. The table, view or alias need not exist at the time the alias is defined. If it does not exist when the alias is created, a warning is returned. However, the referenced object must exist when a SQL statement that contains the alias is used, otherwise an error is returned. If the table or view does exist, the referenced object can be at the current server or at another server.

If alias-name2 is specified it must be a three-part name. The first part of the three-part name must be the location name for a remote server. If the alias exists, the alias must exist at the remote server identified by the location name.

If a table is identified, it must not be an auxiliary table, a declared temporary table, or a table that is implicitly created for an XML column.

If alias-name2 is specified, it must not resolve to the fully-qualified form of alias-name, and alias-name2 must not be an alias that exists at the current server.

FOR SEQUENCE sequence-name

Identifies the sequence for which alias-name is defined. The sequence-name must not be a sequence that is generated by the Db2 subsystem for an identity column or a DOCID column. The schema name must not begin with 'SYS' unless the schema name is 'SYSADM'. sequence-name must not be an existing alias for a sequence.

The sequence need not exist at the time the alias is defined. If sequence-name does not exist when the alias is created, a warning is returned. However, the referenced object must exist when a SQL statement that contains the alias is used, otherwise an error is returned.

Notes for CREATE ALIAS

Owner privileges:: There are no specific privileges on an alias. For more information about ownership of an object, see Authorization, privileges, permissions, masks, and object ownership.
PUBLIC aliases:: If the PUBLIC keyword is specified or if SYSPUBLIC is explicitly specified as the schema qualifier for alias-name, a public alias is created.
Resolving an unqualified name:: When an unqualified name is resolved, private aliases are considered before public aliases.

Examples for CREATE ALIAS

Example 1

Create an alias for a catalog table at a Db2 with location name DB2USCALABOA5281.

   CREATE ALIAS LATABLES FOR DB2USCALABOA5281.SYSIBM.SYSTABLES;

Example 2

Create a public alias called SEQS for a sequence named JOE.JOESSEQ.

	CREATE PUBLIC ALIAS SEQS FOR SEQUENCE JOE.JOESSEQ;

The alias can be referenced as SYSPUBLIC.SEQS, or simply as SEQS if a private sequence or alias named SEQS does not exist.