nbkd72e
asked on
How does sybase proxy table work?
We are building a system which needs the information from two Sybase databases.
The DBA proposed the proxy table solution so that we only need to access one database and the tables from the other database will be treated the same as the local ones.
I have read some documents about Sybase proxy table and understand that only metadata and some statistics of the remote database tables are stored in the local database, not the data.
But I am not clear how the followings are supported through proxy table and what would be the caveats of using the proxy table mechanism:
1. Can we query dthe proxy table with joins between proxy tables and local tables? What would be the performance impacts?
2. Does any data block I/O go to the remote side? Is anything cached locally? Is so, how is local cache synchronized with the remote storage and cache?
3. How does locking work for the proxy tables? Does it require a distributed locking mechanism?
Thanks
The DBA proposed the proxy table solution so that we only need to access one database and the tables from the other database will be treated the same as the local ones.
I have read some documents about Sybase proxy table and understand that only metadata and some statistics of the remote database tables are stored in the local database, not the data.
But I am not clear how the followings are supported through proxy table and what would be the caveats of using the proxy table mechanism:
1. Can we query dthe proxy table with joins between proxy tables and local tables? What would be the performance impacts?
2. Does any data block I/O go to the remote side? Is anything cached locally? Is so, how is local cache synchronized with the remote storage and cache?
3. How does locking work for the proxy tables? Does it require a distributed locking mechanism?
Thanks
ASKER
Bret:
Thanks a lot for your answers, which make many things clearer.
In 2), you mentioned:
"Pages belonging to the local work table may enter the local server's data cache"
Does this mean that remote data may be cached locally? Will the cached data be directly used for future proxy table queries? Is so, how do we know that it has not been updated by the remote side?
Thanks
Thanks a lot for your answers, which make many things clearer.
In 2), you mentioned:
"Pages belonging to the local work table may enter the local server's data cache"
Does this mean that remote data may be cached locally? Will the cached data be directly used for future proxy table queries? Is so, how do we know that it has not been updated by the remote side?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>Does this mean that remote data may be cached locally? Will the cached data be directly used for future proxy table queries? Is so, how do we know that it has not been updated by the remote side?
In a sense, yes, it is. However, it is only used for the context of that one query, then discarded. Future queries will again go to the source for their data.
-bret
In a sense, yes, it is. However, it is only used for the context of that one query, then discarded. Future queries will again go to the source for their data.
-bret
Bret,
Sorry. I thought the author would have split the points.
Bill
Sorry. I thought the author would have split the points.
Bill
2) When you issue a query involving proxy tables, the local ASE opens a connection to the remote ASE and passes the part of your query involving that table (desired columns, applicable WHERE clauses) to the remote server, where the command is run and the results sent back to the local ASE, which places them in a work table and joins that with any local tables. Pages belonging to the local work table may enter the local server's data cache. Any updates to a proxy table - the update command is sent to the remote server and the table updated there. There is no need to synchronize the caches on the two servers - proxy data is not modified locally.
3) The local ASE's connection to the remote server works much like any other connection to that server. The remote server handles the locking on it's tables.