Abstract

Some systems need user sessions terminated regularly – whether human or automated – to prevent eventual excessive memory consumption. This article provides an Informix Dynamic Server “sysmaster” query and script to identify those that are idle, and which therefore are most likely no longer needed and can be terminated.

Content

A standard scheduled task “idle_user_timeout” is provided with later versions of IDS which can be configured using Informix HQ:


Other than defining when it runs, the only adjustable parameter is the maximum idle time allowed. There is no control over the scope, such as to target only certain users or client hosts.

We therefore firstly need a report that lists such details against idle time, as produced by the following shell script (bash or ksh):

Copy to Clipboard
  • A threshold in hours can be specified as an argument, otherwise it defaults to zero (all sessions).
  • You can use the SQL statement directly in a database GUI replacing “$1” with your preferred value.
  • You might need to adjust column widths to match your system’s requirements.
  • Sessions not connected to any database have “N/A” in that column.
  • UTC timestamps are converted via the built-in DBINFO(‘utc_to_datetime’, value))
  • The current session is excluded using the built-in DBINFO(‘sessionid’)
  • Those with no host name are internal Informix sessions and also excluded.
  • Sessions are sorted descendingly by idle time.
  • See also an IBM Support page which lists similar SQL here.

In a real case, we needed to wrap this in another script to report and kill idle sessions for a specific client machine (which you can’t do with idle_user_timeout):

Copy to Clipboard


The “cron” job to run that as user “informix” at 6pm daily was:

Copy to Clipboard


An anonymised actual resulting email follows:

Copy to Clipboard

Conclusion

You can determine how long Informix sessions have been idle using SQL on the “sysmaster” database. Scripts are provided in this article to list the results and/or kill connections from specific clients that have been idle for more than a given number of hours.

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.

Contact Us

If you have any questions or would like to find out more about this topic, please contact us.

Author