Remote server connection name - how to specify it individually?

[former_SQLA_member1694875]

Hi, We have a remote server:

CREATE SERVER "main_database" CLASS 'SAODBC' USING '...' READ ONLY;

Is there any way to set remote server connection name individually for each connection?
We have only one database user (for that kind of action) with external login to that server and all connections are using that DB user. When looking in the remote database (main_database) active connection names, I see something like ASACIS_653bf763d8e84707a90a6d6bba494e34_385. Of course, I can put CON parameter into remote server connection string but then all connections will have the same name. It would be nice to be able to set remote connection name individually.

[Breck_Carter · Accepted solution]

Try using ALTER SERVER before executing the first SQL statement that needs the remote server.

Here is an end-to-end example; the ALTER SERVER ... CLOSE CONNECTION CURRENT statements may or may not be necessary in your setup to force the connection to be re-established with the new name. As you can see, the ALTER SERVER statements don't disturb existing connections.

"%SQLANY12%\\Bin32\\dbinit.exe" ddd1.db
"%SQLANY12%\\Bin32\\dbinit.exe" ddd2.db

"%SQLANY12%\\Bin32\\dbspawn.exe" -f "%SQLANY12%\\Bin32\\dbeng12.exe" ddd1.db 
"%SQLANY12%\\Bin32\\dbspawn.exe" -f "%SQLANY12%\\Bin32\\dbeng12.exe" ddd2.db

"%SQLANY12%\\Bin32\\dbisql.com" -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql;CON=ddd1"
"%SQLANY12%\\Bin32\\dbisql.com" -c "ENG=ddd2;DBN=ddd2;UID=dba;PWD=sql;CON=ddd2"

"%SQLANY12%\\Bin32\\dbisql.com" -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql;CON=user1"
"%SQLANY12%\\Bin32\\dbisql.com" -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql;CON=user2"

---------------------------------------------------------------------
-- On ddd2 (remote)

BEGIN
   DROP TABLE t
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t VALUES ( 1, 2 );
INSERT t VALUES ( 2, 2 );
COMMIT;

SELECT * FROM t ORDER BY t.pkey;

---------------------------------------------------------------------
-- On ddd1 (local) CREATE EXISTING TABLE ... AT ...

BEGIN
   DROP TABLE proxy_t
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN -- optional for SQL Anywhere
   DROP EXTERNLOGIN DBA TO ddd2_server;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER ddd2_server;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=ddd2;DBN=ddd2;';

CREATE EXTERNLOGIN DBA -- optional for SQL Anywhere
   TO ddd2_server 
   REMOTE LOGIN "DBA" IDENTIFIED BY 'sql';

CREATE EXISTING TABLE proxy_t 
   AT 'ddd2_server...t';

SELECT * FROM proxy_t ORDER BY proxy_t.pkey;

---------------------------------------------------------------------
-- On user1 (local) connect and display table.

ALTER SERVER ddd2_server CONNECTION CLOSE CURRENT;

ALTER SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=ddd2;DBN=ddd2;CON=from_user1';

SELECT * FROM proxy_t ORDER BY proxy_t.pkey;

---------------------------------------------------------------------
-- On user2 (local) connect and display table.

ALTER SERVER ddd2_server CONNECTION CLOSE CURRENT;

ALTER SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=ddd2;DBN=ddd2;CON=from_user2';

SELECT * FROM proxy_t ORDER BY proxy_t.pkey;

---------------------------------------------------------------------
-- On ddd2 (remote) display connections

SELECT * FROM sa_conn_info() ORDER BY Name;

Number,Name,Userid,DBNumber,LastReqTime,ReqType,CommLink,NodeAddr,ClientPort,ServerPort,BlockedOn,LockRowID,LockIndexID,LockTable,UncommitOps,ParentConnection
3,'ASACIS_edb83ceebed24a799f578b3ab02874ba_4','DBA',0,'2012-11-23 09:59:17.434','FETCH','local','',0,0,0,0,,'',0,
2,'ddd2','DBA',0,'2012-11-23 09:59:48.946','FETCH','local','',0,0,0,0,,'',0,
4,'from_user1','DBA',0,'2012-11-23 09:59:27.839','FETCH','local','',0,0,0,0,,'',0,
5,'from_user2','DBA',0,'2012-11-23 09:59:35.327','FETCH','local','',0,0,0,0,,'',0,

[former_SQLA_member1694875 · Accepted solution]

Thank you! This seems to be a good (and the only one?) approach.

[Former Member]

There is a second approach you can take here, but before I detail that method, I thought I would give some background as to why remote data access connections to a remote SA set the connection name at all.

Each database running on a server has a unique internal identifier (which is basically a GUID plus a little bit of extra information) and this internal identifier changes every time the database is restarted. When a remote data access connection is made to a remote SA server, the connection is always named with ASACISxxxx where the xxxx represents the internal identifier of the local SA database. When the connection is established at the remote SA server, the first thing that happens is the remote SA server identifies the connection as an incoming remote data access connection and uses the connection name to compare the internal database identifier of the originating database with the internal database identifier of the remote database being connected to. If the two internal database identifiers match, then the incoming remote connection is rejected with an appropriate error code. This is how SA prevents circular remote data access connections since allowing such connections is not only "silly" but there are also serious issues where the entire server can hang if both the local and remote connection on the same database attempt to modify the catalog. So you have to be careful when explicitly naming remote data access connections because you are opening yourself up to accidentally creating circular remote data access connections.

Having said all that, if you are okay with taking the above risk, then there is a second approach that you can employ. First, you could change the login procedure or use some other method to create a variable and set it to the connection name you eventually want on the remote connection. Then you could use variables in the USING clause to set the remote connection to the variable you created. Here is an example of what you could do. Note that this example does not actually change the login procedure but does give you an idea of what you can do to make the connections unique.

create server rem class 'saodbc' using '...;con={remote_connection_name}';

grant connect to user1 identified by user1;
create externlogin user1 to rem remote login dba identified by sql;

grant connect to user2 identified by user2;
create externlogin user2 to rem remote login dba identified by sql;

Now, start up two dbisql sessions, one with user1 as the userid and the second with user2. In each of the dbisql sessions, execute:

create variable remote_connection_name varchar(256);
set remote_connection_name = 'this remote connection is for ' || user_name();
forward to rem {select 1};

If you now run:

sa_conn_info()

on the remote database, you will see that there will be two connections one named "this remote connection is for user1" and the other named "this remote connection is for user2". Please note that variables in the using clause are new to SA 12.0.1, so you must have a recent SA 12.0.1 install to use this approach. For more information you can look at:

Create Server Statement

Also, the second example on this page gives a nice overview on how variables in the USING and AT clauses can be used:

Variable In USING and AT clause example

HTH, Karim

[VolkerBarth]

Ah, that's surely for Breck, a How-To circumvent the nasty "you-cannot-forward-to-yourself" limitation... by explicitly naming the connection...

@Karim: Yes, I have noticed your clear stop sign:)