CREATE SYNONYM (deprecated)

The CREATE SYNONYM statement defines a synonym for a table or view at the current server.

Deprecated function: Start of changeSynonyms are deprecated. Start of changeIn Db2 12, packages bound with APPLCOMPAT(V12R1M504 or higher cannot issue CREATE synonym statements. Use aliases instead.End of changeSynonyms are similar to aliases but are supported only for compatibility with previous releases Aliases behave the same for all Db2 family products. For more about aliases, see Aliases and CREATE ALIAS. Existing synonyms remain supported, but support might be removed in the future.End of change

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified. The statement cannot be processed in a trusted context that is defined with a role as the object owner.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagram CREATE SYNONYM synonym FOR authorization-name.table-nameview-name

Description

synonym
Names the synonym. The name must not identify a synonym, table, view, or alias that exists at the current server and that is owned by the owner of the synonym that is being created and must not identify a table that exists in the SYSIBM.SYSPENDINGOBJECTS catalog table. The unqualified name must not be the same as an existing synonym.
FOR authorization-name.table-name or authorization-name.view-name
Identifies the object to which the synonym applies. The name must consist of two parts and must identify a table, view, or alias that exists at the current server. If a table is identified, it must not be an auxiliary table or a declared temporary table. If an alias is identified, it must be an alias for a table or view at the current server and the synonym is defined for that table or view. The name must not identify a table that was implicitly created for an XML column, or an accelerator-only table.

Notes

Owner privileges: There are no specific privileges on a synonym.

Start of changeThe owner of the synonym is determined as follows:End of change

Start of change
  • If the CURRENT SCHEMA special register contains a value that is not the same as the CURRENT SQLID special register, the owner of the synonym is the value of the CURRENT SCHEMA special register.
  • Otherwise, the owner of the schema is the value of the CURRENT SQLID special register.
End of change

For more information about ownership of an object, see Authorization, privileges, permissions, masks, and object ownership.

If an alias is used to denote the table or view, the name of that table or view, not the alias, is recorded in the catalog as the definition of the synonym. That severs the connection between the synonym and alias, and even if the alias is dropped and redefined, the synonym is still in effect and names the original table or view.

Example

Define DEPT as a synonym for the table DSN8B10.DEPT.
   CREATE SYNONYM DEPT
     FOR DSN8B10.DEPT;