ASA SQL User's Guide
Accessing Remote Data
Internal operations
The previous steps are performed on all queries, both local and remote.
The following steps depend on the type of SQL statement and the capabilities of the remote servers involved.
Each remote server defined to Adaptive Server Anywhere has a set of capabilities associated with it. These capabilities are stored in the syscapabilities system table. These capabilities are initialized during the first connection to a remote server. The generic server class odbc relies strictly on information returned from the ODBC driver to determine these capabilities. Other server classes such as db2odbc have more detailed knowledge of the capabilities of a remote server type and use that knowledge to supplement what is returned from the driver.
Once syscapabilities is initialized for a server, the capability information is retrieved only from the system table. This allows a user to alter the known capabilities of a server.
Since a remote server may not support all of the features of a given SQL statement, Adaptive Server Anywhere must break the statement into simpler components to the point that the query can be given to the remote server. SQL features not passed off to a remote server must be evaluated by Adaptive Server Anywhere itself.
For example, a query may contain an ORDER BY statement. If a remote server cannot perform ORDER BY, the statement is sent to the remote server without it and Adaptive Server Anywhere performs the ORDER BY on the result returned, before returning the result to the user. The result is that the user can employ the full range of Adaptive Server Anywhere supported SQL without concern for the features of a particular back end.