This statement can be embedded in an application program or issued interactively.
No special authorization is required. However, you must possess the appropriate privileges on the object to which the synonym refers before you can use the synonym. If dynamically prepared, the synonym is owned by the CURRENT SQLID.
In the case of users with SYSADM authority, ownership can be assigned to other users by first changing the CURRENT SQLID special register value, and then issuing the CREATE SYNONYM statement. The originally recorded AuthID for a synonym is the only AuthID for which the synonym is defined, and the only AuthID that can drop the synonym.
CREATE SYNONYM synonym
FOR authorization-name.{table-name | view-name}
| synonym | Specifies the alternate name you want to define. The synonym may be up to 18 characters long, and must meet all other criteria for a table or view name. The synonym must be unique among the set of all table names, view names and synonyms in the current location. |
| authorization-name.{table-name or view-name} | Identifies an existing table or view (with associated AuthID) to which you intend to assign a synonym. 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 example below assigns the alternate name syscolumns for the system table SYSIBM.SYSCOLUMNS:
CREATE SYNONYM syscolumns FOR SYSIBM.SYSCOLUMNS
Once this synonym is created, the query:
SELECT * FROM syscolumns
returns the same result as:
SELECT * FROM SYSIBM.SYSCOLUMNS
Description
Synonyms can be used to assign alternate names to existing tables or views at the current location. Synonyms may be used interchangeably with their corresponding view or table names in SQL commands. If using a synonym name when granting or revoking privileges or creating indexes, the actual table or view name is substituted for the synonym name before the command is executed.
If a CREATE SYNONYM command denotes an alias name instead of an actual table or view, that table or view (not the named alias) is recorded in the system catalog as the object of the synonym. In these cases the connection between the synonym and alias is severed. If the original alias name is later dropped and redefined, the synonym naming the original table or view is still in effect.