Chaining Synonyms

If you create a synonym for a table or view that is not in the current database, and this table or view is dropped, the synonym stays in place. You can create a new synonym for the dropped table or view with the name of the dropped table or view as the synonym, which points to another external or remote table or view. (Synonyms for external sequence objects are not supported.)

In this way, you can move a table or view to a new location and chain synonyms together so that the original synonyms remain valid. (You can chain up to 16 synonyms in this manner.)

The following steps chain two synonyms together for the customer table, which will ultimately reside on the zoo database server (the CREATE TABLE statements are not complete):

1. In the stores_demo database on the database server that is called training, issue the following statement:

   CREATE TABLE customer (lname CHAR(15)...)

2. On the database server called accntg, issue the following statement:

   CREATE SYNONYM cust FOR stores_demo@training:customer

3. On the database server called zoo, issue the following statement:

   CREATE TABLE customer (lname CHAR(15)...)

4. On the database server called training, issue the following statement:

   DROP TABLE customer
   CREATE SYNONYM customer FOR stores_demo@zoo:customer

The synonym cust on the accntg database server now points to the customer table on the zoo database server.

The following steps show an example of chaining two synonyms together and changing the table to which a synonym points:

1. On the database server called training, issue the following statement:

   CREATE TABLE customer (lname CHAR(15)...)

2. On the database server called accntg, issue the following statement:

   CREATE SYNONYM cust FOR stores_demo@training:customer

3. On the database server called training, issue the following statement:

   DROP TABLE customer
   CREATE TABLE customer (lastname CHAR(20)...)

The synonym cust on the accntg database server now points to a new version of the customer table on the training database server.

Related Information