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

Table 1. database
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

The time spent metric monitors the database time spent by measuring the time for the following categories, which are calculated as:
  • 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_time

    The 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.
Note: The total time spent is calculated by adding the above categories.