Are these assumptions about remote data access and query rewriting correct?

[VolkerBarth]

Based on current own work with proxy tables and Adam's series of questions, I'd like to check my understanding of the way remote data access does work.

Are the following four assumptions correct?

1. Each statement run against a proxy table must be a valid SQL statement for the local SQL Anywhere database server; each referenced column, procedure or function must exist in the current database.

2. The server capabilities are responsible what part of a SQL statement can be sent to the remote server or has to be processed locally; i.e. they have influence whether full or partial passthrough mode can be used.

3. Except the mentioned splitting into partial statements, there is no "rewriting" of statements to make them compatible with the remote server, i.e. if a remote server does support a particular SQL feature but does use a different syntax (or a different name for a semantically equal feature) then the remode data access layer won't rewrite the statement to be sent to the remote server with that syntax. (As a consequence, one won't be able to use such a feature.)

4. In contrast, when using FORWARD TO, the statement has only to be compatible and valid for the remote server.

Question:

Are these assumptions correct?

---

Some samples used:

I created a readonly MS SQL 2000 remote server (named MS_SVR) and tested with ASA 8.0.3 and SA 11.0.1 and the MS SQL system table sysobjects (as proxy table named MS_sysobjects).

Running the following statement in FORWARD TO mode succeeds both with ASA 8 and SA 11 as the syntax is valid on MS SQL:

forward to MS_SVR { select name, unicode(name) from sysobjects }

Running the same statement

select name, unicode(name) from MS_SysObjects

against the ASA 8.0.3 proxy table fails, as unicode is not a known ASA 8 function. However, running against SA 11.0.1 succeeds as that does support the unicode function. Note, however, that there's no capability for such a function, and therefore the statement is not processed in full passthrough mode:

The query is being processed in NO PASSTHRU mode
The capability check of Unknown builtin function failed
The Remote Statement for vt_1 is 
    select MS_SysObjects.name from MS_SysObjects

An example for the "rewriting" mentioned in Assumption 3 would be the syntax differences between "SELECT TOP" in SA and "SELECT ... LIMIT" in MySQL or function "length" in ASA 8.0.3 and "len" in MS-SQL (SA 11.0.1 seems to support "len", too, but will process it locally with the default MS SQL capabilities).

[VolkerBarth]

The 1. assumption seems to be stated in a similar wy in the docs, cf. http://dcx.sybase.com/index.html#1200en/dbusage/ug-accessrd-sectb-5161698.html.

[Former Member · Accepted solution]

1) yes, the SQL executed on the proxy tables must be SA "syntax"

2) yes, SA will by default set up the remote server capabilities as best as possible based on known limitation of the remote server (for the lowest supported version) and additional information gleaned from the ODBC driver. The user is then responsible for tweaking the capabilities to better match his/her version of the remote server (if necessary).

3) no, SA will attempt to perform some rewriting if need be. For example:

SELECT TOP n c FROM T

when pushed to DB2 will be rewritten as

SELECT c FROM T FETCH FIRST n ROWS ONLY

However not all nuances of the remote are handled by the remote data access layer, so some rewriting will not get done. We do our best though.

4) yes, when using forward to, the query/statement being forwarded is sent verbatim to the remote and not parsed by SA. As a result, the query/statement being forwarded must be understandable by the remote.

[VolkerBarth · Accepted solution]

Thanks for the clarification! - I guess I'm still right that a rewriting for "comparable" functions (like len() vs. length()) does not take place?