The MON_GET_TABLE table function returns monitor metrics for one or more tables.
EXECUTE privilege on the MON_GET_TABLE function.
List the activity on all tables accessed since the database was activated, aggregated across all database members, ordered by highest number of reads.
SELECT varchar(tabschema,20) as tabschema,
varchar(tabname,20) as tabname,
sum(rows_read) as total_rows_read,
sum(rows_inserted) as total_rows_inserted,
sum(rows_updated) as total_rows_updated,
sum(rows_deleted) as total_rows_deleted
FROM TABLE(MON_GET_TABLE('','',-2)) AS t
GROUP BY tabschema, tabname
ORDER BY total_rows_read DESC
The following example is a sample output from this query.
TABSCHEMA TABNAME TOTAL_ROWS_READ ...
-------------------- -------------------- -------------------- ...
SYSIBM SYSHISTO 113 ...
SYSIBM SYSWORKL 22 ...
SYSIBM SYSROUTI 13 ...
SYSIBM SYSSERVI 13 ...
SYSIBM SYSTHRES 6 ...
SYSIBM SYSTABLE 3 ...
SYSIBM SYSCONTE 2 ...
SYSIBM SYSDBAUT 2 ...
SYSIBM SYSEVENT 2 ...
SYSIBM SYSPLAN 1 ...
SYSIBM SYSSURRO 1 ...
SYSIBM SYSVERSI 1 ...
SYSIBM SYSXMLST 1 ...
SYSIBM SYSAUDIT 0 ...
SYSIBM SYSROLEA 0 ...
SYSIBM SYSROLES 0 ...
SYSIBM SYSTASKS 0 ...
SYSIBM SYSWORKA 0 ...
SYSIBM SYSXMLPA 0 ...
19 record(s) selected.
Output for query (continued).
... TOTAL_ROWS_INSERTED TOTAL_ROWS_UPDATED TOTAL_ROWS_DELETED
... -------------------- -------------------- --------------------
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
The MON_GET_TABLE table function returns one row of data per database table and per database member. If range-partitioned tables are being used, one row is returned for each table partition per database member. No aggregation across database members is performed. However, aggregation can be achieved through SQL queries as shown in the example.
Metrics are returned only for tables accessed since the database was activated.
Metrics are always enabled. You do not need to turn on any system monitor switches to access table metrics through this function.
Column Name | Data Type | Description |
---|---|---|
TABSCHEMA | VARCHAR(128) | table_schema - Table schema name |
TABNAME | VARCHAR(128) | table_name - Table name |
MEMBER | SMALLINT | member- Database member |
TAB_TYPE | VARCHAR(14) | table_type - Table type. This interface returns
a text identifier based on defines in sqlmon.h, and is one of:
|
TAB_FILE_ID | BIGINT | table_file_id - Table file ID |
DATA_PARTITION_ID | INTEGER | data_partition_id - Data partition identifier |
TBSP_ID | BIGINT | tablespace_id - Table space identification |
INDEX_TBSP_ID | BIGINT | index_tbsp_id - Index table space ID |
LONG_TBSP_ID | BIGINT | long_tbsp_id - Long table space ID |
TABLE_SCANS | BIGINT | table_scans - Table scans |
ROWS_READ | BIGINT | rows_read - Rows read |
ROWS_INSERTED | BIGINT | rows_inserted - Rows inserted |
ROWS_UPDATED | BIGINT | rows_updated - Rows updated |
ROWS_DELETED | BIGINT | rows_deleted - Rows deleted |
OVERFLOW_ACCESSES | BIGINT | overflow_accesses - Accesses to overflowed records |
OVERFLOW_CREATES | BIGINT | overflow_creates - Overflow creates |
PAGE_REORGS | BIGINT | |
DATA_OBJECT_L_PAGES1, 2 | BIGINT | |
LOB_OBJECT_L_PAGES2 | BIGINT | |
LONG_OBJECT_L_PAGES2 | BIGINT | |
INDEX_OBJECT_L_PAGES2 | BIGINT | |
XDA_OBJECT_L_PAGES2 | BIGINT | xda_object_l_pages - XML storage object (XDA) data logical pages |
DBPARTITIONNUM | SMALLINT | In a partitioned database environment, this is the numeric identifier for the database member. For DB2® Enterprise Server Edition and in a DB2 pureScale® environment, this value is 0. |
ADDITIONAL_DETAILS | BLOB(100K) | Reserved for future use. |
Notes:
|