onstat -g sql command: Print SQL-related session information

Use the onstat -g sql command to display SQL-related information about a session.

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

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

You can specify one of the following invocations.

Invocation: Explanation
onstat -g sql: Displays a one line summary for each session
onstat -g sql session_id: Displays SQL 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

Note: Encrypted passwords and password hint parameters in encryption functions are not shown. The following figure displays an encrypted password in the Last parsed SQL statement field.

Figure 1. onstat -g sql command output

onstat -g sql 22

Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.          Current
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain    Role
22     -             test               CR  Not Wait    0    0   9.03 Off         hr
Last parsed SQL statement :
  select id, name, decrypt_char(ssn, 'XXXXXXXXXX') from emp

Output description

Sess id

The session identifier

SQL Stmt type

The type of SQL statement

Current® Database

Name of the current database of the session

ISO Lvl

Isolation level

DR: Dirty Read
CR: Committed Read
CS: Cursor Stability
DRU: Dirty Read, Retain Update Locks
CRU: Committed Read, Retain Update Locks
CSU: Cursor Stability, Retain Update Locks
LC: Committed Read, Last Committed
LCU: Committed Read Last Committed with Retain Update Locks
RR: Repeatable Read
NL: Database Without Transactions

Lock mode

Lock mode of the current session

SQL Error

SQL error number encountered by the current statement

ISAM Error

ISAM error number encountered by the current statement

F.E. Version

The version of the SQLI protocol used by the client program

Explain

SET EXPLAIN setting

Current Role

Role of the current user