728x90

Problem(Abstract)

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: (버전 10.0까지 사용가능한 SQL)

    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

sid

Is 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.

username

Is the user ID at the operating system, that established the connection.

database

Is the database name the session is connected to.

pid

Is the identifier of the process at the operating system from which the connection was established.

tid

Is the thread identifier for the primary thread of user session. It matches column 'tid' in 'onstat -g ath' output.

address

Is the address for the thread, in hexadecimal value. It matches column 'rstcb' in 'onstat -g ath' and column 'address' in 'onstat -u' outputs.

state

Is 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_time

Is 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_time

Is 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_time

Is 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


http://www-01.ibm.com/support/docview.wss?uid=swg21209482

728x90

+ Recent posts