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
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
'Informix > informix troubleshooting' 카테고리의 다른 글
onstat cannot be loaded : Symbol resolution failed for /usr/lib/libc.a(aio.o) (0) | 2013.03.24 |
---|---|
Userthread flag values in IDS onstat -u output (0) | 2013.03.20 |
Compilation of C-ISAM program fails on AIX 5.x 64-bits (0) | 2013.03.09 |
INSUFFICIENT FREE HUGE PAGES ... (0) | 2013.03.08 |
Unloading around table corruption (0) | 2013.03.08 |