Working with remote servers

User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 28. Accessing Remote Data

Working with remote servers

Before you can map remote objects to a local proxy table, you must define the remote server where the remote object is located. When you define a remote server, an entry is added to the sysservers table for the remote server. This section describes how to create, alter, and delete a remote server definition.

Creating remote servers

Use the CREATE SERVER statement to set up remote server definitions. You can execute the statements directly, or use Sybase Central (Java Edition).

For ODBC connections, each remote server corresponds to an ODBC data source. For some systems, including Adaptive Server Anywhere, each data source describes a database, so a separate remote server definition is needed for each database.

Example 1

The following statement creates an entry in the sysservers table for the Adaptive Server Enterprise named ASEserver:

CREATE SERVER ASEserver 
CLASS 'asejdbc' 
USING 'rimu:6666'

where:

ASEserver is the name of the remote server
asejdbc specifies the server is an Adaptive Server Enterprise and the connection to it is JDBC-based
rimu:6666 is the machine name and the TCP/IP port number where the remote server is located

Example 2

The following statement creates an entry in the sysservers table for the ODBC-based Adaptive Server Anywhere named testasa:

CREATE SERVER testasa 
CLASS 'asaodbc' 
USING 'test4'

where:

testasa is the name by which the remote server is known within this database.
asaodbc specifies that the server is an Adaptive Server Anywhere and the connection to it uses ODBC.
test4 is the ODBC data source name.

For Info For a full description of the CREATE SERVER statement, see CREATE SERVER statement.

Creating remote servers using Sybase Central

To create a remote server using Sybase Central (Java):

Connect to the host database from Sybase Central (Java) using a JDBC connection.
In the left panel, open the Remote Server folder. Then double-click Add Remote Server on the right panel.
On the first page of the Wizard, enter a name to use for the remote server. This is the name you use to refer to the remote server will be from within the local database, and need not correspond to the server name the server supplies. Then click Next.
On the next page, select an appropriate class for the server and click Next.
Select a data access method (JDBC or ODBC) and supply connection information:
- For JDBC, supply a URL in the form machine-name:port-number
- For ODBC, supply a data source name.
Click Finish to create the remote server definition.

Notes

The data access method (JDBC or ODBC) is the method used by Adaptive Server Anywhere to access the remote database. This is different from that used by Sybase Central to connect to your database.

Deleting remote servers

Use the DROP SERVER statement to drop a remote server from the Adaptive Server Anywhere system tables. All remote tables defined on that server must already be dropped for this statement to succeed. You can execute the statements directly, or use Sybase Central (Java Edition).

Example

The following statement drops the server named testasa:

DROP SERVER testasa

For Info For a full description of the DROP SERVER statement, see DROP SERVER statement.

Deleting remote servers using Sybase Central

To delete a remote server using Sybase Central (Java):

Connect to the host database from Sybase Central (Java) using a JDBC connection.
In the left panel, open the Remote Server folder. Then right-click the remote server on the right panel.
Select Delete from the popup menu.

Altering remote servers

Use the ALTER SERVER statement to modify the attributes of a server. These changes do not take effect until the next connection to the remote server. You can execute the statements directly, or use Sybase Central (Java Edition).

Example

The following statement changes the server class of the server named ASEserver to aseodbc:

ALTER SERVER ASEserver 
CLASS 'aseodbc'

The Data Source Name for the server is ASEserver.

The ALTER SERVER statement can also be used to enable or disable a server's known capabilities.

For Info For a complete description of the ALTER SERVER statement, see ALTER SERVER statement.

Altering remote servers using Sybase Central

To alter the properties of a remote server using Sybase Central (Java):

Connect to the host database from Sybase Central (Java) using a JDBC connection.
In the left panel, open the Remote Server folder. Then right-click the remote server on the right panel.
Select Properties from the popup menu and make the changes you need in the server property sheet.

Listing the remote tables on a server

It may be helpful when you are configuring your Adaptive Server Anywhere to get a list of the remote tables available on a particular server. The sp_remote_tables procedure returns a list of the tables on a server.

sp_remote_tables servername 
                 [,tablename]
                 [, owner ]
                 [, database]

If tablename, owner, or database is given, the list of tables is limited to only those that match.

For example, to get a list of all of the Microsoft Excel worksheets available from an ODBC data source named excel:

sp_remote_tables  excel

Or to get a list of all of the tables in the production database in an ASE named asetest, owned by 'fred':

sp_remote_tables  asetest, null, fred, production

For Info For more information, see sp_remote_tables system procedure.

Listing remote server capabilities

The sp_servercaps procedure displays information about a remote server's capabilities. Adaptive Server Anywhere uses this capability information to determine how much of a SQL statement can be passed of to a remote server.

The system tables which contain server capabilities are not populated until after Adaptive Server Anywhere first connects to the remote server. This information comes from the SYSCAPABILITY and SYSCAPABILITYNAME system tables. The servername specifed must be the same servername used in the CREATE SERVER statement.

Issue the stored sp_servercaps as follows:

sp_servercaps  servername

For Info For more information, see sp_servercaps system procedure.