Time spent
This time spent metric indicates the percentage of time that is taken for database calls.
Repository table
This metric monitors the database time that is spent by measuring the time of various database activities (such as SQL execution time, I/O time). The information is collected from the repository table <SCHEMA>.database.Source
The data that is found in the repository table database comes from the table function mon_get_workload. The table function mon_get_workload is passed the parameter of -2, so the values are the aggregation from all the workloads for all active database members. DSM_WORKLOAD, CONSOLE_WORKLOAD, DV_WORKLOAD, BIGSQL_WORKLOAD are excluded. .
Columns
Column Name | Type | Description |
dbconn_int | INTEGER | The internal ID for connection name. dbconn_int maps to a unique connection name, which is the name that is specified for the connection when user configures a database to be monitored. The mapping of dbconn_int to connection name is stored in repository table <SCHEMA>.RTMON_MAP_DBCONN. |
collected | Timestamp | Timestamp of the end of the collection interval. |
timestamp | BIGINT | Timestamp of the end of collection interval that is stored as Bigint. This time is stored as a Unix Epoch time. To convert this number into a readable format, see Change UNIX time to readable timestamp. |
level_tag | BIGINT | level_tag is used to filter out data. |
row_id | CHAR(5) | Internal column |
total_cpu_usec | BIGINT | Aggregation of total CPU time for all workloads and all members. The value is in microseconds. This metric is gathered from column total_cpu_time from mon_get_workload table function. For more information, see Db2® documentation. |
act_completed_total | BIGINT | Aggregation of total completed activities for all workloads and all members. This metric is gathered from column app_act_completed_total from mon_get_workload table function. For more information, see Db2 documentation. |
rows_read | BIGINT | Aggregation of rows read for all workloads and all members. This metric is gathered from column ROWS_READ from mon_get_workload table function. For more information, see Db2 documentation. |
rows_modified | BIGINT | Aggregation of rows modified which includes updates and deletions of table data for all workloads and all members. This metric is gathered from column ROWS_MODIFIED from mon_get_workload table function. For more information, see Db2 documentation. |
rows_returned | BIGINT | This is the aggregation of the number of rows that have been selected and returned to the application for all workloads and all members. This metric is gathered from column ROWS_RETURNED from mon_get_workload table function. For more information, see Db2 documentation. |
logical_reads | BIGINT | Aggregation of Buffer pool data logical reads (POOL_DATA_L_READS), Buffer pool XDA data logical reads (POOL_XDA_L_READS), Buffer pool index logical reads (POOL_INDEX_L_READS), Buffer pool column-organized logical reads (POOL_COL_L_READS), Buffer pool temporary data logical reads (POOL_TEMP_DATA_L_READS), Buffer pool temporary index logical reads (POOL_TEMP_INDEX_L_READS), Buffer pool temporary XDA data logical reads (POOL_TEMP_XDA_L_READS), Buffer pool column-organized temporary logical reads (POOL_TEMP_COL_L_READS). The aggregation is for all the workloads and all the members. This metric is gathered from mon_get_workload table function.For more information, see Db2 knowledge center page. |
direct_reads | BIGINT | Aggregation of direct reads from database. |
direct_writes | BIGINT | Aggregation of direct writes to database. |
total_sorts | BIGINT | Aggregation of total number of sorts that have been executed. |
total_app_commits | BIGINT | Aggregation of total number of commit statements issued by the client application. |
total_commit_time | BIGINT | Aggregation of the total amount of time spent performing commit processing on the database server. The value is given in milliseconds. |
total_app_rollbacks | BIGINT | Aggregation of total number of rollback statements issued by the client application. |
total_section_time | BIGINT | Aggregation of the total time agents spent performing section execution. The value is given in milliseconds. |
total_routine_time | BIGINT | Aggregation of the total time spent executing routines. The value is given in milliseconds. |
pool_read_time | BIGINT | Indicates the total amount of time spent reading in data and index pages from the table space containers (physical) for all types of table spaces. This aggregated value is given in milliseconds. |
pool_write_time | BIGINT | Cumulative elapsed time for each asynchronous write to complete. This aggregated value is reported in milliseconds. |
direct_read_time | BIGINT | The elapsed time required to perform the direct reads. This aggregated value is given in milliseconds. |
direct_write_time | BIGINT | The elapsed time required to perform the direct writes. This aggregated value is reported in milliseconds. |
total_section_sort_time | BIGINT | Total amount of time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application. The aggregated value is given in milliseconds. |
wlm_queue_time_total | BIGINT | The aggregation of time spent waiting on a WLM queuing threshold. This value is given in milliseconds. |
lock_wait_time | BIGINT | The total elapsed time spent waiting for locks. The aggregated value is given in milliseconds. |
log_buffer_wait_time | BIGINT | The amount of time an agent spends waiting for space in the log buffer. The aggregated value is given in milliseconds. |
log_disk_wait_time | BIGINT | The amount of time an agent spends waiting for log records to be flushed to disk. The aggregated value is given in milliseconds. |
total_compile_proc_time | BIGINT | The total amount of processing (non-wait) time spent performing explicit compiles on the database server. Explicit compiles are compilations that are directly initiated by a user request such as a bind, rebind, prepare, or execute immediate. The aggregated value is given in milliseconds. |
total_implicit_compile_proc_tm | BIGINT | The total amount of processing (non-wait) time spent performing implicit compiles on the database server. Implicit compiles are compilations that are not directly requested by the user. That is, they are not a result of a bind, rebind, prepare, or execute immediate request. For example, an implicit compilation might occur when executing a statement that was bound by using the VALIDATE RUN option if the statement needs to be compiled at execution time. The aggregated value is given in milliseconds. |
lock_waits | BIGINT | The aggregation of the total number of times that applications or connections waited for locks. |
lock_timeouts | BIGINT | The aggregate of the number of times that a request to lock an object timed out instead of being granted. |
lock_escals | BIGINT | The aggregate of the number of times that locks have been escalated from several row locks to a table lock. When DB2_AVOID_LOCK_ESCALATION is ON, lock escalation is not performed by the database. Instead, lock escalation is avoided and the lock_escals monitor element is used to count the number of times the database avoids escalation. |
deadlocks | BIGINT | The aggregation of the total number of deadlocks that have occurred. |
agent_wait_time | BIGINT | Time spent by an application queued to wait for an agent under concentrator configurations. The aggregated value is given in milliseconds. |
lock_wait_time_global | BIGINT | Time that is spent on global lock waits. The aggregated value is given in milliseconds. |
tcpip_wait_time | BIGINT | Aggregation of the time spent blocking on a TCP/IP send to the client and the time spent waiting for an incoming client request over TCP/IP excluding idle time. The value is given in milliseconds. |
ipc_wait_time | BIGINT | Aggregation of Interprocess communication receiving wait time and send wait time. The value is given in milliseconds. |
audit_subsystem_wait_time | BIGINT | Time spent waiting for space in audit buffer. Waiting occurs when audit buffer is full and agent must wait for audit daemon to write buffer to disk. The aggregated value is given in milliseconds. |
audit_file_write_wait_time | BIGINT | The aggregation of time spent waiting to write an audit record. The value is given in milliseconds. |
diaglog_write_wait_time | BIGINT | The time spent waiting on a write to the db2diag log file. The aggregated value is given in milliseconds. |
evmon_wait_time | BIGINT | The amount of time that an agent waited f an event monitor record to become available. A wait occurs when the agent tries to write an event monitor record and the agent is blocked until a fast writer record becomes available. Fast writers are used for high volume, parallel writing of event monitor data to tables, files, or pipes. The aggregated value is in milliseconds. |
total_extended_latch_wait_time | BIGINT | The aggregation of the amount of time, in milliseconds, spent in extended latch waits. |
prefetch_wait_time | BIGINT | The time an application spent waiting for an I/O server (prefetcher) to finish loading pages into the buffer pool. The aggregated value is given in milliseconds. |
comm_exit_wait_time | BIGINT | Time spent waiting for the return from a communication exit library API function. The aggregated value is given in milliseconds. |
ida_wait_time | BIGINT | Aggregation of the total amount of time spent waiting to send data to an in-database analytics process and waiting to receive data from an in-database analytics process. |
cf_wait_time | BIGINT | The amount of time spent communicating with the cluster caching facility. This time does not include time spent performing any of the processings that might have been requested by, or that might occur as a result of the communications, such as granting locks or performing page reclaims. The aggregated value is in milliseconds. |
reclaim_wait_time | BIGINT | In a Db2 pureScale® environment, this element represents the amount of time spent waiting on page locks, where the lock request caused a page to be reclaimed. The aggregated value of time is in milliseconds. |
spacemappage_reclaim_wait_time | BIGINT | In a Db2 pureScale environment, this element represents the amount of time spent waiting on page locks for pages that are related to internally maintained object space management where the lock request caused a reclaim from another member. The aggregated value of time is in milliseconds. |
total_rtn_user_code_proc_tm | BIGINT | The total amount of time spent processing user code in routines. The aggregated value is in milliseconds. |
total_section_proc_time | BIGINT | The total amount of processing time the agents spent performing section execution. Processing time does not include wait time. The aggregated value is given in milliseconds. |
total_section_sort_proc_time | BIGINT | Total amount of processing (non-wait) time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application. The aggregated value is given in milliseconds. |
total_col_proc_time | BIGINT | The total non-wait processing time spent accessing columnar data in a query on column-organized tables. The aggregated value is given in milliseconds. This element is a subset of the elapsed time by the monitor element total_col_time, and represents the time that is returned in which the column-organized processing subagents were not idle on a measured wait time (for example, lock wait or IO). |
total_commit_proc_time | BIGINT | The total amount of processing (non-wait) time spent performing commit processing on the database server. The aggregated value is in milliseconds. |
total_rollback_proc_time | BIGINT | The total amount of processing (non-wait) time spent performing rollback operations on the database server. The aggregated value is in milliseconds. |
total_runstats_proc_time | BIGINT | The total amount of processing (non-wait) time spent performing runstats operations on the database server. The aggregated value is in milliseconds. Anytime the runstats utility spends throttled does not count towards the runstats processing time. |
total_reorg_proc_time | BIGINT | The total amount of processing (non-wait) time spent performing reorg operations on the database server. The aggregated value is in milliseconds. |
total_load_proc_time | BIGINT | Total amount of processing (non-wait) time spent performing load processing on the database server. The aggregated value is in milliseconds. |
total_backup_proc_time | BIGINT | The total amount of processing (non-wait) time that was spent doing online backups. The aggregated value is in milliseconds. |
total_index_build_proc_time | BIGINT | The total amount of processing (non-wait) time that is spent building indexes due to index creation or re-creation. This time includes the time that is spent by subagents when the index creation or re-creation operation is parallelized. The aggregated value is in milliseconds. |
total_connect_request_proc_tm | BIGINT | The aggregation of the amount of processing (non-wait) time spent processing a connection or switch user request, in milliseconds. |
total_connect_auth_proc_tm | BIGINT | The aggregation of the amount of processing (non-wait) time spent performing connection or switch user authentication, in milliseconds. |
client_idle_wait_time | BIGINT | The time spent waiting for the client to send its next request. The aggregated value is given in milliseconds. |
total_rqst_time | BIGINT | The total amount of time spent working on requests. This aggregated value is in milliseconds. |
total_wait_time | BIGINT | The total time spent waiting within the database server. The aggregated value is in milliseconds. |
sum_sql_execution_time | BIGINT | Aggregation of total_col_proc_time , total_section_proc_time and total_rtn_user_code_proc_tm. |
sum_nonsql_proc_time | BIGINT | Total amount of time spent on non SQL processing. The aggregation includes total_compile_proc_time, total_implicit_compile_proc_tm, total_load_proc_time, total_reorg_proc_time, total_runstats_proc_time, total_connect_request_proc_tm, total_connect_auth_proc_tm, total_commit_proc_time, total_rollback_proc_time, total_backup_proc_time, total_index_build_proc_time. |
sum_io_time | BIGINT | The value is the aggregation of pool_read_time, pool_write_time, direct_read_time, direct_write_time. |
local_lock_wait_time | BIGINT | The value is the difference between the lock_wait_time and lock_wait_time_global. |
other_wait_time | BIGINT | The value is the aggregation of agent_wait_time, wlm_queue_time_total, log_buffer_wait_time, log_disk_wait_time, tcpip_wait_time, ipc_wait_time, audit_subsystem_wait_time, audit_file_write_wait_time, diaglog_write_wait_time, evmon_wait_time, total_extended_latch_wait_time, prefetch_wait_time, comm_exit_wait_time, ida_wait_time, reclaim_wait_time, spacemappage_reclaim_wait_time, fed_wait_time |
total_act_time | BIGINT | The total amount of time spent executing activities. This aggregated value is in milliseconds. |
act_aborted_total | BIGINT | Aggregation of the total number of coordinator activities at any nesting level that completed with errors. For service classes, if an activity is remapped to a different service subclass with a REMAP ACTIVITY action before it aborts, then this activity counts only toward the total of the subclass it aborts in. |
total_compilations | BIGINT | The aggregation of the total number of explicit compiles on the database server. Explicit compiles are compilations that are directly initiated by a user request such as a bind, rebind, prepare or execute immediate. |
sort_overflows | BIGINT | The aggregation of the total number of sorts that ran out of sort heap and might have required disk space for temporary storage. |
physical_reads | BIGINT | Aggregation of Buffer pool data physical reads, Buffer pool index physical reads, Buffer pool column-organized physical reads, Buffer pool temporary data physical reads, Buffer pool temporary index physical reads, Buffer pool temporary XDA data physical reads, Buffer pool column-organized temporary physical reads. |
fed_wait_time | BIGINT | Aggregation of time that was spent by a federation server to run a statement and get results from a remote data source. |
fed_rows_deleted | BIGINT | Aggregation of the number of rows deleted in a remote database by a federation system statement. |
fed_rows_inserted | BIGINT | Aggregation of the number of rows inserted into a remote database by a federation system statement. |
fed_rows_updated | BIGINT | Aggregation of the number of rows updated into a remote database by a federation system statement |
fed_rows_read | BIGINT | Aggregation of the number of rows read from a remote database by a federation system statement. |
fed_waits_total | BIGINT | Aggregation of the total number of times that a federation server ran statements and received results from a remote data source. |
select_sql_stmts | BIGINT | Aggregation of the number of SQL SELECT statements that were executed. |
uid_sql_stmts | BIGINT | Aggregation of the number of UPDATE, INSERT, MERGE and DELETE statements that were executed. |
other_sql_stmts | BIGINT | Aggregation of the total number of dynamic and static SQL statements that were attempted excluding the SELECT, UPDATE, INSERT, MERGE and DELETE statements that were executed. |
Metric calculation
- Time spent on SQL processing - Total time spent on SQL processing is calculated as:
Time spent on SQL processing = total_section_proc_time + total_routine_user_code_proc_time
where total_section_proc_time includes total_col_proc_time and total_section_sort_proc_timeThe percentage time spent on SQL processing is calculated as: (Time spent on SQL processing/total time spent) * 100
The SQL processing time for total_section_proc_time, total_routine_user_code_proc_time and total_section_sort_proc_time is calculated as:
total_section_proc_time_percent = 100 *(total_section_proc_time - total_section_sort_proc_time) / total time spent
total_section_sort_proc_time_percent = 100 * total_section_sort_proc_time / total time spent
total_routine_user_code_proc_time_percent= 100 * total_routine_user_code_proc_time / total time spent
- Lock wait time - Retrieved from columns lock_wait_time and
lock_wait_time_global.
Total lock wait time percentage, which includes both local and global locks is calculated as:
total lock wait time percent = 100 * lock_wait_time / total time spent
The time spent on local locks and global locks is calculated as:
local lock wait time percent = 100 * (lock_wait_time - lock_wait_time_global) / total time spent
global lock wait time percent = 100 * lock_wait_time_global / total time spent
- Time spent on I/O - Total time spent on I/O is the sum of columns pool_read_time,
pool_write_time, direct_read_time and direct_write_time.
Total I/O Time percent = 100 * (pool_read_time + pool_write_time + direct_read_time + direct_write_time)/total time spent .
The breakdown of time spent is calculated as:
pool read time percent = 100 * pool_read_time / total time spent
pool write time percent= 100 * pool_write_time / total time spent
direct read time percent= 100 * direct_read_time / total time spent
direct write time percent= 100 * direct_write_time / total time spent
- Other processing time - Total time spent on other processing is the sum of columns
total_compile_proc_time, total_implicit_compile_proc_time,
total_load_proc_time, total_reorg_proc_time, total_runstats_proc_time,
total_commit_proc_time,total_rollback_proc_time, total_connect_request_proc_time,
total_backup_proc_time and total_index_build_proc_time.
The breakdown of time spent on other processing is calculated as:
total compile proc time percent = 100 * (total_compile_proc_time - total_implicit_compile_proc_time) / total time spent
total implicit compile proc time percent = 100 * total_implicit_compile_proc_time / total time spent
total load proc time percent = 100 * total_load_proc_time / total time spent
total reorg proc time percent = 100 * total_reorg_proc_time / total time spent
total runstats proc time percent = 100 * total_runstats_proc_time_percent / total time spent
total connect request proc time percent (includes total_connect_authentication_proc_time) = 100 * total_connect_request_proc_time / total time spent
total commit proc time percent = 100 * total_commit_proc_time / total time spent
total rollback proc time percent = 100 * total_rollback_proc_time / total time spent
total backup proc time percent = 100 * total_backup_proc_time / total time spent
total index build proc time percent = 100 * total_index_build_proc_time / total time spent
- Other wait time -Total of other wait time is the sum of columns agent_wait_time,
wlm_queue_time_total, log_buffer_wait_time, log_disk_wait_time, tcpip_wait_time, ipc_wait_time,
ida_wait_time, audit_subsystem_wait_time, audit_file_write_wait_time, diaglog_write_wait_time,
evmon_wait_time, total_extended_latch_wait_time, prefetch_wait_time, comm_exit_wait_time,
reclaim_wait_time, spacemappage_reclaim_wait_time and fed_wait_time (for federation
server).
The breakdown of time spent is calculated as:
agent wait time percent= 100 * agent_wait_time / total time spent
wlm queue time total percent= 100 * wlm_queue_time_total / total time spent
log buffer wait time percent= 100 * log_buffer_wait_time / total time spent
log disk wait time percent= 100 * log_disk_wait_time / total time spent
tcpip wait time percent= 100 * tcpip_wait_time / total time spent
ipc wait time percent= 100 * ipc_wait_time / total time spent
audit subsystem wait time percent= 100 * audit_subsystem_wait_time / total time spent
audit file write wait time percent= 100 * audit_file_write_wait_time / total time spent
diaglog write wait time percent= 100 * diaglog_write_wait_time / total time spent
evmon wait time percent= 100 * evmon_wait_time / total time spent
total extended latch wait time percent= 100 * total_extended_latch_wait_time / total time spent
prefetch wait time percent= 100 * prefetch_wait_time / total time spent
comm exit wait time percent= 100 * comm_exit_wait_time / total time spent
ida wait time percent= 100 * ida_wait_time / total time spent
reclaim wait time percent= 100 * reclaim_wait_time / total time spent
spacemappage reclaim wait time percent= 100 * spacemappage_reclaim_wait_time / total time spent
fed wait time percent= 100 * fed_wait_time / total time spent
- Time spent communicating with cluster caching facility - Retrieved from column cf_wait_time.