The create existing table command enables the definition of existing (proxy) tables. The syntax for this option is similar to the create table command and reads as follows:
create existing table table_name (column_list) [ on segment_name ] at “pathname”
When the server processes this command, it does not create a new table. Instead, it checks the table mapping and verifies the existence of the underlying object. If the object does not exist (either host data file or remote server object), the server rejects the command and returns an error message to the client.
After you define an existing table, it is good practice to issue an update statistics command for that table. This helps the query optimizer make intelligent choices regarding index selection and join order.
Figure A-2 illustrates the remote Adaptive Server tables publishers and titles in the sample pubs2 database mapped to a local server.
Figure A-2: Defining remote tables in a local server
The steps required to produce the mapping illustrated above are as follows:
Define a server named SYBASE. Its server class is sql_server, and its name in the interfaces file is SYBASE:
exec sp_addserver SYBASE, sql_server, SYBASE
Define a remote login alias. This step is optional. User “sa” is known to remote server SYBASE as user “sa,” password “timothy”:
exec sp_addexternlogin SYBASE, sa, sa, timothy
Add an object definition for the remote publishers table:
exec sp_addobjectdef publishers, "SYBASE.pubs2.dbo.publishers", "table"
Define the remote publishers table:
create existing table publishers ( pub_id char(4) not null, pub_name varchar(40) null, city varchar(20) null, state char(2) null ) at "SYBASE.pubs2.dbo.titles"
Define the remote titles table:
create existing table books ( title_id tid not null, title varchar(80) not null, type char(12) not null, pub_id char(4) null, price money null, advance money null, total_sales int null, notes varchar(200) null, pubdate datetime not null, contract bit not null )
Update statistics in both tables to ensure reasonable choices by the query optimizer:
update statistics publishers
update statistics books