Monitoring metrics with the Db2 Big SQL query interface

You can use the Db2 Big SQL query interface from any valid JDBC or ODBC connection to query database monitoring metrics.

About this task

Monitoring of the Big SQL I/O interfaces (readers and writers which read data to and from the distributed file system) as well as the Db2 component, which processes the data retrieved from the I/O interface, is important when monitoring the performance of the Db2 Big SQL service. The following metrics can help identify root cause of a performance slowdown during query execution.

Table 1. Metrics for monitoring the I/O interface
Metric	Description
ext_table_read_volume	Total volume that is read from physical devices by the Db2 Big SQL reader. The value is given in bytes.
ext_table_recvs_total	Total number of buffers that are received by the Db2 agents from the Db2 Big SQL reader. Each buffer is 2MB in size.
ext_table_recv_volume	Total volume that is received by the Db2 agent from the Db2 Big SQL reader. The value is given in bytes.
ext_table_recv_wait_time	Total time the Db2 agent spent waiting for the Db2 Big SQL reader to read and process data from the tables. The value is given in milliseconds.
ext_table_send_wait_time	Total time the Db2 agent spent waiting for the sent data to be processed and written by the Db2 Big SQL writer. The value is given in milliseconds.
ext_table_sends_total	Total number of buffers that were sent to the Db2 Big SQL writers by the Db2 agents. Each buffer is 2MB in size.
ext_table_send_volume	Total volume that was sent by the Db2 agents to the Db2 Big SQL writer. The value is given in bytes.
ext_table_write_volume	Total volume that was written by the Db2 Big SQL writer to physical devices, such as disks. The value is given in bytes.
num_columns_referenced	Number of table columns that are referenced during the execution of a SQL statement.
rows_inserted	Number of rows inserted into tables.
rows_modified	Number of rows that are inserted into tables. Update and delete operations are not supported for Hadoop tables.
rows_read	Number of rows that are read from tables.
section_exec_with_col_reference	Number of section executions that referenced columns in a table.
table_scans	Number of times the table was scanned.
tab_type	EXTERNAL_TABLE refers to Hadoop 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 Db2 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 |
...