Step 4: Map the Remote Tables to Adaptive Server

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.

Example

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

Mapping the Remote Tables

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
```