Remote table mappings

SQL Anywhere presents tables to a client application as if all the data in the tables were stored in the database to which the application is connected. Internally, when a query involving remote tables is executed, the storage location is determined, and the remote location is accessed so that data can be retrieved.

To access data in a remote table, you must set up the following.

You must define the remote server where the remote data is located. This includes the class of server and location of the remote server. The CREATE SERVER statement is used to do this.
You must define remote server user login information if the credentials required to access the database on the remote server are different from the database to which you are connected. The CREATE EXTERNLOGIN statement is used to do this.
You must create a proxy table definition. This specifies the mapping of a local proxy table to a remote table. This includes the server where the remote table is located, the database name, owner name, table name, and column names of the remote table. The CREATE EXISTING TABLE statement is used to do this. Also, the CREATE TABLE statement can be used to create new tables at the remote server.

To manage remote server definitions, external logins, and proxy table mappings, you can use Sybase Central or you can use a tool such as Interactive SQL to execute SQL statements.

Caution

Some remote servers, such as Microsoft Access, Microsoft SQL Server, and Sybase Adaptive Server Enterprise do not preserve cursors across COMMITs and ROLLBACKs. With these remote servers, you cannot use the Data tab in Sybase Central to view or modify the contents of a proxy table. However, you can still use Interactive SQL to view and edit the data in these proxy tables as long as autocommit is turned off (this is the default behavior in Interactive SQL). Other RDBMSs, including Oracle Database, IBM DB2, and SQL Anywhere do not have this limitation.

Discuss this page in DocCommentXchange.