Question Disconnect idle connections

[FocusIT]

Apologies if this has been asked before, but is there a method of diconnecting idle connections to an OE database? We are trying to maximise the available licenses under our concurrent user allowance and all attempts to fix the humans (i.e. get them to log off the sytem at end of shift) have failed. Is there a way to disconnect them if there has been no activity for a preset time? Switching to Named User or Registered Client licensing is not currently an option due to excessive cost.

This applies to both ABL and SQLC conenctions. For ABL connections the users are just locking their screens and going home with the application logged in, sometimes with uncommitted transactions and open locks. For SQLC connections there are a few SSIS and SAS packages that are not disconnecting properly.

Ideally the solution would be run every hour on the data server disconnecting idle connections that have read or writtne to the database. I have looked through the VST tables and can determine the connection time from the _Connect table, but cannt find a field for last DBread or similar. How can I tell if the connection has been idle for a certain period of time?

Details: -

OE Enterprise 10.2B07 X64
Windows Server 2008 R2 X64

Regards

Andrew Bremner

 

[Cringer]

You can monitor the _UserIO, but be careful how you decide what it a real user and what is a server process such as the AppServer.

FOR EACH _Connect NO-LOCK
  WHERE  _Connect._Connect-Name NE ?,
  FIRST _UserIO NO-LOCK 
  WHERE _UserIO._UserIO-Usr EQ _Connect._Connect-Usr:
END

The three fields you are probably interested in are
_UserIO._UserIO-DbAccess
_UserIO._UserIO-DbRead
_UserIO._UserIO-DbWrite

 

[Rob Fitzpatrick]

In _Connect you can distinguish between ABL and SQL clients. As SQL clients are always remote you should be able to disconnect them or kill the client process without issue. You need to be more careful with ABL clients. If they are remote clients (_Connect-Type REMC, _Connect-ClientType ABL) then again, you can disconnect them or kill them without issue. If they are self-service ABL clients (_Connect-Type SELF, _Connect-ClientType ABL) you should not kill them. If a self-service client is killed while it holds a latch the database will shut down. Instead disconnect it with a proshut -C disconnect.

It may be more difficult than you think to decide deterministically which clients are "idle". What does "activity" mean to you? Can "idle" state be determined from VST data? If a client is doing file or network I/O, or performing a computationally-intensive task then surely it is active and should not be disconnected. But you will see no activity for such a client in the database. However if your business hours and remote-access policies dictate that no users should be connected and doing work past a certain time of day then take the steps outlined above to disconnect them. You should verify in the database log that they actually do disconnect. This may take some time, as the server will need to back out any uncommitted transactions.

 

[TomBascom]

Actually the alternative licensing schemes almost always end up being less expensive if you take the time to work through the details.

 

[Rob Fitzpatrick]

Actually the alternative licensing schemes almost always end up being less expensive if you take the time to work through the details.

That's been my experience, particularly when there are multiple servers involved.

If it's shift work, with multiple people using the same pool of computers at different times, then the Registered Client model may be a good fit. It costs less per seat than Concurrent and can be installed on multiple servers including DR.