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.

The statements contain the activities that are completed with errors and the activities that are completed successfully within a certain period. The count of rows read is not the number of rows that were returned to the calling application. Instead, it is the number of rows that were read to return the result set. Additionally, this count does not include any index accesses. This metric summary represents database throughput by quantifying the statements and rows that are read in two aspects: an average number per minute and time series for an average.

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.
The total number of statements is calculated as:

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.
For example, the API call for average rows that are read per minute is:
'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'
where
start

required

BIGINT

Example: 1546272000000

Start timestamp.
end

required

BIGINT

Example: 1546272300000

End timestamp.
Note: The start and end timestamps are in Unix (Epoch) time format.
Therefore, the delta value for rows read will be calculated as follows:
<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.