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.

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

CREATE SYNONYM APPLS FOR Q.APPLICANT

After executing this statement, you can write APPLS instead of Q.APPLICANT.

A synonym is only of value when it is shorter than the fully qualified table name (which can be up to 26 characters, not counting the intervening period). It can also be a valuable protection for your queries if you are using tables created by someone else.

For example, suppose that table Q.APPLICANT is dropped and re-created by user BDJ1385L. All your queries were written using the synonym APPLS. If you use SQL/DS, your first step is to drop the synonym by using this command:

DROP SYNONYM APPLS

If you use SQL/DS or DB2, make this change:

CREATE SYNONYM APPLS FOR BDJ1385L.APPLICANT

If you share a query that uses a synonym, it will not work for the other user until that user creates the same synonym. You cannot share synonyms you define under your authorization identifier. However, other users can define the same synonyms with the same meanings.