User proxy databases

When a user proxy database is created, metadata for the proxy tables is imported automatically from the remote location which contains the actual tables. This metadata is then used to create proxy tables within the proxy database.

Proxy database creation is done through syntax which extends the create database command:

	create database <dbname>
		[create database options]
		[with default_location = ’pathname’]
		[for proxy_update]] 

The use of the clause with default_location allows the database creator to specify the storage location of any new tables, and the location from which metadata may be imported for automatic proxy table creation if the for proxy_update clause is also specified. The for proxy_update clause establishes the database as a proxy database; the with default_location clause defines the location from which proxy tables are imported. Without the for proxy_update clause, the behavior of the with default_location clause is the same as that provided by the stored procedure sp_defaultloc - a default storage location is established for new and existing table creation, but automatic import of proxy table definitions is not done during the processing of the create database command.

The value of pathname is a string identifier in the following format: servername.dbname.owner.

Note: the dots are significant, and all three must be present! Each field in this string is described as follows:

If for proxy_update is specified with no default_location, an error is reported.

When a proxy database is created (using the for proxy_update option), CIS functions are called upon to:

After the database has been created, it contains a proxy table for each table or view found in the default_location. Then the behavior for a user proxy database, is identical to prior database behavior. Users can create additional objects, such as procedure, views, rules, defaults, etc., and both DDL and DML statements that operate on proxy tables behave as documented in the Component Integration Services User’s Guide.

The only exception to this is the alter database command. New syntax and capabilities of this command are described in the next section.