DB2 10.5 for Linux, UNIX, and Windows

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
  • 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

Read syntax diagramSkip visual syntax diagram
>>-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

Examples