At times, it may be necessary for a database administrator to force resynchronization of the proxy tables contained within the proxy database.
This can be done using 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 is not 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 create database 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 resynchronization 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 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 path name differs from the default location, then the alter database command will not synchronize the metadata for this table.
To change the default location, drop the database, then re-create it with a new path name specified in the 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 may be dropped and replaced if the name conflicts with that of tables at the new location.