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 </div>
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 </div> |
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.
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.