Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Accessing Remote Data

Listing the columns on a remote table Next Page

Joining remote tables


The following figure illustrates proxy tables on a local database server mapped to the remote tables Employees and Departments of the SQL Anywhere sample database on the remote server RemoteSA mapped.

The remote tables and proxy tables are mapped.

You can use joins between tables on different SQL Anywhere databases. The following example is a simple case using just one database to illustrate the principles.

To perform a join between two remote tables (SQL)
  1. Create a new database named empty.db.

    This database holds no data. It is used only to define the remote objects, and to access the SQL Anywhere sample database.

  2. Start a database server running the empty.db. You can do this using the following command line:

    dbeng10 empty
  3. From Interactive SQL, connect to empty.db as user DBA.

  4. In the new database, create a remote server named RemoteSA. Its server class is saodbc, and the connection string refers to the DSN SQL Anywhere 10 Demo:

    CREATE SERVER RemoteSA
    CLASS 'saodbc'
    USING 'SQL Anywhere 10 Demo';
  5. In this example, you use the same user ID and password on the remote database as on the local database, so no external logins are needed.

    In some cases you must provide a user ID and password when connecting to the database at the remote server. In the new database, you could create an external login to the remote server. For simplicity in our example, the local login name and the remote user ID are both DBA:

    CREATE EXTERNLOGIN "DBA"
    TO "RemoteSA"
    REMOTE LOGIN "DBA"
    IDENTIFIED BY "sql";
  6. Define the p_Employees proxy table:

    CREATE EXISTING TABLE p_Employees
    AT 'RemoteSA..GROUPO.Employees';
  7. Define the p_Departments proxy table:

    CREATE EXISTING TABLE p_Departments
    AT 'RemoteSA..GROUPO.Departments';
  8. Use the proxy tables in the SELECT statement to perform the join.

    SELECT GivenName, Surname, DepartmentName
    FROM p_Employees JOIN p_Departments
    ON p_Employees.DepartmentID = p_Departments.DepartmentID
    ORDER BY Surname;