CREATE SYNONYM

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

Important: Start of changeUse 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. 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 diagram
>>-CREATE SYNONYM--synonym-------------------------------------->

>--FOR--authorization-name.-+-table-name-+---------------------><
                            '-view-name--'   

Description

synonym
Start of changeNames 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.End of change
FOR authorization-name.table-name or authorization-name.view-name
Start of changeIdentifies 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.End of change

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.

Start of changeIf 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.End of change

Example

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