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:
servername - required field; represents the name of the server that owns the objects to be referenced by proxy tables. Must exist in master.dbo.sysservers.srvname.
dbname - may be omitted. The name of the database within servername which contains objects to be referenced by proxy tables
owner - may be omitted. The name of the owner of objects to be referenced by proxy tables. This may be restrictive, so that if more than one user owns objects in dbname, specifying the owner will select only those objects owned by that user. Proxy tables must not be created for objects owned by other users.
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:
Provide an estimate of the database size required to contain all proxy tables representing the actual tables/views found in the primary server’s database. This estimate is provided in terms of the number of database pages needed to contain all proxy tables and indexes. This size is used if no size is specified, and no database devices are specified.
Note: if the database is created with specific size specifications [on device_name = nn], or if a device name is specified with no size [on device_name], then the size requirements for the proxy database are not estimated; it is assumed in this case that the user or dba wants to override the default size calculated for the proxy database.
If you are importing metadata from another Adaptive Server, remote database users are imported before proxy tables are created. Each imported database user must have a corresponding system user name in syslogins.
Create all proxy tables representing the actual tables/views found in the companion server’s database. Proxy tables are not created for system tables.
Note: Before the proxy tables are created, the quoted identifier state is turned on, and each table is created with quotes surrounding the table name and column name. This allows the creation of tables containing names that may be Sybase Transact-SQL reserved words. When all proxy tables are created, the quoted identifier state is restored to its original setting.
grant all permissions on proxy tables to public
add the guest user to the proxy database
import database users from remote site (if Adaptive Server Enterprise)
grant create table permission to public
The database status is set to indicate that this database is a user proxy database. This is done by setting a status field in master.dbo.sysdatabases.status3 (0x0001, DBT3_USER_PROXYDB).
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.
