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?