Throughput
The throughput metric monitors the query performance by measuring the number of statements that are processed and the number of rows that are read from the table.
Repository table
The repository table that is used to provide the throughput metrics is <SCHEMA>.database. The default schema for repository table is IBMCONSOLE.
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 a parameter of -2, so the values are the aggregation from all the workloads for all the active database members. However, DSM_WORKLOAD, DV_WORKLOAD, BIGSQL_WORKLOAD, and CONSOLE_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 by the user when monitoring was configured for the database. The mapping of dbconn_int to a connection name is stored in the repository table <SCHEMA>.RTMON_MAP_DBCONN. |
collected | timestamp | Timestamp of the end of 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 | Used to filter out data. |
row_id | char(5) | Internal column. |
total_cpu_usec | bigint |
Aggregation of total CPU time that is consumed by all workloads and members. The value is in microseconds. To scale this as seconds, divide by 1,000,000. This metric is gathered from the column total_cpu_time from mon_get_workload table function. For more information, see Db2 documentation. |
act_completed_total | bigint |
Aggregation of total activities that are completed by all workloads and members. The number of activities that are completed is a measure of work accomplished. This metric is gathered from the column app_act_completed_total from mon_get_workload table function. For more information, see Db2 documentation. |
rows_read | bigint | Aggregation of rows that are read for all workloads and members. Rows read is a good indication of how much work is being done by queries.This metric is gathered from column ROWS_READ from mon_get_workload table function. For more information, refer to Db2 documentation. |
rows_modified | bigint |
Aggregation of rows modified which includes updates and deletions of table data for all the workloads and members. This metric is gathered from the column ROWS_MODIFIED from mon_get_workload table function. For more information, refer to Db2 documentation. |
rows_returned | bigint |
Aggregation of the number of rows that were selected and returned to the application for all the workloads and members. This metric is gathered from the 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
members. This metric is gathered from mon_get_workload table function. For more information, refer to Db2 documentation. |
direct_reads | bigint | Aggregation of direct reads from the database. |
direct_writes | bigint | Aggregation of direct writes to the database. |
total_sorts | bigint | Aggregation of total number of sorts that were 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 to perform commit processing on the database server. The value is in milliseconds. |
total_app_rollbacks | bigint | Aggregation of total number of rollback statements issued by the client application. |
total_section_time | bigint | Aggregation of total time agents spent performing section execution. The value is in milliseconds. |
total_routine_time | bigint | Aggregation of total time spent executing routines. The value is 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 in milliseconds. |
pool_write_time | bigint | Cumulative elapsed time for each asynchronous write to complete. This aggregated value is in milliseconds. |
direct_read_time | bigint | The elapsed time required to perform the direct reads. This aggregated value is in milliseconds. |
direct_write_time | bigint | The elapsed time required to perform the direct writes. This aggregated value is 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 in milliseconds. |
wlm_queue_time_total | bigint | Aggregation of time spent waiting on a WLM queuing threshold. This value is in milliseconds. |
lock_wait_time | bigint | The total elapsed time spent waiting for locks. The aggregated value is 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 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 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 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 in milliseconds. |
lock_waits | bigint | Aggregation of the total number of times that applications or connections waited for locks. |
lock_timeouts | bigint | Aggregation of the number of times that a request to lock an object timed out instead of being granted. |
lock_escals | bigint | Aggregation 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 | Aggregation of the total number of deadlocks that occurred. |
agent_wait_time | bigint | Time spent by an application queued to wait for an agent under concentrator configurations. The aggregated value is in milliseconds. |
lock_wait_time_global | bigint | Time that is spent on global lock waits. The aggregated value is 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 in milliseconds. |
audit_subsystem_wait_time | bigint | The 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 in milliseconds. |
audit_file_write_wait_time | bigint | Aggregation of time spent waiting to write an audit record. The value is in milliseconds. |
diaglog_write_wait_time | bigint | The time spent waiting on a write to the db2diag log file. The aggregated value is 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 in milliseconds. |
comm_exit_wait_time | bigint | The time spent waiting for the return from a communication exit library API function. The aggregated value is 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 agents spent performing section execution. Processing time does not include wait time. The aggregated value is in milliseconds. |
total_section_sort_proc_time | bigint | The 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 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 for 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 | The 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 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 | 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 | 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 in milliseconds. |
total_rqst_time | bigint | The total amount of time spent working on requests. The 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_authentication_proc_tm, total_commit_proc_time, total_rollback_proc_time, total_backup_proc_time, and, total_index_build_proc_time |
sum_io_time | bigint | The value is the aggregation of pool_read_time, pool_write_time, direct_read_time, and 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, and 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 towards the total of the subclass it aborts in. |
total_compilations | bigint | 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 | 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 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. |
sql_rate = act_aborted_total + act_completed_total;
Metric calculation
The delta values of the columns in the repository table are calculated for a given timeframe. The database repository table contains the aggregation of the metrics, so the delta is calculated as the difference between the current collection timestamp and the previous collection interval.'https://{HOSTNAME}/dbapi/v4/metrics/average_rows_?start=1546272000000&end=1546272300000' \
-H 'authorization: Bearer {AUTH_TOKEN}' \
-H 'content-type: application/json' \
-H 'x-db-profile: SOME_STRING_VALUE'
start required |
BIGINT Example: 1546272000000 Start timestamp. |
end required |
BIGINT Example: 1546272300000 End timestamp. |
<Aggregated_value_of_rows_read_at_end_time> - <Aggregated_value_of_rows_read_at_start_time>/ <end_timestamp> - <start_timestamp>
To report a rate, such as rows read per minute, use the following
formula:(<Aggregated_value_of_rows_read_at_end_time> - <Aggregated_value_of_rows_read_at_start_time>) * 60000/ <end_timestamp> - <start_timestamp>
where <end_timestamp> - <start_timestamp> is the length of the collection interval in
milliseconds. To scale the result, multiply by an appropriate value. In this example, the formula multiplies by 60,000 ms/minute to scale the result to rows read/minute.