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
- 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
>>-CREATE--+------------+--+--------+----ALIAS------------------>
'-OR REPLACE-' '-PUBLIC-'
>--+-| table-alias |----+--------------------------------------><
+-| module-alias |---+
'-| sequence-alias |-'
table-alias
.-TABLE-.
|--alias-name--FOR--+-------+--+-table-name--+------------------|
+-view-name---+
+-nickname----+
'-alias-name2-'
module-alias
|--alias-name--FOR--MODULE--+-module-name-+---------------------|
'-alias-name2-'
sequence-alias
|--alias-name--FOR--SEQUENCE--+-sequence-name-+-----------------|
'-alias-name2---'
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.
- alias-name
- 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