IBM Support

System Monitor Interface query to find idle time for sessions

Troubleshooting


Problem

This document explains how to find out the time sessions that have been idle since their last read or write in system, through a System Monitor Interface (SMI) query with similar information as returned by onstat -g ntt.

Resolving The Problem


INTRODUCTION

Specific information about the sessions established against the IBM® Informix® Dynamic Server (IDS) instance regarding the connection time when the last read or write operation was performed, which can be found by running 'onstat -g ntt'. The last time a session has performed a read or write operation gives the Database Administrator or DBA an idea about how long a session has been idle. Similar information can be obtained by querying either the System Monitoring Interface (SMI) or sysmaster database.


BEFORE YOU BEGIN

Ensure that the IDS instance is in On-Line mode. This method uses a query either through the System Monitoring Interface (SMI) or sysmaster database.


STEPS

1. Run this query against the sysmaster database, using dbaccess:

    DATABASE sysmaster;

    SELECT s.sid, s.username, q.odb_dbname database, s.pid,
            t.tid, hex(t.address) address, t.state,
            dbinfo('UTC_TO_DATETIME',s.connected) conection_time,
            dbinfo('UTC_TO_DATETIME',t.run_time) last_run_time,
            current - dbinfo('UTC_TO_DATETIME',t.run_time) idle_time
    FROM syssessions s, systcblst t,  sysrstcb r, sysopendb q
    WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.odb_sessionid ;

2. Results will list each session information, including the time it connected to the database, the last time it performed a read or write operation, and the calculated idle time when comparing that last operation time with current time. The information regarding each session is the following:


Column name
Column Description
sidIs the session ID, the unique identifier for the session. Details on the session can be seen with 'onstat -g ses sid' command. It matches column 'sid' in 'onstat -g ntt' output.
usernameIs the user ID at the operating system, that established the connection.
databaseIs the database name the session is connected to.
pidIs the identifier of the process at the operating system from which the connection was established.
tidIs the thread identifier for the primary thread of user session. It matches column 'tid' in 'onstat -g ath' output.
addressIs the address for the thread, in hexadecimal value. It matches column 'rstcb' in 'onstat -g ath' and column 'address' in 'onstat -u' outputs.
stateIs the status in a numeric code, in which the session is currently at. If returns value '4', it means session is in 'wait' status, value '0' means session is currently 'running'.
connection_timeIs the date and time in which session was opened or established against a database in the server. It matches column 'open' in 'onstat -g ntt' output.
last_run_timeIs the last time a read or write operation was performed by this session in the server. It should match either 'read' or 'write' columns in 'onstat -g ntt' output for that session.
idle_timeIs the calculated time interval the session has been without doing any read nor write in the server, so can be considered as its idle time.


3. Results are somehow comparable to the onstat -g ntt output:
    onstat -g ntt


Example:

    1. You can create a file querysessions.sql containing the query above:
      SELECT s.sid, s.username, q.odb_dbname database, s.pid,
              t.tid, hex(t.address) address, t.state,
              dbinfo('UTC_TO_DATETIME',s.connected) conection_time,
              dbinfo('UTC_TO_DATETIME',t.run_time) last_run_time,
              current - dbinfo('UTC_TO_DATETIME',t.run_time) idle_time
      FROM syssessions s, systcblst t,  sysrstcb r, sysopendb q
      WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.odb_sessionid ;

      and run the query using dbaccess:
        $ dbaccess sysmaster querysessions

    2. Results:
      sid             38
      username        informix

      database        stores7
      pid             28513
      tid             588
      address         0x0B128498
      state           4
      conection_time  2005-05-31 09:08:47
      last_run_time   2005-05-31 09:08:47
      idle_time       0 00:19:44.000

      sid             39
      username        informix

      database        sysmaster
      pid             28386
      tid             587
      address         0x0B128140
      state           0
      conection_time  2005-05-31 09:06:12
      last_run_time   2005-05-31 09:28:31
      idle_time       0 00:00:00.000

      sid             41
      username        informix

      database        stores_demo
      pid             29120
      tid             589
      address         0x0B1286F0
      state           4
      conection_time  2005-05-31 09:20:51
      last_run_time   2005-05-31 09:22:27
      idle_time       0 00:06:04.000

      3 row(s) retrieved.

    3. Similar results as ones retrieved when issuing the onstat -g ntt command:
      $ onstat -g ntt

      Individual thread network information (times):
       netscb thread name    sid     open     read    write address
      b202138 sqlexec         41 09:20:50 09:22:27 09:22:27
      b203760 sqlexec         38 09:08:46 09:08:47 09:08:47
      b2272c8 sqlexec         39 09:06:11 09:28:31 09:28:31
      b1fccd0 tlitcplst       11 17:04:42                   killa|1838|tlitcp        
                   05/23/05
      b1fc678 sm_discon       10 17:04:42                      05/23/05
      b1f94f0 sm_listen        8 17:04:42                      05/23/05
      b1f8e98 sm_discon        7 17:04:42                      05/23/05
      b1d5ab8 sm_listen        5 17:04:42                      05/23/05
      b1bf858 tlitcppoll       4 17:04:42                      05/23/05
      b029958 sm_poll          3 17:04:42                      05/23/05
      b028d80 sm_poll          2 17:04:42                      05/23/05

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF025","label":"Platform Independent"},{"code":"PF008","label":"DYNIX\/ptx"},{"code":"PF010","label":"HP-UX"},{"code":"PF015","label":"IRIX"},{"code":"PF016","label":"Linux"},{"code":"PF026","label":"Reliant UNIX"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"7.2;7.3;9.2;9.3;9.4;10.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21209482