Synonyms (deprecated)

A synonym is an alternate name for a table or view. A synonym can be used to reference a table or view in cases where an existing table or view can be referenced. However, Db2 no longer supports the creation of new synonyms.

Unsupported function: Beginning in Db2 12, packages bound with APPLCOMPAT(V12R1M504) or higher cannot issue CREATE SYNONYM statements. Although there are some differences, you can use aliases instead. Unlike synonyms, aliases behave the same for all Db2 family products. For more information about aliases, see Aliases and CREATE ALIAS statement. Existing synonyms remain supported, but support might be removed in the future.

Differences between synonyms and aliases

The following table summarizes the differences between aliases and synonyms, which are deprecated.

Table 1. Differences between synonyms and aliases
Characteristic	Synonyms (deprecated)	Aliases
Can be created in application compatibility V12R1M504 and higher?	No	Yes
Requires authorization to create?	No	Yes
Can be defined on objects not at the current sever?	No	Yes
Can be defined on the name of an object that does not yet exist?	No	Yes, but the object must exist when the alias is used
Is dropped when referenced objects are dropped?	Yes	No
Uses a qualified object name for the object?	No, uses a one-part name	Yes
Can be referenced or used by users other than the object owner?	No	Yes

The option of referencing a table or view by a synonym is not explicitly shown in the syntax diagrams or mentioned in the description of SQL statements. But synonyms can be referred to in an SQL statement.

Like tables and views, a synonym can be dropped, and associated with a comment. No authority is necessary to use a synonym. However, access to the tables and views that are referenced by the synonym still requires the appropriate authorization for the current statement.

A synonym name designates a synonym when it is preceded by the keyword SYNONYM, as in DROP SYNONYM. In all other contexts, a synonym designates a table or a view. 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.

The effect of using a synonym in an SQL statement is the same as text substitution. For example, 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'.