Sybase: create a proxy for a remote database

Note that all strings in bold+italic should be changed to something which is relevant for your setup.

First you have to make the remote server known locally:

sp_addserver "logical_name" , ASEnterprise, "xxx.xxx.xxx.xxx:2055"
 

You can choose anything as a logical name. This is how the remote server will be called in master..sysservers and the name used by the other commands we'll use.
If you use another port than 2055, you of course have to change it in the statement above.
"ASEntreprise" is the remote server type and means it is also an ASE server.

You can check whether the remote server has been added properly:

SELECT * FROM master.dbo.sysservers WHERE srvname = "logical_name"

Now you need to create an entry in the sysremotelogins table for the remote server. This is done with the stored procedure sp_addremotelogin.

If you have the same users on both server, you can just execute the following to map remote names to local names:

sp_addremotelogin logical_name

If you want to map all logins from the remote server to a local user name:

sp_addremotelogin logical_name, local_user_name

If you only want to map a single remote login from a remote user on the remote server to a local use:

sp_addremotelogin logical_name, local_user_name, remote_user_name

Now we can create the proxy database:

create database proxy_db_name on default = 200M with default_location = 'logical_name.remote_db_name..' for proxy_update
 

If you do not want to create it on default but on a new device, you'll need to first create the device.

disk init name="proxy_dev", physname="/var/sybase/ASE/proxy_dev.dat", size="200M"

Of course the parent directory of the file which path is set in physname should exist and the appropriate rights should be set.
(you can also added the parameter directio=true to this command if required)

And then create the database:

create database proxy_db_name on proxy_dev = 200M with default_location = 'logical_name.remote_db_name..' for proxy_update

Now you can use the proxy database to access data of all tables in the remote database:

select name from proxy_db_name..sysobjects

The data are still residing on the remote server so you do not need to do anything when data change. But if the structure changes (i.e. if you add or remove tables or update the structure of a table), you need to update the proxy:

alter database proxy_db_name for proxy_update