onstat -g ses session id

Prints session information by session id. If session id is missing, a one line summary of each session prints. This option can be used with the xctl utility.

Example Output

       local                               #RSAM   total   used    Start    Accum
sessid sessid user   tty    pid   hostname threads memory  memory  Time     Time
1.101  101    ssajip pts/3  19074 falcon1  1       229376  216016  14:21:02 00:05:03
 
tid      tcb              rstcb            flags    curstk   status                name
239      d97f950          c7b3c28          ---P---  8151     running        sqlexec_1.101
Memory pools    count 1
name         class addr             totalsize  freesize   #allocfrag #freefrag
101          V     d7b5028          229376     13360      441        4
name           free                 used                     name           free     used
overhead       0                    192                      scb            0         168
opentable      0                    8296                     filetable      0        1456
log            0                    4240                     temprec        0        2424
blob           0                    88                       keys           0         608
ralloc         0                    52568                    gentcb         0       19392
ostcb          0                    3248                     sqscb          0       18768
sql            0                    65896                    rdahead        0         200
hashfiletab    0                    552                      osenv          0        2112
sqtcb          0                    928                      fragman        0        5896
la_append_t    0                    184                      la_bufhdr_p    0          72
la_bufhdr_t    0                    104                      la_bm_buffs    0        4216
|--Session ID--| SQL         Current  Iso Lock     SQL ISAM F.E. Submit   Admit    Current
Global    Local  Stmt type   Database Lvl Mode     ERR ERR  Vers Time     Time     Role
1.101     101    SELECT INTO ko       CR  Not Wait 0   0    8.50 14:22:29 14:23:01 engineer
PDQ priority : 0
Scheduling level : 50
Current SQL statement :
  select a.* from systables a, systables b, systables c, systables d into  scratch tmp1

Output Description

The column definitions for the onstat -g ses option are:

session id

The session id

user

The username who started this session

tty

The tty 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 has connected

#RSAM thread

The number of RSAM threads allocated for this session

total memory

The amount of memory allocated for this session

used memory

The amount of memory actually used by this session

Start Time

The time when the user started the client session

Accum Time

The total RGM usage of all queries in the session. This is the sum of all the completed queries in the session, not including the current query. To determine the time of the current query, use onstat -g sql.

The thread status fields are:

tid

The thread id

name

The name of the thread

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

btcleaner

curstk

Current stack size

status

Current thread status

For serial plans that do not use any RGM resources, the Admit Time in the onstat -g sql output will be "N/A" (Not Applicable). For RGM queries that are still waiting in the RGM queue and have not yet been scheduled, the Admit Time in the onstat -g sql output will be "-" (blank).