CREATE SYNONYM

The CREATE SYNONYM statement defines an alternative name for a table or view. This lets you refer to a table owned by another user without having to enter the fully qualified name.

You can also create synonyms for your own tables and views. The synonym remains defined until it is dropped from the database.

The following example creates a new name for the table Q.APPLICANT.

CREATE SYNONYM APPLS FOR Q.APPLICANT

After executing this statement, you can use APPLS in all commands and statements where you previously used Q.APPLICANT.

A synonym is only of value when it is shorter than the fully-qualified table name. It can also be a valuable protection for your queries if you are using tables created by someone else. For example, suppose that the Q.APPLICANT table is dropped and re-created by user BDJ1385L. All your queries were written using the synonym APPLS. Your first step is to drop the synonym by using this command:

DROP SYNONYM APPLS

Then make this change:

CREATE SYNONYM APPLS FOR BDJ1385L.APPLICANT

If you share a query that uses a synonym, it will not work for the users with whom you have shared it until those users create the same synonym. You cannot share synonyms you define under your authorization ID. However, other users can define the same synonyms with the same meanings.

If your site uses DBCS data, do not create a synonym that contains double-byte characters that are internally represented as double quotation marks unless your database specifically supports double-byte characters in table names.