Aliases and synonyms

A table or view can be referred to in an SQL statement by its name, by an alias that has been defined for its name, or by a synonym that has been defined for its name. Thus, aliases and synonyms can be thought of as alternative names for tables and views.

Important: Use aliases instead of synonyms. Synonyms are similar to aliases but are supported only for compatibility with previous releases. Aliases behave the same for the DB2® family of products. Recommendation: When writing new SQL statements or creating portable applications, use aliases instead. IBM has no current plans to remove support for synonyms from DB2 for z/OS®; however the use of synonyms is discouraged.

An alias can be defined at a local server and can refer to a table or view that is at the current server or a remote server. The alias name can be used wherever the table name or view name can be used to refer to the table or view in an SQL statement. The rules for forming an alias name are the same as the rules for forming a table name or a view name, as explained below. A fully qualified alias name (a three-part name) can refer to an alias at a remote server. However, the table or view identified by the alias at the remote server must exist at the remote server.

An alias name designates an alias when it is preceded by the keyword ALIAS, as in CREATE ALIAS, DROP ALIAS, COMMENT ON ALIAS, and LABEL ON ALIAS. In all other contexts, an alias name designates a table or view. For example, COMMENT ON ALIAS A specifies a comment about the alias A, whereas COMMENT ON TABLE A specifies a comment about the table or view designated by A.

A synonym designates a synonym when it is preceded by the keyword SYNONYM, as in CREATE SYNONYM and DROP SYNONYM. In all other contexts, a synonym designates a local table or view and can be used wherever the name of a table or view can be used in an SQL statement.

Statements that use three-part names and refer to distributed data result in DRDA access to the remote site. DRDA access for three-part names is used when the plan or package that contains the query to distributed data is bound with bind option DBPROTOCOL(DRDA), or the value of field DATABASE PROTOCOL on installation panel DSNTIP5 is DRDA. 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.

The option of referencing a table or view by an alias or a synonym is not explicitly shown in the syntax diagrams or mentioned in the description of SQL statements. But they can be referred to in an SQL statement, with two exceptions: a local alias cannot be used in the CREATE ALIAS statement, and a synonym cannot be used in the CREATE SYNONYM statement. If an alias is used in the CREATE SYNONYM statement, it must identify a table or view at the current server. The synonym is defined on the name of that table or view. If a synonym is used in the CREATE ALIAS statement, the alias is defined on the name of the table or view identified by the synonym.

The effect of using an alias or a synonym in an SQL statement is that of text substitution. For example, if A is an alias for table Q.T, one of the steps involved in the preparation of SELECT * FROM A is the replacement of 'A' by 'Q.T'. Likewise, if S is a synonym for Q.T, one of the steps involved in the preparation of SELECT * FROM S is the replacement of 'S' by 'Q.T'.

The differences between aliases and synonyms are as follows:

• Authorization or the CREATE ALIAS privilege is required to define an alias. No authorization is required to define a synonym.
• An alias can be defined on the name of a table or view, including tables and views that are not at the current server. A synonym can only be defined on the name of a table or view at the current server.
• An alias can be defined on an undefined name. A synonym can only be defined on the name of an existing table or view.
• Dropping a table or view has no effect on its aliases. But dropping a table or view does drop its synonyms.
• An alias is a qualified name that can be used by any authorization ID. A new alias cannot have the same fully qualified name as an existing alias, table, or view, and a new unqualified alias name cannot have the same name as an existing synonym.
• A synonym is an unqualified name that can only be used by the owner of the synonym. A new synonym cannot have the same name as an existing synonym, or the unqualified name of an existing alias, table, or view.
• An alias defined at one DB2 subsystem can be used at another DB2 subsystem. A synonym can only be used at the DB2 subsystem where it is defined.
• When an alias is used, an error occurs if the name that it designates is undefined or is the name of an alias at the current server. (The alias can represent another alias at a different server, which can represent yet another alias at yet another server as long as eventually a referenced alias represents a table or view.) When a synonym is used, this error cannot occur.
• A synonym cannot be created in a trusted context that has ROLE AS OBJECT OWNER in effect.