Aliases

An alias can be thought of as an alternative name for a table, partition of a table, view, or member of a database file. A table or view in an SQL statement can be referenced by its name or by an alias. Start of changeAn alias can refer to a table, partition of a table, view, or database file member within the same or a remote relational database.End of change

An alias can be used wherever a table or view name can be used, except:

Aliases can also help avoid using file overrides. Not only does an alias perform better than an override, but an alias is also a permanent object that only need be created once.

An alias can be created even though the object that the alias refers to does not exist. However, the object must exist when a statement that references the alias is executed. A warning is returned if the object does not exist when the alias is created. An alias cannot refer to another alias.

Start of changeStatements that use three-part names and refer to distributed data, result in DRDA® access to the remote relational database. When an application program uses three-part name aliases for remote objects and DRDA access, the application program must be bound at each location that is specified in the three-part names. Also, each alias needs to be defined at the local site. An alias at a remote site can refer to yet another server as long as a referenced alias eventually refers to a table or view.End of change

The option of referring to a table, partition of a table, view, or database file member by an alias name is not explicitly shown in the syntax diagrams or mentioned in the description of the SQL statements.

A new alias cannot have the same fully-qualified name as an existing table, view, index, file, or alias.

The effect of using an alias in an SQL statement is similar to that of text substitution. The alias, which must be defined before the SQL statement is executed, is replaced at statement preparation time by the qualified base table, partition of a table, view, or database file member name. For example, if PBIRD.SALES is an alias for DSPN014.DIST4_SALES_148, then at statement run time:

   SELECT * FROM PBIRD.SALES

effectively becomes

   SELECT * FROM DSPN014.DIST4_SALES_148

The effect of dropping an alias and recreating it to refer to another table depends on the statement that references the alias.

For syntax toleration of existing DB2® for z/OS® applications, SYNONYM can be used in place of ALIAS in the CREATE ALIAS and DROP ALIAS statements.