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.

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

dbsrv12 empty

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

In the new database, create a remote server named RemoteSA. Its server class is saodbc, and the connection string refers to the SQL Anywhere 12 Demo ODBC data source:

CREATE SERVER RemoteSA
CLASS 'saodbc'
USING 'SQL Anywhere 12 Demo';

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.

Sometimes 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;

Define the p_Employees proxy table:

CREATE EXISTING TABLE p_Employees
AT 'RemoteSA..GROUPO.Employees';

Define the p_Departments proxy table:

CREATE EXISTING TABLE p_Departments
AT 'RemoteSA..GROUPO.Departments';

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;