SQL Reference

CREATE ALIAS

The CREATE ALIAS statement defines an alias for a table, view, nickname, or another alias.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

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

SYSADM or DBADM authority
IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the alias does not exist
CREATEIN privilege on the schema, if the schema name of the alias refers to an existing schema.

To use the referenced object via the alias, the same privileges are required on that object as would be necessary if the object itself were used.

Syntax

>>-CREATE----+-ALIAS--------+--alias-name---FOR----------------->
             |         (1)  |
             '-SYNONYM------'
 
>-----+-table-name--+------------------------------------------><
      +-view-name---+
      +-nickname----+
      '-alias-name2-'

Notes:

CREATE SYNONYM is accepted as an alternative for CREATE ALIAS for syntax toleration of existing CREATE SYNONYM statements of other SQL implementations.

Description

alias-name

Names the alias. The name must not identify a table, view, nickname, or alias that exists in the current database.

If a two-part name is specified, the schema name cannot begin with "SYS" (SQLSTATE 42939).

The rules for defining an alias name are the same as those used for defining a table name.

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

Identifies the table, view, nickname, or 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).

Notes

The definition of the newly created alias is stored in SYSCAT.TABLES.
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 view definition, a statement in a package, or a trigger points to an alias chain, then a dependency is recorded for the view, package, or trigger on each alias in the chain. Repetitive cycles in an alias chain are not allowed and are detected at alias definition time.
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.

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

[ Top of Page | Previous Page | Next Page ]