Join tables from multiple local databases

A SQL Anywhere server may have several local databases running at one time. By defining tables in other local SQL Anywhere databases as remote tables, you can perform cross-database joins.

For more information about specifying multiple databases, see USING parameter in the CREATE SERVER statement.

Example

Suppose you are using database db1, and you want to access data in tables in database db2. You need to set up proxy table definitions that point to the tables in database db2. For example, on a SQL Anywhere server named RemoteSA, you might have three databases available, db1, db2, and db3.

  1. If you are using ODBC, create an ODBC data source name for each database you will be accessing.

  2. Connect to one of the databases from which you will be performing. For example, connect to db1.

  3. Perform a CREATE SERVER statement for each other local database you will be accessing. This sets up a loopback connection to your SQL Anywhere server. 

    CREATE SERVER remote_db2
CLASS 'saodbc'
USING 'RemoteSA_db2';
CREATE SERVER remote_db3
CLASS 'saodbc'
USING 'RemoteSA_db3';

    Alternatively, using JDBC:

    CREATE SERVER remote_db2
CLASS 'sajdbc'
USING 'mypc1:2638/db2';
CREATE SERVER remote_db3
CLASS 'sajdbc'
USING 'mypc1:2638/db3';

  4. Create proxy table definitions by executing CREATE EXISTING TABLE statements for the tables in the other databases you want to access. 

    CREATE EXISTING TABLE Employees
AT 'remote_db2...Employees';
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1