Oninit Logo
The Down System Specialists
Partnerships Contact
Onstat -a -b/-B -C -c -d/-D -F -f -G -h -i -j -k/-K -L -l -m -o -P -p -R -r -s -t/-T -u -X -x -z

onstat -g his SQL Trace Information

The onstat -g his command displays SQL trace information

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.

Statement history:

Trace Level                 Low	
Trace Mode               Global	
Number of traces           2000	
Current Stmt ID              28	
Trace Buffer size          4096
Duration of buffer          182 Seconds
Trace Flags          0x00001611	
Control Block        0x4c2f0028


Statement # 28     @ 0x4c2f3028

Database:        sysmaster
 Statement text:
   select count(*) from t9000dbaudit t9000, t9010dbname t9010 where c9000custid = ?
                and t9000.c9000id = t9010.c9000id

	     SELECT using tables [ t9000dbaudit, t9010dbname ]


 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


Host Variables
 1 integer    69


Statement information:
  Sess_id  User_id  Stmt Type  Finish Time   Run Time   TX Stamp  PDQ
  237892   1092     SELECT     09:44:23      0.0017     340a6e9    0


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          0.0027     0.0027     0.0027     0.0010     0.0010     2895.8959

  Estimated  Estimated  Actual     SQL        ISAM       Isolation  SQL
  Cost       Rows       Rows       Error      Error      Level      Memory
  102        1          1          0          0          CR         32608

Output Description

Field 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.
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 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
Host Variables
  • 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.
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
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

To discuss how Oninit ® can assist please call on +1-913-674-0360 or alternatively just send an email specifying your requirements.