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.

Table 1. Metrics for monitoring the access to DFS tables
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.

These are the supported monitoring functions:

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.

Procedure

Connect to the Big SQL server.

Run the following query as an example of request-based metrics:


SELECT MEMBER, ROWS_READ, EXT_TABLE_RECV_WAIT_TIME 
  FROM TABLE(MON_GET_DATABASE( -2)) 
  AS t order by MEMBER;

For each member or partition it returns the total number of rows that are read from Hadoop tables and non-Hadoop tables (such as temporary) and the time the agent waits for rows that are returned by the external table DFS readers since the last activation of the database, which corresponds to the last start of Big SQL.


+--------+-----------+--------------------------+
| MEMBER | ROWS_READ | EXT_TABLE_RECV_WAIT_TIME |
+--------+-----------+--------------------------+
|      0 |    511350 |                        0 |
|      1 |   1006586 |                    11630 |
|      2 |     10779 |                     2102 |
|      3 |   2755544 |                     9583 |
+--------+-----------+--------------------------+
4 rows in results(first row: 0.1s; total: 0.1s)

For more detail at the table level, the following query on object-based metrics can be used:


SELECT VARCHAR(SUBSTR( TABSCHEMA, 1, 20), 20) 
  AS TABSCHEMA, VARCHAR(SUBSTR(TABNAME,1, 20), 20) 
  AS TABNAME, MEMBER, TABLE_SCANS, ROWS_READ, EXT_TABLE_RECV_WAIT_TIME 
FROM TABLE(MON_GET_TABLE('', '',  -2)) 
  AS t where TAB_TYPE = 'EXTERNAL_TABLE' 
  order by TABSCHEMA, TABNAME, MEMBER;

A portion of the output is shown in the following example result:


+-----------+------------+------+----------+---------+------------------------+
| TABSCHEMA | TABNAME    | MEMB | TABLE_SC | ROWS_RE | EXT_TABLE_RECV_WAIT_TI |
|           |            |   ER |      ANS |      AD |                     ME |
+-----------+------------+------+----------+---------+------------------------+
| GOSALESDW | DIST_INVEN |    0 |        0 |       0 |                      0 |
|           | TORY_FACT  |      |          |         |                        |
| GOSALESDW | DIST_INVEN |    1 |        1 |   53837 |                      0 |
|           | TORY_FACT  |      |          |         |                        |
| GOSALESDW | DIST_INVEN |    2 |        0 |       0 |                      0 |
|           | TORY_FACT  |      |          |         |                        |
| GOSALESDW | DIST_INVEN |    3 |        3 |   62029 |                    644 |
...