ASA SQL User's Guide
  Accessing Remote Data

Joining tables from multiple local databases

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

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

Example 

For example, if 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 instance, on an Adaptive Server Anywhere named testasa, you might have three databases available, db1, db2, and db3.

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

• Connect to one of the databases that you will be performing joins from. For example, connect to db1.

• Perform a CREATE SERVER for each other local database you will be accessing. This sets up a loopback connection to your Adaptive Server Anywhere server.

  CREATE SERVER local_db2
  CLASS 'asaodbc'
  USING 'testasa_db2'
  CREATE SERVER local_db3
  CLASS 'asaodbc'
  USING    'testasa_db3'

  Alternatively, using JDBC:

  CREATE SERVER local_db2
  CLASS 'asajdbc'
  USING 'mypc1:2638/db2'
  CREATE SERVER local_db3
  CLASS 'asajdbc'
  USING 'mypc1:2638/db3'

• Create proxy table definitions using CREATE EXISTING to the tables in the other databases you want to access.

  CREATE EXISTING TABLE employee
  AT 'local_db2...employee'