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.

Read syntax diagramSkip visual syntax diagram

>>-onstat-- -g--his--------------------------------------------><

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 ]
The following table describes this output:
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.
 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
The following table describes this output:
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
The following table describes this output:
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
The following table describes this output:
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.

Copyright© 2018 HCL Technologies Limited