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.

Figure 1: Syntax:

1  onstat   -g ses?  session_id

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

Example output for all sessions

Figure 2: 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
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
user
The user who started the session
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 3: onstat -g ses session_id command output for a completed SQL statement
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:

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 HCL OneDB™)
Autocommit
Default transaction autocommit mode for HCL OneDB data sources
Packagepath
Client package path (not used by HCL OneDB)

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.
Figure 4: onstat -g ses session_id command output for a running SQL statement command output
session           effective                       #RSAM    total    used     dynamic 
id       user     user      tty  pid   hostname   threads  memory   memory   explain 
37       informix -         9    13965 apollo8    1        327680   308200   off 

Program :
/usr/informix/bin/dbaccess

tid      name     rstcb            flags    curstk   status
44       sqlexec  44e5b350         ---P---  4320     running-

Memory pools    count 2
name         class addr             totalsize  freesize   #allocfrag #freefrag 
37           V     8c64c040         323584     18736      199        21        
37*O0        V     8c756040         4096       744        1          1         

name             free       used         name             free       used      
overhead         0          6704         scb              0          144       
opentable        0          5968         filetable        0          768       
log              0          16536        temprec          0          22688     
keys             0          216          ralloc           0          194672    
gentcb           0          1592         ostcb            0          2992      
sqscb            0          27880        sql              0          13384     
hashfiletab      0          552          osenv            0          2672      
sqtcb            0          9664         fragman          0          728       
sapi             0          240          udr              0          272       
rsam_seqscan     0          528          

sqscb info
scb              sqscb            optofc   pdqpriority optcompind  directives
44ef4200         8ac90028         0        0           2           1         

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

Current statement name : unlcur

Current SQL statement (3) :
  select * from systables, sysindexes, syscolumns

  QUERY_TIMEOUT setting: 00:00:25
  Clock time elapsed   : 00:00:13

Last parsed SQL statement :
  select * from systables, sysindexes, syscolumns
The QUERY_TIMEOUT setting and clock time are displayed only for running queries, not for DML or DDL statements or administration operations.