Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009

    Red face Unanswered: Dblink or proxy tables

    Hi All,
    My environment
    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

  2. #2
    Join Date
    Mar 2007

    Proxy tables (or proxy db)

    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)

    use master
    -- 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
    on vol_data=nn
    log on vol_log=nn
    with default_location = '<remote_server name>.dbname.dbo'
    for proxy_update

    -- be sure sybsystemdb is sized to handle rpc requests --

  3. #3
    Join Date
    Mar 2009
    Sydney, Australia
    That's correct. But it is not the whole picture.

    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.)
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

  4. #4
    Join Date
    Mar 2007

    proxy tables

    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts