Monitoring metrics with the Big SQL query interface
You can use the Big SQL query interface from any valid JDBC or ODBC connection to query database monitoring metrics.
About this task
In-memory metrics include information that is relevant for monitoring the performance of the distributed file system (DFS) readers and writers.
DFS table metrics | Value that is returned for DFS tables |
---|---|
ext_table_read_volume | Total volume that is read by the external table reader from physical devices, such as disks. The value is given in bytes. |
ext_table_recvs_total | Total number of buffers that are received by the agent from the external table readers. |
ext_table_recv_volume | Total volume that is received by the agent from the external table readers. The value is given in bytes. |
ext_table_recv_wait_time | Total time the agent spent waiting for the external table readers to read and process data from external tables. The value is given in milliseconds. |
ext_table_send_wait_time | Total time the agent spent waiting for the sent data to be processed and written by the external table writers. The value is given in milliseconds. |
ext_table_sends_total | Total number of buffers that were sent to the external table writers. |
ext_table_send_volume | Total volume that was sent by the agent to the external table writers. The value is given in bytes. |
ext_table_write_volume | Total volume that was written by the external writers to physical devices, such as disks. The value is given in bytes. |
num_columns_referenced | Number of DFS table columns that are referenced during the execution of a section for a SQL statement. |
rows_inserted | Number of row insert attempts for DFS tables. |
rows_modified | Number of rows that are inserted into DFS tables. Update and delete operations are not supported for DFS tables. |
rows_read | Number of rows that are read from DFS tables. |
section_exec_with_col_reference | Number of section executions that referenced columns in a DFS table by using a scan. |
table_scans | Number of table scans for the DFS table. |
tab_type | new value EXTERNAL_TABLE, to distinguish it from standard user table |
For
each of the metrics, you can also determine the supporting table functions, event monitors, and
snapshot levels. See the Monitor element reference
in the DB2 Knowledge
Center.
- Request-based monitoring table functions
-
- MON_GET_UNIT_OF_WORK
- MON_GET_UNIT_OF_WORK_DETAILS
- MON_GET_CONNECTION
- MON_GET_CONNECTION_DETAILS
- MON_GET_DATABASE
- MON_GET_DATABASE_DETAILS
- MON_GET_ROUTINE
- MON_GET_ROUTINE_DETAILS
- MON_GET_SERVICE_SUBCLASS
- MON_GET_SERVICE_SUBCLASS_DETAILS
- MON_GET_WORKLOAD
- MON_GET_WORKLOAD_DETAILS
- Activity-based monitoring functions
-
- MON_GET_ACTIVITY
- MON_GET_ACTIVITY_DETAILS
- MON_GET_PKG_CACHE_STMT
- MON_GET_PKG_CACHE_STMT_DETAILS
- WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES
- Object-based monitoring functions
-
- MON_GET_TABLE
- MON_GET_TABLE_USAGE_LIST
These metrics are also included in the statistics, unit of work, package cache, and activity event monitors. Their values are collected for the request and activity-based monitoring functions when the corresponding database configuration parameters mon_req_metrics and mon_act_metrics is set to a value different from NONE (such as BASE or EXTENDED). For the object-based monitoring functions, the database configuration parameter mon_obj_metrics must be set to EXTENDED.
For more information, see Monitor procedures and functions
in the DB2 Knowledge
Center.