onstat -g ses command: Print session-related information

Use the onstat -g ses command to display information about the session.

By default, only the DBSA can view onstat -g ses information. However, when the UNSECURE_ONSTAT configuration parameter is set to 1, all users can view this information.

>>-onstat-- -g--ses--+-session_id-+-----------------------------><
                     +-ready------+
                     +-running----+
                     +-busy-------+
                     +-bufwait----+
                     +-critsec----+
                     +-iowait-----+
                     +-logwait----+
                     +-mutexwait--+
                     +-lockwait---+
                     '-active-----'

You can specify one of the following invocations.

onstat -g ses: Displays a one-line summary for each session
onstat -g ses session_id: Displays information for a specific session
ready: Displays information for all sessions containing a thread that is on the ready queue
running: Displays information for all sessions containing a thread that is running
busy: Displays information for all sessions containing a thread that is either ready or running
bufwait: Displays information for all sessions containing a thread that is waiting on a buffer
critsec: Displays information for all sessions containing a thread that is in a critical section
iowait: Displays information for all sessions containing a thread that is waiting on I/O
logwait: Displays information for all sessions containing a thread that is waiting on a log buffer
mutexwait: Displays information for all sessions containing a thread that is waiting on a mutex
lockwait: Displays information for all sessions containing a thread that is waiting on a lock
active: Displays information for all sessions containing a thread that is in any of the states described above

Example output for all sessions

Figure 1. onstat -g ses command output

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
24       informix -        0        -        0        12288      7936       off
23       informix -        17602    carson   1        57344      48968      off
3        informix -        0        -        0        12288      9168       off
2        informix -        0        -        0        12288      7936       off

Last 20 Sessions Terminated

Ses ID  Username  Hostname  PID    Time              Reason
46      user_1    host_1    21220  01/19/2015.15:20  session limit txn time (60s)
43      user_1    host_1    21340  01/19/2015.15:14  session limit memory (5124 KB)
61      user_1    host_1    21404  01/19/2015.15:04  session limit logspace (10242 KB)
64      user_1    host_1    21458  01/19/2015.15:02  session limit txn time (39548 KB)

Output description: session section

Session id: The session ID
tty: The tty that is associated with the front end for this session
pid: The process ID associated with the front end for this session
hostname: The hostname from which this session connected
#RSAM threads: The number of RSAM thread that is allocated for this session
total memory: The amount of memory that is allocated for this session
used memory: The amount of memory that is actually used by this session
dynamic explain: Generate explain output of the SQL statements of the session (on or off)

Output description: Last 20 Sessions Terminated section

Ses ID: The session ID
Username: The user who started the session
Hostname: The hostname from which this session connected
PID: The process ID associated with the front end for this session
Time: The time at which the session was terminated.
Reason: The limit that was exceeded, followed by the limit value in parentheses.

Example output for a specific session

Figure 2. onstat -g ses session_id command output

session         effective                     #RSAM    total   used   dynamic 
id     user     user      tty  pid   hostname threads  memory  memory explain 
53     informix -         36   18638 apollo11 1        73728   63048  off 

Program :
/usr/informix/bin/dbaccess

tid      name     rstcb            flags    curstk   status
77       sqlexec  4636ba20         Y--P---  4240     cond wait  sm_read   -

Memory pools    count 1
name         class addr              totalsize  freesize   #allocfrag #freefrag 
53           V     4841d040         73728      10680      84         6         

name           free       used           name           free       used      
overhead       0          3288           scb            0          144       
opentable      0          2904           filetable      0          592       
log            0          16536          temprec        0          2208      
gentcb         0          1656           ostcb          0          2920      
sqscb          0          21296          sql            0          72        
hashfiletab    0          552            osenv          0          2848      
sqtcb          0          7640           fragman        0          392       

sqscb info
scb              sqscb            optofc   pdqpriority optcompind  directives
481b70a0         483e2028         0        0           0           1         

Sess       SQL            Current            Iso Lock       SQL  ISAM F.E. 
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain    
53         -              sysmaster          CR  Not Wait   0    0    9.24  Off        

Last parsed SQL statement :
  Database 'sysmaster@lx1'

