onstat -g his command: Print SQL trace information
Use the onstat -g his command to display SQL trace information from the collection of syssqltrace tables (syssqltrace, syssqltrace_info, syssqltrace_hvar and syssqltrace_itr) in the sysmaster database.
The level setting of the SQLTRACE configuration
parameter affects what SQL trace information is stored and displayed
by the set of syssqltrace tables, and what information onstat
-g his displays. Each row of the syssqltrace table
describes a previously executed SQL statement. By default, only the
DBSA can view the syssqltrace information from the onstat
-g his command. However, when the UNSECURE_ONSTAT configuration
parameter is set to 1, all users can view this information.
Example output
The content of the output depends on the trace settings.
The Statement history section in the output provides information about the current settings for tracing.
Statement history:
Trace Level Low
Trace Mode Global
Number of traces 1000
Current Stmt ID 2
Trace Buffer size 2008
Duration of buffer 293 Seconds
Trace Flags 0x00001611
Control Block 0x4c2f0028
The following table describes this output:
| Information | Description |
|---|---|
| Trace Level | Amount of information traced. Valid values are LOW, MED, HIGH, and OFF. |
| Trace Mode | Type of tracing performed. Global refers to all users on the system User refers to only those users who have tracing enabled by an SQL administration API function. |
| Number of traces | The number of SQL statements that are being traced. This is the value set in your onconfig file unless the ntraces parameter is changed dynamically through SQL Administration API functions. The range is 500 to 2147483647. If you have 100,000 trace buffers and your organization runs 1000 SQL statements a second, and are tracing all of the statements, then the buffers would last for 100 seconds before they would begin being overwritten. |
| Current® Stmt ID | The ID for the current SQL statement. Each statement being traced gets a unique ID. |
| Trace Buffer size | The amount of data each trace buffer will capture, in bytes. If you set the size to 2KB, but have an SQL statement that is 12KB, the statement is truncated by at least 10KB. More data might be truncated, depending on what else is being traced. |
| Duration of buffer | The amount of time, in seconds, that the trace data in the current trace buffer spans. This is not how long the sqltrace feature has been running. In the above example Duration of buffer is 293 seconds which indicates the number of seconds between the first and last SQL statement that are traced. |
| Trace Flags | The current SQL trace flags that are set. |
| Control Block | The memory address of the SQL trace control block. |
The information displayed below is repeated one
time for each time a statement was run. In this example there are
two variables being called.
Statement # 2: @ 0x4c2f3028
Database: sysmaster
Statement text:
select count(*) from systables,syscolumns where systables.tabid > ? and
systables.nrows < ?
SELECT using tables [ systables syscolumns ]
| Information | Description |
|---|---|
| Database | The name of the database or part number of the systables entry for the database. |
| Statement text | The statement text for this SQL statement. If the statement is a stored procedure, then the statement text would display the procedure stack trace. The statement text might be truncated if the statement and the numeric statistics are larger than the trace buffer. |
Iterator/Explain
================
ID Left Right Est Cost Est Rows Num Rows Partnum Type
3 0 0 17 42 146 1048579 Index Scan
4 0 0 5249 2366 2366 1048580 Seq Scan
2 3 4 5266 99372 345436 0 Nested Join
1 2 0 1 1 1 0 Group
| Information | Description |
|---|---|
| ID | SQL iterator ID |
| Left | ID of the left input to the iterator |
| Right | ID of the right input to the iterator |
| Est Cost | Estimated cost of this iterator |
| Est Rows | Estimated rows for this iterator |
| Num Rows | Actual number of rows for this iterator |
| Partnum | The table or index partition number. |
| Type | Type of operation |
If
the SQL statement contains one or more variables, and you are tracing
host variables, the Host Variables section is included in the
output.
Host Variables
==============
1 integer 100
2 float 1000.0000000000000000
| Information | Description |
|---|---|
| Column 1 | The position of the variable in the statement. |
| Column 2 | The data type of the variable. |
| Column 3 | The value of the variable. |
Statement information:
Sess_id User_id Stmt Type Finish Time Run Time TX Stamp PDQ
5 2053 SELECT 01:08:48 0.4247 340a6e9 0
| Information | Description |
|---|---|
| Sess_id | The session ID |
| User_id | The operating system user ID |
| Stmt Type | The type of SQL statement |
| Finish Time | The time of day that the SQL statement finished |
| Run Time | The total amount of time consumed by the virtual processors or threads used to process the statement. For example, if the Finish Time is 1:15:00 and the Run Time is 9 minutes and the start time is not necessarily 1:06:00. There might be multiple virtual processors or threads involved in processing parts of the statement in parallel. |
| TX Stamp | The time the BEGIN WORK statement was logged in this transaction |
| PDQ | The SQL statement PDQ level |
The Statement Statistics section in the
output provides specific information about the statement.
Statement Statistics:
Page Buffer Read Buffer Page Buffer Write
Read Read % Cache IDX Read Write Write % Cache
1285 19444 93.39 0 810 17046 95.25
Lock Lock LK Wait Log Num Disk Memory
Requests Waits Time (S) Space Sorts Sorts Sorts
10603 0 0.0000 60.4 KB 0 0 0
Total Total Avg Max Avg I/O Wait Avg Rows
Executions Time (S) Time (S) Time (S) IO Wait Time (S) Per Sec
1 30.8660 30.8660 30.8660 0.0141 29.2329 169.8959
Estimated Estimated Actual SQL ISAM Isolation SQL
Cost Rows Rows Error Error Level Memory
102 1376 5244 0 0 CR 32608
| Information | Description |
|---|---|
| Page Read | Number of pages that have been read from disk for this SQL statement |
| Buffer Read | Number of times a page has been read from the buffer pool and not read from disk for this SQL statement |
| Read % Cache | Percentage of times the page was read from the buffer pool |
| Buffer IDX Read | This Currently not implemented |
| Page Write | Number of pages written to disk |
| Buffer Write | Number of pages modified and sent back to the buffer pool |
| Write % Cache | Percentage of time that a page was written to the buffer pool but not to disk |
| Lock Requests | Total number of locks required by this statement |
| Lock Waits | Number of times this SQL statement waited on locks |
| LK Wait Time (S) | Amount of time the statement waited for application locks, in seconds |
| Log Space | Amount of storage space that the SQL statement used in the logical log |
| Num Sorts | Total number of sorts used to execute the statement |
| Disk Sorts | Number of sorts which required disk space to execute the sort for this SQL statement |
| Memory Sorts | Number of sorts executed which executed entirely in memory for this SQL statement |
| Total Executions | Total number of times this prepared statement has been executed, or the number of times this cursor has been re-used |
| Total Time (S) | Total time this prepared statement ran, in seconds |
| Avg Time (S) | Average time this prepared statement required to execute, in seconds |
| Max Time (S) | Total time to run the prepared SQL statement, in seconds, excluding any time taken by the application. If you prepare a query then run the query 5 times, each time the query is run a trace is added to the trace buffer. The Max Time is the maximum time any one execution took. |
| Avg IO Wait | Average amount of time the statement waited for I/O, excluding any asynchronous I/O |
| I/O Wait Time (S) | Amount of time the statement waited for I/O, excluding any asynchronous I/O, in seconds |
| Avg Rows Per Sec | Average number of rows a second produced by this statement |
| Estimated Cost | The query optimizer cost associated with the SQL statement |
| Estimated Rows | Number of rows returned by the statement, as estimated by the query optimizer |
| Actual Rows | Number of rows returned for this statement |
| SQL Error | The SQL error number |
| ISAM Error | The RSAM or ISAM error number |
| Isolation Level | Isolation level this statement was run with |
| SQL Memory | Number of bytes this SQL statement required |
For the complete schema of the syssqltrace System Monitoring Interface table, see syssqltrace.
For details of setting the SQLTRACE configuration parameter, see SQLTRACE configuration parameter.