Create Synonym

Valid in: SQL, ESQL, DBProc

The Create Synonym statement defines a synonym for a table, view, or index. A synonym is an alias (alternate name) for an object.

References to synonyms in applications are resolved to their base objects at runtime. References to synonyms in definitions of database procedures, views, and permissions are resolved at the time the procedure, view, or permission is defined. For this reason, the synonym must be valid at definition time and at runtime, but can be dropped and recreated in between.

Syntax

The Create Synonym statement has the following format:

[EXEC SQL] CREATE SYNONYM synonym_name

              FOR [schema.]object

• synonym_name

  Specifies a valid object name and must not conflict with the names of other tables, views, indexes, or synonyms owned by the user issuing the statement. Synonyms can be used any place that table, view, or index identifiers are required.

Embedded Usage

You cannot use host language variables in an embedded Create Synonym statement.

Permissions

All users are permitted to use this statement.

Locking

The Create Synonym statement locks the iisynonym system catalog, and takes an exclusive lock on the table, view, or index for which a synonym is being created.

Related Statements

Drop Synonym

Examples: Create Synonym

The following are Create Synonym statement examples:

1. Create a synonym for the authors table.

   create synonym writers for authors;

2. Create a synonym for the composers table, owned by another user.

   create synonym cmp for tony.composers;