CREATE ALIAS statement

The CREATE ALIAS statement defines an alias for a module, nickname, sequence, table, view, or another alias. Aliases are also known as synonyms.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the alias does not exist
SCHEMAADM authority on the schema if the schema name of the alias refers to an existing schema
CREATEIN privilege on the schema, if the schema name of the alias refers to an existing schema, or CREATEIN privilege on SYSPUBLIC, if a public alias is being created
DBADM authority

Privileges required to use the referenced object through its alias are identical to the privileges required to use the object directly.

To replace an existing alias, the authorization ID of the statement must be the owner of the existing alias (SQLSTATE 42501).

Syntax

table-alias

module-alias

sequence-alias

Description

OR REPLACE: Specifies to replace the definition for the alias if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog. This option is ignored if a definition for the alias does not exist at the current server. This option can be specified only by the owner of the object.
PUBLIC: Specifies that the alias is an object in the system schema SYSPUBLIC.; Names the alias. For a table alias, the name must not identify a nickname, table, view, or table alias that exists at the current server. For a module alias, the name must not identify a module or module alias that exists at the current server. For a sequence alias, the name 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' (SQLSTATE 42939) except if PUBLIC is specified, then the schema name must be SYSPUBLIC (SQLSTATE 428EK).
FOR TABLE table-name, view-name, nickname, or alias-name2: Identifies the table, view, nickname, or table alias for which alias-name is defined. If another alias name is supplied (alias-name2), then it must not be the same as the new alias-name being defined (in its fully-qualified form). The table-name cannot be a declared temporary table (SQLSTATE 42995).
FOR MODULE module-name, or alias-name2: Identifies the module or module alias for which alias-name is defined. If another alias name is supplied (alias-name2), then it must not be the same as the new alias-name being defined (in its fully-qualified form).
FOR SEQUENCE sequence-name, or alias-name2: Identifies the sequence or sequence alias for which alias-name is defined. If another alias name is supplied (alias-name2), then it must not be the same as the new alias-name being defined (in its fully-qualified form). The sequence-name must not be a sequence generated by the system for an identity column (SQLSTATE 428FB).

Notes

The keyword PUBLIC is used to create a public alias (also known as a public synonym). If the keyword PUBLIC is not used, the type of alias is a private alias (also known as a private synonym).
Public aliases can be used only in SQL statements and with the LOAD utility.
The definition of the newly created table alias is stored in SYSCAT.TABLES. The definition of the newly created module alias is stored in SYSCAT.MODULES. The definition of the newly created sequence alias is stored in SYSCAT.SEQUENCES.
An alias can be defined for an object that does not exist at the time of the definition. If it does not exist, a warning is issued (SQLSTATE 01522). However, the referenced object must exist when a SQL statement containing the alias is compiled, otherwise an error is issued (SQLSTATE 52004).
An alias can be defined to refer to another alias as part of an alias chain but this chain is subject to the same restrictions as a single alias when used in an SQL statement. An alias chain is resolved in the same way as a single alias. If an alias used in a statement in a package, an SQL routine, a trigger, the default expression for a global variable, or a view definition points to an alias chain, then a dependency is recorded for the package, SQL routine, trigger, global variable, or view on each alias in the chain. An alias cannot refer to itself in an alias chain and such a cycle is detected at alias definition time (SQLSTATE 42916).
Resolving an unqualified alias name: When resolving an unqualified name, private aliases are considered before public aliases.
Conservative binding for public aliases: If a public alias is used in a statement in a package, an SQL routine, a trigger, the default expression for a global variable, or a view definition, the public alias will continue to be used by these objects regardless of what other object with the same name is created subsequently.
Creating an alias with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of Db2® and with other database products.
- SYNONYM can be specified in place of ALIAS

Examples

Example 1: HEDGES attempts to create an alias for a table T1 (both unqualified).
```
  
   CREATE ALIAS A1 FOR T1
```
The alias HEDGES.A1 is created for HEDGES.T1.
Example 2: HEDGES attempts to create an alias for a table (both qualified).
```
   CREATE ALIAS HEDGES.A1 FOR MCKNIGHT.T1
```
The alias HEDGES.A1 is created for MCKNIGHT.T1.
Example 3: HEDGES attempts to create an alias for a table (alias in a different schema; HEDGES is not a DBADM; HEDGES does not have CREATEIN on schema MCKNIGHT).
```
   CREATE ALIAS  MCKNIGHT.A1 FOR MCKNIGHT.T1
```
This example fails (SQLSTATE 42501).
Example 4: HEDGES attempts to create an alias for an undefined table (both qualified; FUZZY.WUZZY does not exist).
```
   CREATE ALIAS HEDGES.A1 FOR FUZZY.WUZZY
```
This statement succeeds but with a warning (SQLSTATE 01522).
Example 5: HEDGES attempts to create an alias for an alias (both qualified).
```
   CREATE ALIAS HEDGES.A1 FOR MCKNIGHT.T1
   CREATE ALIAS HEDGES.A2 FOR HEDGES.A1
```
The first statement succeeds (as per example 2).

The second statement succeeds and an alias chain is created, consisting of HEDGES.A2 which refers to HEDGES.A1 which refers to MCKNIGHT.T1. Note that it does not matter whether or not HEDGES has any privileges on MCKNIGHT.T1. The alias is created regardless of the table privileges.
Example 6: Designate A1 as an alias for the nickname FUZZYBEAR.
```
   CREATE ALIAS A1 FOR FUZZYBEAR 
```
Example 7: A large organization has a finance department numbered D108 and a personnel department numbered D577. D108 keeps certain information in a table that resides at a Db2 RDBMS. D577 keeps certain records in a table that resides at an Oracle RDBMS. A DBA defines the two RDBMSs as data sources within a federated system, and gives the tables the nicknames of DEPTD108 and DEPTD577, respectively. A federated system user needs to create joins between these tables, but would like to reference them by names that are more meaningful than their alphanumeric nicknames. So the user defines FINANCE as an alias for DEPTD108 and PERSONNEL as an alias for DEPTD577.
```
   CREATE ALIAS FINANCE FOR DEPTD108
   CREATE ALIAS PERSONNEL FOR DEPTD577
```
Example 8: Create a public alias called TABS for the catalog view SYSCAT.TABLES.
```
   CREATE PUBLIC ALIAS TABS FOR SYSCAT.TABLES 
```