CREATE SYNONYM statement (unsupported)

Start of change The CREATE SYNONYM statement is unsupported in application compatibility level V12R1M504 and higher. Use CREATE ALIAS statements instead. End of change

Unsupported function: Start of change

FL 504 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. End of change

Invocation for CREATE SYNONYM

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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for CREATE SYNONYM

None required.

Syntax for CREATE SYNONYM

Description for CREATE SYNONYM

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 for CREATE SYNONYM

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

The owner of the synonym is determined as follows:

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.

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 for CREATE SYNONYM

Define DEPT as a synonym for the table DSN8C10.DEPT.

   CREATE SYNONYM DEPT
     FOR DSN8C10.DEPT;