Xadatasources participated in this session :
Xadatasource name                                    RMID       Active
xabasicdb@atmol10:sitaramv.xads_t3_i1                   6        YES
xabasicdb@atmol10:sitaramv.xads_t2_i1                   4        YES
xabasicdb@atmol10:sitaramv.xads_t1_i3                   3        YES
xabasicdb@atmol10:sitaramv.xads_t1_i2                   2        YES
xabasicdb@atmol10:sitaramv.xads_t1_i1                   1        YES
xabasicdb@atmol10:sitaramv.xads_t2_i2                   5        NO

DRDA client info
           Userid:       
           Wrkstnname:   nemea
           Applname:     db2jcc_application
           Acctng:       JCC03510nemea                                         
           Programid:    
           Autocommit:   
           Packagepath:

Session Limits
                Limit     Current
Locks           10000     1
Memory(KB)      5120      72
Temp Space(KB)  30720     0
Log Space(KB)   10240     0
Txn Time(s)     120       0

Output description: program section

Displays the full path of the client program that is used in your session. Use the client program information to monitor or stop access to the database.

Output description: threads section

Although this section has no title, the following output displays information about threads.

tid

The thread ID

name

The name of the thread

rstcb

RSAM control block

flags

Describes the status of the thread using the following codes:

Position 1

B: Waiting on a buffer
C: Waiting on a checkpoint
G: Waiting on a logical-log buffer write
L: Waiting on a lock
S: Waiting on a mutex
T: Waiting on a transaction
X: Waiting on a transaction cleanup
Y: Waiting on a condition

Position 2

*: An asterisk in this position means that the thread encountered an I/O failure in the middle of a transaction

Position 3

A: Archive thread
B: Begin work
P: Begin Prepare or Prepared work
X: XA prepared
C: Committing or committed
R: Aborting or aborted
H: Heuristically aborted or heuristically rolling back

Position 4

P: Primary thread

Position 5

R: Reading
X: Critical section

Position 6

R: Recovery thread

Position 7

M: Monitor thread
D: Daemon thread
C: Cleaner
F: Flusher
B: B-tree scanner

curstk

Current® stack size

status

Current thread status

Output description: memory pools header section

The information is repeated for each session pool.

name: Name of pool
class: Class of the memory where the pool is allocated from. R is for Resident, V is for Virtual, and M is for Message
addr: Address of the pool structure
totalsize: Total size of the memory that is acquired by the pool (in bytes)
freesize: Number of bytes free in the pool
#allocfrag: Number of allocated memory fragments in the pool
#freefrag: Number of free fragments in the pool

Output description: Memory pools section

name: Name of a component which allocated memory from the pool
free: Number of bytes freed
used: Number of bytes allocated

Output description: sqscb info section

scb: The session control block. This is the address of the main session structure in shared memory
sqscb: SQL level control block of the session
optofc: The current value of the OPTOFC environment variable or ONCONFIG configuration file setting
pdqpriority: The current value of the PDQPRIORITY environment variable or ONCONFIG configuration file setting
optcompind: The current value of the OPTCOMPIND environment variable or ONCONFIG configuration file setting
directives: The current value of the DIRECTIVES environment variable or ONCONFIG configuration file setting

Output description: SQL section

Displays SQL information for the specified session. This section contains the same information that is output from the onstat -g sql command. See onstat -g sql command: Print SQL-related session information.

Output description: Last parsed SQL statement section

The Last parsed SQL statement section contains the same information that is output from the onstat -g sql command. See onstat -g sql command: Print SQL-related session information.

Output description: Xadatasources participated in this session section

The Xadatasources participated in this session section shows information about the XA data sources that are available during the session, their resource manager identifiers, and whether they are currently active.

Xdatasource name: The XA data source that participated in the session
RMID: The identifier of the resource manager for the corresponding XA data source
Active: Whether the XA data source is still active

Output description: DRDA client info section

The DRDA client info section shows information about Distributed Relational Database Architecture™ (DRDA) connections to clients.

Userid: User ID of the client user

Wrkstnname: Name of the client workstation

Applname: Name of the client application, for example db2jcc_application

Acctng: Accounting string from the client, for example JCC03510nemea

Programid: Client program identifier (not used by Informix®)

Autocommit: Default transaction autocommit mode for Informix data sources

Packagepath: Client package path (not used by Informix)

Output description: Session limits section

Locks: The session's number of locks.
Memory(KB): The session's memory.
Temp Space(KB): The session's temporary table space.
Log Space(KB): Log space for single transactions.
Txn Time(s): Duration of single transactions.