p_drop_other_connections Revisited

You may have seen a little stored procedure in the newsgroups or out on the web called "p_drop_other_connections" which issues DROP CONNECTION commands for every connection except the one that called the procedure. It's useful when you want to grab complete control of a running SQL Anywhere server and do something important, like ALTER TABLE or CREATE INDEX on the Table From Hell that everyone's using:

/* How to get full control over a SQL Anywhere server...

(1) CALL sa_server_option ( 'ConnsDisabled', 'ON' );

    At this point, any attempt to open a new connection will
    get "Connections to database have been disabled". 
    However, current connections will still work until...

(2) CALL p_drop_other_connections(); 

(3) Do whatever you need to do.

(4) CALL sa_server_option ( 'ConnsDisabled', 'OFF' );

*/

Well, it turns out that the original p_drop_other_connections doesn't work at all on connections to other databases running on the same server: the DROP CONNECTION command gets SQLCode -121 Permission denied.

Here's a new version of p_drop_other_connections that does work, by sending DROP CONNECTION commands to the other databases where they will work without getting permission denied:

CREATE PROCEDURE p_drop_other_connections ()
BEGIN

/* How to get full control over a SQL Anywhere server...

(1) CALL sa_server_option ( 'ConnsDisabled', 'ON' );

    At this point, any attempt to open a new connection will
    get "Connections to database have been disabled". 
    However, current connections will still work until...

(2) CALL p_drop_other_connections(); 

(3) Do whatever you need to do.

(4) CALL sa_server_option ( 'ConnsDisabled', 'OFF' );

*/

DECLARE @this_database_number     INTEGER;
DECLARE @this_connection_number   INTEGER;
DECLARE @database_name            VARCHAR ( 128 );
DECLARE @sql                      LONG VARCHAR;
DECLARE @sqlcode                  INTEGER;
DECLARE @sqlstate                 VARCHAR ( 5 );
DECLARE @errormsg                 VARCHAR ( 32767 );
DECLARE @connection_count         INTEGER;
DECLARE @dropped_count            INTEGER;

SET @this_database_number   = CONNECTION_PROPERTY ( 'DBNumber' );
SET @this_connection_number = CONNECTION_PROPERTY ( 'Number' );
SET @connection_count       = 0;
SET @dropped_count          = 0;

SET @database_name = ''; -- until filled in with an actual value

-- Create a remote server without the DBN filled in.

BEGIN
   DROP SERVER other_database;
   EXCEPTION WHEN OTHERS THEN -- ignore any exception
END;

CREATE SERVER other_database CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;DBN=unknown';

-- Loop through each connection on the server.

FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT sa_conn_info.DBNumber AS @database_number, 
       sa_conn_info.Number   AS @connection_number 
  FROM sa_conn_info() 
 ORDER BY sa_conn_info.DBNumber,
       sa_conn_info.Number
