Creating database object aliases

An alias is an indirect method of referencing a table, nickname, or view, so that an SQL or XQuery statement can be independent of the qualified name of that table or view.

About this task

Only the alias definition must be changed if the table or view name changes. An alias can be created on another alias. An alias can be used in a view or trigger definition and in any SQL or XQuery statement, except for table check-constraint definitions, in which an existing table or view name can be referenced.

An alias can be defined for a table, view, or alias that does not exist at the time of definition. However, it must exist when the SQL or XQuery statement containing the alias is compiled.

An alias name can be used wherever an existing table name can be used, and can refer to another alias if no circular or repetitive references are made along the chain of aliases.

The alias name cannot be the same as an existing table, view, or alias, and can only refer to a table within the same database. The name of a table or view used in a CREATE TABLE or CREATE VIEW statement cannot be the same as an alias name in the same schema.

You do not require special authority to create an alias, unless the alias is in a schema other than the one owned by your current authorization ID, in which case DBADM authority is required.

When an alias, or the object to which an alias refers, is dropped, all packages dependent on the alias are marked as being not valid and all views and triggers dependent on the alias are marked inoperative.

Note: Db2® for z/OS® employs two distinct concepts of aliases: ALIAS and SYNONYM. These two concepts differ from Db2 as follows:
  • ALIASes in Db2 for z/OS:
    • Require their creator to have special authority or privilege
    • Cannot reference other aliases
  • SYNONYMs in Db2 for z/OS:
    • Can only be used by their creator
    • Are always unqualified
    • Are dropped when a referenced table is dropped
    • Do not share namespace with tables or views

Procedure

To create an alias using the command line, enter: 
    CREATE ALIAS alias_name FOR table_name
The following SQL statement creates an alias WORKERS for the EMPLOYEE table: 
    CREATE ALIAS WORKERS FOR EMPLOYEE
The alias is replaced at statement compilation time by the table or view name. If the alias or alias chain cannot be resolved to a table or view name, an error results. For example, if WORKERS is an alias for EMPLOYEE, then at compilation time:
    SELECT * FROM WORKERS
becomes in effect 
    SELECT * FROM EMPLOYEE