Listing and limiting idle time of Informix sessions

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):

idle_sessions.sh

Copy to Clipboard

# Informix session listing by idle time
# Doug Lawry, Oninit Consulting, January 2022

if [ $# = 0 ]
then
    set -- 0
else
    case "$*" in *[!0-9]*)
        echo "Usage: $0 [hours]" 1>&2
        exit 1
    esac
fi

umask 0
cd /tmp
PROG=$(basename $0 .sh)

(dbaccess sysmaster - > $PROG.log 2>&1) <<!
    UNLOAD TO '$PROG.unl'
    SELECT  *
    FROM
    (
        SELECT  s.sid,
                s.pid,
                s.username[1,14],
                s.hostname[1,18],
                NVL(o.odb_dbname[1,20],'N/A') AS dbname,
                CASE
                    WHEN s.connected IS NOT NULL THEN
                        (CURRENT - DBINFO('utc_to_datetime', s.connected))
                END
                    :: INTERVAL HOUR(5) TO SECOND AS connected,
                CASE
                    WHEN t.last_run_time IS NOT NULL THEN
                        (CURRENT - DBINFO('utc_to_datetime', t.last_run_time))
                END
                    :: INTERVAL HOUR(5) TO SECOND AS idle
        FROM    syssessions AS s,
        OUTER ( sysrstcb AS r,
        OUTER   systcblst AS t ),
        OUTER   sysopendb AS o
        WHERE   s.sid = r.sid
        AND     r.tid = t.tid
        AND     s.sid = o.odb_sessionid
        AND     s.hostname != ''
        AND     o.odb_iscurrent = 'Y'
        AND     s.sid != DBINFO('sessionid')
    )
    WHERE   idle >= $1 UNITS HOUR
    ORDER   BY idle DESC, sid;
!

if [ $? != 0 ]
then
    egrep '^ *[0-9]+:' $PROG.log 1>&2
    exit 1
fi

awk -F'|' '
    BEGIN {
        fmt = "%-9s%-9s%-14s%-18s%-20s%12s%12s\n"
        printf(fmt, "Session", "Process", "User", "Hostname", \
            "Database", "Duration", "Idle")
    }
    {
        printf(fmt, $1, $2, $3, strip_domain($4), $5, $6, $7)
    }
    function strip_domain(host) {
        if (host ~ "[^.0-9]")
            for (pos = 1; pos <= length(host) ; ++pos)
                if (substr(host,pos,1) == ".")
                    return (substr(host,1,pos-1))
        return(host)
    }
' $PROG.unl

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):

kill_idle.sh

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.