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