a. I have two servers m1 and m2 where Sybase database is installed.
b. I have stored procedure on m1.
c. From that stored procedure I have to fetch table data from m2 server.
d. I found two option found on net, 1. use of proxy tables 2. Db link.
but when i use db link it do not allow me to access database tables from m1 server.
and I can not affort creating proxy table as data is in millions.
Please help me
proxies are metadata .. not data, so the millions of rows are not an issue. I use CIS all the time for a lot of things (including archiving / purging data to-from remote server). Just keep the statistics up to date on the proxy .. I run update stats at 6-hour intervals, but depends on how much your data is changed.
I recommend this method.. as it is cheap and only dependant on your network bandwidth.
Read up on CIS implementation for the version your're deploying on. ASE 15 has a few really nice features not available in previous versions ('create prxydb using for proxy update' for instance)
-- Configure CIS
exec sp_configure "enable cis",1
exec sp_configure "cis rpc handling",1
exec sp_configure 'cis packet size',2048
- network must be configured for varlen packets
exec sp_configure 'cis cursor rows',2000
-- setup ASEnterprise class server mapped to remote server
exec sp_addserver <remote server>
-- for sa login
exec sp_addexternlogin <remote_server name>,sa,sa,<remote server sa password)
exec sp_serveroption ,<remote server name,'net password encryption',true
-- to test connectivity issue connect to cmd --
connect to <server name>
disconnect from <server name>
-- on remote server --
-- create proxy database --
CREATE DATABASE <dbname>_proxy
log on vol_log=nn
with default_location = '<remote_server name>.dbname.dbo'
-- be sure sybsystemdb is sized to handle rpc requests --
CIS, any service you use on a computer system is not free. As you have pointed out, it needs to be set up, sized correctly, administered (thresholds, dumps). Millions of rows are "not a problem" but the number of services invoked to move the data from one server to the other, while not quite visible, cannot be ignored.
Just look at the mess they made with MDA tables (which use CIS in loopback mode). And the overhead (published as 5 to 22%, depending on what you enable.)
CIS is a free service, but as this poster articulated, it has it's limitations. As for overhead, that goes without saying as there is always a cost. CIS is magnitutdes better at the ASE 15.x release than prior versions. I use it for specific applications (non SOA architectures), and in a virtualized (cpu pooling) AIX / ASE 15.0.3 infra with a dedicated network (the latter minimizes collisions). It performs well for my needs. I limit the cursor rows to 2000, so remote deletes are not intrusive. For an OLTP system I would not rely on it for the best performance. It would depend on your SLA, and benchmarks. For dedicated data at high volume SL or brokerage desks we always use repserver (with the associated cost in $$ and resources).