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

Example: a join between two remote tables

The following figure illustrates the remote Adaptive Server Anywhere tables employee and department in the sample database mapped to the local server named testasa.

This example shows how to:

• Define the remote testasa server

• Create the proxy tables employee and department

• Perform a join between the remote employee and department tables.

In real-world cases, you may use joins between tables on different Adaptive Server Anywhere databases. Here we describe a simple case using just one database, which may not be particularly useful, to illustrate the principles.

  To perform a join between two remote tables, using Interactive SQL:

1. Create a new database named empty.db.

   This database holds no data. We will use it only to define the remote objects, and access the sample database from it.

2. Start a database server running both empty.db and the sample database. You can do this using the following command line, executed from the installation directory:

   dbeng6 asademo empty

3. Connect to empty.db it from Interactive SQL using user ID dba and password sql.

4. In the new database, create a remote server named testasa. Its server class is asajdbc, and the connection information is 'ASA 6.0 Sample':

   CREATE SERVER testasa 
   CLASS 'asaodbc' 
   USING 'ASA 6.0 Sample'

5. In this example, we use the same user ID and password on the remote database as on the local database, so no external logins are needed.

6. Define the employee proxy table:

   CREATE EXISTING TABLE employee 
   AT 'testasa..dba.employee'

7. Define the department proxy table:

   CREATE EXISTING TABLE department
   AT 'testasa..dba.department'

8. Use the proxy tables in the SELECT statement to perform the join.

   SELECT emp_fname, emp_lname, dept_name
   FROM employee JOIN department
   ON employee.dept_id = department.dept_id
   ORDER BY emp_lname