Example: a join between two remote tables

The following figure illustrates the remote Sybase IQ tables employee and department in the sample database mapped to the local server named testasiq.

This example shows how to:

• Define the remote testasiq 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 Sybase IQ databases. Here we describe a simple case using just one database, which may not be particularly useful, to illustrate the principles.

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 asiqdemo sample database from it.

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

   start_asiq asiqdemo 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 testasiq. Its server class is asaodbc, and the connection information is 'Sybase IQ Demo':

   CREATE SERVER testasiq 
   CLASS 'asaodbc' USING 'Sybase IQ Demo'
   

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 'testasiq..DBA.employee'
   

7. Define the department proxy table:

   CREATE EXISTING TABLE department
   AT 'testasiq..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