At times, it may be necessary for a DBA to force re-synchronization of the proxy tables contained within the proxy database. This can be done through the alter database command:
alter database <dbname> [alter database options] [for proxy_update]
If the for proxy_update clause is entered with no other options, the size of the database will not be extended; instead, the proxy tables, if any, are dropped from the proxy database and re-created from the metadata obtained from the pathname specified during create database ... with default_location = ’pathname’
If this command is used with other options to extend the size of the database, the proxy table synchronization is performed after the size extensions are made.
The purpose of this alter database extension is to provide a DBA with an easy-to-use, single step operation with which to obtain an accurate and up-to-date proxy representation of all tables at a single remote site.
This re-synchronization is supported for all external data sources, and not just the primary server in a HA-cluster environment. Also, a database need not have been created with the for proxy_update clause. If a default storage location has been specified, either through the create database command or using sp_defaultloc, the metadata contained within the database can be synchronized with the metadata at the remote storage location.
Certain behavior is implied by the use of create/alter database to specify a proxy database:
Modification to the default location specified with the create database command is not allowed using alter database.
Local tables cannot be created in the proxy database. create table commands result in the creation of proxy tables, and the actual table is created at the default location.
The default location of the table may be specified in the create table command, using the at ’pathname’ syntax. If the pathname differs from the default location, then the alter database command will not synchronize the metadata for this table.
In order to change the default location, it is necessary to first drop the database then re-create it with a new pathname specified in the with default_location = ’pathname’ clause. If the location is changed using sp_defaultloc, then the new location is used to provide metadata synchronization, and proxy tables that were created with the prior location not be synchronized, and in fact may be dropped and replaced if the name conflicts with that of tables at the new location.
