DB2 SQL Statistics
| SQL statistic name | Description | DBmarlin collects? | Doc link |
|---|---|---|---|
duration | Derived Field = STMT_EXEC_TIME | Yes - Derived | N/A |
executions | Derived Field = NUM_EXECUTIONS | Yes - Derived | N/A |
cpu_time_milliseconds | Derived Field = TOTAL_CPU_TIME / 1000 | Yes - Derived | N/A |
physical_reads | Derived Field = POOL_DATA_P_READS | Yes - Derived | N/A |
logical_reads | Derived Field = POOL_DATA_L_READS | Yes - Derived | N/A |
logical_writes | Derived Field = POOL_DATA_WRITES | Yes - Derived | N/A |
deadlocks | The total number of deadlocks that have occurred. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-d#r0001283 |
direct_read_reqs | The number of requests to perform a direct read of one or more sectors of data. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-d#r0001262 |
direct_reads | The number of read operations that do not use the buffer pool. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-d#r0001260 |
direct_read_time | The elapsed time required to perform the direct reads. This value is given in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-d#r0001264 |
direct_write_reqs | The number of requests to perform a direct write of one or more sectors of data. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-d#r0001263 |
direct_writes | The number of write operations that do not use the buffer pool. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-d#r0001261 |
direct_write_time | The elapsed time required to perform the direct writes. This value is reported in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-d#r0001265 |
duration | Calculated field. | Yes | N/A |
executions | Calculated field. | Yes | N/A |
lock_escals | The number of times that locks have been escalated from several row locks to a table lock. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0001284 |
lock_escals_global | Number of lock escalations on a global lock due to global lock memory usage. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0056238 |
lock_escals_locklist | Number of lock escalations due to local lock memory usage reaching the limit. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0056237 |
lock_escals_maxlocks | Number of lock escalations due to local lock memory usage reaching the limit | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0056236 |
lock_timeouts | The number of times that a request to lock an object timed out instead of being granted. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0001290 |
lock_timeouts_global | Number of lock timeouts where the application holding the lock was on a remote member. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0056235 |
lock_waits | The total number of times that applications or connections waited for locks. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0001293 |
lock_waits_global | Number of lock waits due to the application holding the lock being on a remote member. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0056233 |
lock_wait_time | The total elapsed time spent waiting for locks. in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0001294 |
lock_wait_time_global | Time spent on global lock waits in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0056234 |
log_buffer_wait_time | The amount of time an agent spends waiting for space in the log buffer in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0054044 |
log_disk_waits_total | The number of times agents have to wait for log data to write to disk. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0054046 |
log_disk_wait_time | The amount of time an agent spends waiting for log records to be flushed to disk in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-l#r0054045 |
logical_reads | Calculated field. | Yes | Not in IBM docs |
logical_writes | Calculated field. | Yes | Not in IBM docs |
num _executions | Calculated field. | Yes | Not in IBM docs |
num_exec_with_error | The number of executions of this statement that resulted in an error. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-n#r_num_exec_with_error |
num_exec_with_metrics | The number of times that this SQL statement section has been executed with the metrics collected. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-n#r0054155 |
num_exec_with_warning | The number of executions of this statement that resulted in a warning. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-n#r_num_exec_with_warning |
physical_reads | Calculated field. | Yes | Not in IBM docs |
pool_data_l_reads | Number of data pages synchronously read from the buffer pool (logical). | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-p#r0001235 |
pool_data_p_reads | Number of data pages synchronously and asynchronously read from the table space containers (physical). | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-p#r0001236 |
pool_data_writes | The number of times a buffer pool data page was physically written to disk. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-p#r0001237 |
pool_read_time | Indicates the total amount of time spent in milliseconds reading in data and index pages from the table space containers (physical). | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-p#r0001241 |
pool_write_time | Cumulative elapsed time for each asynchronous write to complete. This value is reported in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-p#r0001242 |
query_cost_estimate | Estimated cost for a query, as determined by the SQL compiler. This value is reported in timerons. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-q#r0001357 |
rows_deleted | This is the number of row deletions attempted. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-r#r0001312 |
rows_inserted | The number of row insertions attempted. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-r#r0001313 |
rows_modified | The number of rows inserted, updated, or deleted. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-r#r0051568 |
rows_read | The number of rows read from the table. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-r#r0001317 |
rows_returned | The number of rows returned. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-r#r0051569 |
rows_updated | This is the number of row updates attempted. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-r#r0001314 |
stmt_exec_time | The total time spent executing this statement by all agents on this member. The value is given in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-s#r0056454 |
total_act_time | The total amount of time spent executing activities. This value is given in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-t#r0054077 |
total_act_wait_time | Total time spent waiting within the database server, while processing an activity. The value is given in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-t#r0054078 |
total_cpu_time | The total amount of CPU time used while within the database system. Represents total of both user and system CPU time. This value is in microseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-t#r0054057 |
total_section_sorts | Total number of sorts performed during section execution, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-t#r0054149 |
total_section_sort_time | Total amount of time spent performing sorts while executing a section of a compiled query plan in milliseconds. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-t#r0054154 |
total_sorts | The total number of sorts that have been executed. | Yes | https://www.ibm.com/docs/en/db2/11.5?topic=reference-t#r0001219 |
Search online​
If this article doesn't have the information you need you can try searching online. Remember, you can contribute suggestions to this page.