onstat -g sql SQL statements

The onstat -g sql command displays a list of the currently active user sessions and the SQL statements associated with them. The command takes a session ID as an optional argument. By default, only the DBSA can view onstat -g sql

Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.           Current
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain   Role
4558  -              wbe                CR  Not Wait   0    0    9.03  Off      web
1458  -              wbe                DR  Wait 5     0    0    9.03  Off      web
125   -              wbe                DR  Wait 5     0    0    9.03  Off      web
124   -              wbe                DR  Wait 5     0    0    9.03  Off      web
123   -              wbe                DR  Wait 5     0    0    9.03  Off      web
112   -              wbe                DR  Wait 5     0    0    9.03  Off      web
101   -              wbe                DR  Wait 5     0    0    9.03  Off      web
100   -              wbe                DR  Wait 5     0    0    9.03  Off      web

Output Description

Heading Description Format See Also
Sess ID The session ID. Dec onstat -g ses
SQL Stmt type The type of SQL statement this session is currently running. Str  
Current Database The name of the database to which this session is connected. Str  
Iso Lvl The current isolation level of the session defined as
   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
Str  
Lock Mode The current setting for the SET LOCK MODE statement for this session. Str onstat -u
SQL ERR The SQL error, if any, for the last statement executed. Dec Informix Error Messages
ISAM ERR The ISAM error, if any, for the last statement executed. Dec Informix Error Messages
F.E. Vers The version of the front end application, if Informix, currently connected. Str  
Explain SET EXPLAIN setting Str  
Role Role of the current user Str  

Notes

  • The statement type will be '-' if no statement is currently being executed.
  • The database name will be '-' if the session is not currently connected to any database.
  • The UPDATE STATISTICS statement will periodically show a negative value in the SQL ERR and ISAM ERR columns. This is a normal function of the statement design.
  • 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.

Monitoring and Tuning

    The onstat -g sql command is a rapid method for determining the type of SQL statements currently running against the system, their associated databases and any potential application SQL errors that might have occurred.

    On smaller systems, it is quite common to first analyze the output from the onstat -g sql command to determine the variety of statements being executed. From there, other onstat commands can be run to determine the specifics of a particular session or SQL statement being executed.

    The onstat -g sql command can also be run with an optional session ID. The output contains the most recently and the current SQL statements being run by the session.