FOR READ ONLY
DO

   SET @connection_count = @connection_count + 1;

   CASE

      WHEN @database_number   = @this_database_number
       AND @connection_number = @this_connection_number THEN

         -- Don't drop this connection.

      WHEN @database_number = @this_database_number THEN

         -- Drop local connection.

         BEGIN
            SET @sql = STRING ( 
               'DROP CONNECTION ', 
               @connection_number );
            MESSAGE @sql TO CLIENT;
            EXECUTE IMMEDIATE @sql;
            SET @dropped_count = @dropped_count + 1;
            EXCEPTION WHEN OTHERS THEN
               SELECT SQLCODE, SQLSTATE, ERRORMSG() 
                 INTO @sqlcode, @sqlstate, @errormsg;
               MESSAGE STRING ( 'EXCEPTION: ', 
                  @sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;
         END;

      ELSE 

         IF DB_PROPERTY ( 'Name', @database_number ) <> @database_name THEN

            -- Change the DBN for the remote server.

            SET @database_name = DB_PROPERTY ( 'Name', @database_number );

            BEGIN
               SET @sql = 'ALTER SERVER other_database CONNECTION CLOSE';
               MESSAGE @sql TO CLIENT;
               EXECUTE IMMEDIATE @sql;
               SET @sql = STRING ( 
                  'ALTER SERVER other_database USING ''DRIVER=SQL Anywhere 11;DBN=',
                  @database_name,
                  '''' );
               MESSAGE @sql TO CLIENT;
               EXECUTE IMMEDIATE @sql;
               EXCEPTION WHEN OTHERS THEN
                  SELECT SQLCODE, SQLSTATE, ERRORMSG() 
                    INTO @sqlcode, @sqlstate, @errormsg;
                  MESSAGE STRING ( 'EXCEPTION: ', 
                     @sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;
            END;

         END IF;

         -- Drop the remote connection.

         BEGIN
            SET @sql = STRING ( 
               'FORWARD TO other_database ''DROP CONNECTION ',
               @connection_number,
               '''' );
            MESSAGE @sql TO CLIENT;
            EXECUTE IMMEDIATE @sql;
            SET @dropped_count = @dropped_count + 1;
            EXCEPTION WHEN OTHERS THEN
               SELECT SQLCODE, SQLSTATE, ERRORMSG() 
                 INTO @sqlcode, @sqlstate, @errormsg;
               MESSAGE STRING ( 'EXCEPTION: ', 
                  @sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;
         END;
   
   END CASE;

END FOR;

BEGIN
   DROP SERVER other_database;
   EXCEPTION WHEN OTHERS THEN -- ignore any exception
END;

MESSAGE STRING ( @dropped_count, ' of ', @connection_count, ' connections dropped.' ) TO CLIENT;

END;

Here's what a test looks like:

CALL p_drop_other_connections();

ALTER SERVER other_database CONNECTION CLOSE
ALTER SERVER other_database USING 'DRIVER=SQL Anywhere 11;DBN=ddd11b'
FORWARD TO other_database 'DROP CONNECTION 26'
FORWARD TO other_database 'DROP CONNECTION 27'
DROP CONNECTION 30
ALTER SERVER other_database CONNECTION CLOSE
ALTER SERVER other_database USING 'DRIVER=SQL Anywhere 11;DBN=ddd11c'
FORWARD TO other_database 'DROP CONNECTION 28'
FORWARD TO other_database 'DROP CONNECTION 29'
5 of 6 connections dropped.

Here's the before-and-after...



Here are some notes on the code...

The CREATE SERVER statement at line 44 establishes the partial definition for a remote server that will be completed via ALTER SERVER statements later on. The CREATE SERVER statement doesn't actually DO anything, like connect to a remote server, but that's not important here... what's important is that CREATE SERVER doesn't have to be semantically perfect when it's executed.

The FOR statement at line 49 calls the sa_conn_info() procedure to get information about every single connection to every single database on the server.

The CASE statement starting at line 60 makes a three-way choice:

• This connection? Don't do anything.

• A different connection to this database? Issue a simple DROP CONNECTION.

• A different database? Send a DROP CONNECTION to that database via FORWARD TO.

The BEGIN END blocks starting at lines 71, 93 and 114 are there to capture any exceptions and carry on with the next connection.

The EXECUTE IMMEDIATE statement at line 76 drops the local connection. If it gets an exception, the SET @dropped_count = @dropped_count + 1 at line 77 doesn't get executed; instead, the MESSAGE statement at line 81 displays the exception in the ISQL Messages tab.

The ALTER SERVER on lines 94 through 96 forces the remote server to disconnect from the previous database. This is necessary for the change made by the second ALTER SERVER on lines 97 through 102 to have any effect; otherwise, the old connection would remain in effect even though the USING clause has changed.

The FORWARD TO statement on lines 115 through 120 send the DROP CONNECTION statement over to the other database, where it's legal.