Feedback
Did this article resolve your question/issue?

   

Article

How to disconnect all self and remote users from a database?

Information

 
TitleHow to disconnect all self and remote users from a database?
URL Name000037493
Article Number000160295
EnvironmentProduct: Progress OpenEdge
Version: All supported versions
OS: All supported platforms
Question/Problem Description
How to disconnect all self-service and remote users from a database?
How to disconnect all users except the current user with ABL VST's?
How to find all self-service and remote users currently connected to the database?
How to disconnect all connected users at the end of the day?
Before shutting the database down, how to disconnect all remaining connected user sessions?
How to disconnect remote user sessions before terminating Remote Servers
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
A list of currently connected users and their associated usernums can be found by running:
$  PROSHUT dbname -C list

And then currently connected users can be disconnected from the command line with:
$  PROSHUT dbname -C disconnect <usernum>

When there are many user connections to disconnect every connected user manually, the process can be scripted by interrogating the _Connect VST then parsing the _Connect-Usr value to an OS-COMMAND which runs the PROSHUT -C DISCONNECT utility. 

This command can only be executed on the database server itself, not from a client session running on a remote machine:
  • While a remote PROSHUT session is able to disconnect a database user by choosing Option 1 Disconnect a User from the menu presented:
$   proshut dbname -H <host> -S <port> -Mm <bytes>
  • The use of the DISCONNECT argument is limited to local non-networked connections only.
  • The same remote PROSHUT session is not able to disconnect that user by adding the '-C disconnect' parameter to the command line when run from a remote machine. It will fail with: "You cannot run the DISCONNECT from a remote machine. (2259)"
    To this end, an AppServer connection can be used instead, where the Agent runs on the database server. For further information refer to Article How to disconnect any database user from a remote machine using an AppServer   
The following code is provided as an example that can be further customized to suit requirement, with added error checking and could be extended to include the disconnection of users in environments where users connect to multiple databases or specific remote user sessions before terminating a Remote Server.

The example below first finds then disconnects each SELF or REMC connection found until there are none remaining, leaving the current connection instate to be disconnected on completion. 
 
/*******************************************************************
* Disconnect all SELF and REMC users from DB, but not me! *
********************************************************************/

DEFINE VARIABLE mycon AS INTEGER     NO-UNDO.

DEFINE VARIABLE xDLC AS CHARACTER INIT "D:\dlc\"  NO-UNDO.
DEFINE VARIABLE xWRK AS CHARACTER INIT "D:\wrk\"  NO-UNDO.
DEFINE VARIABLE xDB AS CHARACTER INIT "mydatabase"  NO-UNDO.

DEFINE VARIABLE xLog AS CHARACTER.
DEFINE STREAM xLog.
xLog = xWRK + "\xxdbdisc.log".

FIND _MyConnection.
mycon = _MyConnection._MyConn-Userid .

FIND FIRST _File WHERE _File-Name = "_Connect" NO-ERROR.
IF AVAILABLE(_File) THEN

REPEAT ON ENDKEY UNDO, LEAVE:
    HIDE MESSAGE NO-PAUSE.   

FIND NEXT _Connect WHERE
    _Connect-Pid <> INT(?) AND
    (
    _Connect-Usr <> ? AND
    _Connect-Usr <> mycon ) AND  
    _Connect-Name <> (?) AND
  (_Connect-Type EQ "REMC" OR
  _Connect-Type EQ "SELF")
  NO-ERROR.

  IF AVAILABLE _connect THEN

RUN dbdisc (_Connect-Usr,_Connect-Pid,_Connect-Name).
  ELSE DO:
      OUTPUT STREAM xLog TO VALUE(xLog) APPEND.
        PUT STREAM xLog UNFORMATTED
            TODAY ' '
            STRING(TIME,"HH:MM:SS") ' '
            'Disconnect no-one except me'
            SKIP(1).
        OUTPUT STREAM xLog CLOSE.
         LEAVE.
  END.
END.

MESSAGE "done, NOW DO unto thyself"
    VIEW-AS ALERT-BOX INFO BUTTONS OK.

/*******************************************************************
* Disconnect User num from Database, but not me! *
********************************************************************/

PROCEDURE dbdisc:

    DEFINE INPUT PARAMETER UsrID LIKE _connect-usr.
    DEFINE INPUT PARAMETER UsrPid LIKE _connect-pid.
    DEFINE INPUT PARAMETER UsrName LIKE _connect-name.

    OS-COMMAND SILENT value(
        xDLC + "bin\proshut " +
        xWRK + xDB + " -C disconnect " +
        string(UsrId)).

    OUTPUT STREAM xLog TO VALUE(xLog) APPEND.
    PUT STREAM xLog UNFORMATTED
        TODAY ' '
        STRING(TIME,"HH:MM:SS") ' '
        'Disconnect ' UsrName FORMAT "X(12)"
        '(PID=' UsrPid
          ' Usr=' UsrId
          ' DB=' xDB
          skip.
        OUTPUT STREAM xLog CLOSE.

        PAUSE 1 NO-MESSAGE.

END PROCEDURE.

 
Workaround
Notes
Last Modified Date1/12/2021 6:15 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.