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.

Figure 1: Syntax:

1  onstat   -g sql  sessionid
You can specify one of the following invocations.
Invocation
Explanation
onstat -g sql
Displays a one line summary for each session
onstat -g sql sessionid
Displays SQL information for a specific session
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 2: onstat -g sql command output for a completed SQL statement
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
Figure 3: onstat -g sql command output for a running SQL statement statement
onstat -g sql 28

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

Current statement name : unlcur

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

  QUERY_TIMEOUT setting:     0 (No Timeout)
  Clock time elapsed   : 00:00:12

Last parsed SQL statement :
  select * from systables, syscolumns, sysindexes

The QUERY_TIMEOUT setting and clock time are displayed only for running queries, not for DML or DDL statements or administration operations.