SQL Reference

CREATE ALIAS

The CREATE ALIAS statement defines an alias for a table, view, 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.

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---+ '-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, 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, or alias-name2

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

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.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]