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?
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:
Running the same statement
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:
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). asked 10 Feb '11, 12:44 Volker Barth |
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:
when pushed to DB2 will be rewritten as
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. answered 10 Feb '11, 14:38 Karim Khamis Thanks for the clarification! - I guess I'm still right that a rewriting for "comparable" functions (like len() vs. length()) does not take place? |
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.