MON_GET_TABLE table function - get table metrics

The MON_GET_TABLE table function returns monitor metrics for one or more tables.

Syntax

The schema is SYSPROC.

Table function parameters

tabschema: An input argument of type VARCHAR(128) that specifies a valid table schema name in the currently connected database when calling this function. If the argument is null or an empty string, metrics are retrieved for all tables in all schemas in the database. If the argument is specified, metrics are only returned for tables in the specified schema.
tabname: An input argument of type VARCHAR(128) that specifies a valid table name in the currently connected database when calling this function. If the argument is null or an empty string, metrics are retrieved for all the tables in the database.
member: An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all active database members. If the NULL value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned for MON_GET_TABLE
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
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	page_reorgs - Page reorganizations
DATA_OBJECT_L_PAGES	BIGINT	data_object_l_pages - Logical data object pages
LOB_OBJECT_L_PAGES	BIGINT	lob_object_l_pages - Logical LOB object pages
LONG_OBJECT_L_PAGES	BIGINT	long_object_l_pages - Logical Long object pages
INDEX_OBJECT_L_PAGES	BIGINT	index_object_l_pages - Logical index object pages
XDA_OBJECT_L_PAGES	BIGINT	xda_object_l_pages - Logical XDA object pages
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number
NO_CHANGE_UPDATES	BIGINT	no_change_updates - Number of no change row updates
LOCK_WAIT_TIME	BIGINT	lock_wait_time - Time waited on locks
LOCK_WAIT_TIME_GLOBAL	BIGINT	lock_wait_time_global - Lock wait time global
LOCK_WAITS	BIGINT	lock_waits - Lock waits
LOCK_WAITS_GLOBAL	BIGINT	lock_waits_global - Lock waits global
LOCK_ESCALS	BIGINT	lock_escals - Number of lock escalations
LOCK_ESCALS_GLOBAL	BIGINT	lock_escals_global - Number of global lock escalations
DATA_SHARING_STATE	VARCHAR(19)	data_sharing_state - Data sharing state monitor element
DATA_SHARING_STATE_CHANGE_TIME	TIMESTAMP	data_sharing_state_change_time - Data sharing state change time monitor element
DATA_SHARING_REMOTE_LOCKWAIT_COUNT	BIGINT	data_sharing_remote_lockwait_count - Data sharing remote lock wait count monitor element
DATA_SHARING_REMOTE_LOCKWAIT_TIME	BIGINT	data_sharing_remote_lockwait_time - Data sharing remote lock wait time monitor element
DIRECT_WRITES	BIGINT	direct_writes - Direct writes to database
DIRECT_WRITE_REQS	BIGINT	direct_write_reqs - Direct write requests
DIRECT_READS	BIGINT	direct_reads - Direct reads from database
DIRECT_READ_REQS	BIGINT	direct_read_reqs - Direct read requests
OBJECT_DATA_L_READS	BIGINT	object_data_l_reads - Buffer pool data logical reads for a table
OBJECT_DATA_P_READS	BIGINT	object_data_p_reads - Buffer pool data physical reads for a table
OBJECT_DATA_GBP_L_READS	BIGINT	object_data_gbp_l_reads - Group buffer pool data logical reads for a table
OBJECT_DATA_GBP_P_READS	BIGINT	object_data_gbp_p_reads - Group buffer pool data physical reads for a table
OBJECT_DATA_GBP_INVALID_PAGES	BIGINT	object_data_gbp_invalid_pages - Group buffer pool invalid data pages for a table
OBJECT_DATA_LBP_PAGES_FOUND	BIGINT	object_data_lbp_pages_found - Local buffer pool found data pages for a table
OBJECT_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	object_data_gbp_indep_pages_found_in_lbp - Group buffer pool independent data pages found in local buffer pool
OBJECT_XDA_L_READS	BIGINT	object_xda_l_reads - Buffer pool XDA data logical reads for a table
OBJECT_XDA_P_READS	BIGINT	object_xda_p_reads - Buffer pool XDA data physical reads for a table
OBJECT_XDA_GBP_L_READS	BIGINT	object_xda_gbp_l_reads - Group buffer pool XDA data logical read requests for a table
OBJECT_XDA_GBP_P_READS	BIGINT	object_xda_gbp_p_reads - Group buffer pool XDA data physical read requests for a table
OBJECT_XDA_GBP_INVALID_PAGES	BIGINT	object_xda_gbp_invalid_pages - Group buffer pool invalid XDA data pages for a table
OBJECT_XDA_LBP_PAGES_FOUND	BIGINT	object_xda_lbp_pages_found - Local buffer pool XDA data pages found for a table
OBJECT_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	object_xda_gbp_indep_pages_found_in_lbp - Group buffer pool XDA independent pages found in local buffer pool
NUM_PAGE_DICT_BUILT	BIGINT	num_page_dict_built - Number of page-level compression dictionaries created or re-created
STATS_ROWS_MODIFIED	BIGINT	stats_rows_modified - Rows modified since last RUNSTATS
RTS_ROWS_MODIFIED	BIGINT	rts_rows_modified - Rows modified since last real time statistics
COL_OBJECT_L_PAGES	BIGINT	col_object_l_pages - Column-organized logical pages
TAB_ORGANIZATION	CHAR(1)	tab_organization - Data organization in table
OBJECT_COL_L_READS	BIGINT	object_col_l_reads - Column-organized logical reads
OBJECT_COL_P_READS	BIGINT	object_col_p_reads - Column-organized physical reads
OBJECT_COL_GBP_L_READS	BIGINT	object_col_gbp_l_reads - Group buffer pool column-organized logical reads
OBJECT_COL_GBP_P_READS	BIGINT	object_col_gbp_p_reads - Group buffer pool column-organized physical reads
OBJECT_COL_GBP_INVALID_PAGES	BIGINT	object_col_gbp_invalid_pages - Group buffer pool column-organized invalid data pages
OBJECT_COL_LBP_PAGES_FOUND	BIGINT	object_col_lbp_pages_found - Local buffer pool column-organized pages found
OBJECT_COL_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	object_col_gbp_indep_pages_found_in_lbp - Group buffer pool column-organized index pages found in local buffer pool
NUM_COLUMNS_REFERENCED	BIGINT	num_columns_referenced - Number of columns referenced
SECTION_EXEC_WITH_COL_REFERENCES	BIGINT	section_exec_with_col_references - Section execution with column-organized references
OBJECT_DATA_CACHING_TIER_L_READS	BIGINT	Reserved for future use.
OBJECT_DATA_CACHING_TIER_PAGES_FOUND	BIGINT	Reserved for future use.
OBJECT_DATA_CACHING_TIER_GBP_INVALID_PAGES	BIGINT	Reserved for future use.
OBJECT_DATA_CACHING_TIER_GBP_INDEP_PAGES_FOUND	BIGINT	Reserved for future use.
OBJECT_XDA_CACHING_TIER_L_READS	BIGINT	Reserved for future use.
OBJECT_XDA_CACHING_TIER_PAGES_FOUND	BIGINT	Reserved for future use.
OBJECT_XDA_CACHING_TIER_GBP_INVALID_PAGES	BIGINT	Reserved for future use.
OBJECT_XDA_CACHING_TIER_GBP_INDEP_PAGES_FOUND	BIGINT	Reserved for future use.
OBJECT_COL_CACHING_TIER_L_READS	BIGINT	Reserved for future use.
OBJECT_COL_CACHING_TIER_PAGES_FOUND	BIGINT	Reserved for future use.
OBJECT_COL_CACHING_TIER_GBP_INVALID_PAGES	BIGINT	Reserved for future use.
OBJECT_COL_CACHING_TIER_GBP_INDEP_PAGES_FOUND	BIGINT	Reserved for future use.
EXT_TABLE_RECV_WAIT_TIME	BIGINT	ext_table_recv_wait_time - Total agent wait time for external table readers monitor element
EXT_TABLE_RECVS_TOTAL	BIGINT	ext_table_recvs_total - Total row batches received from external table readers monitor element
EXT_TABLE_RECV_VOLUME	BIGINT	ext_table_recv_volume - Total data received from external table readers monitor element
EXT_TABLE_READ_VOLUME	BIGINT	ext_table_read_volume - Total data read by external table readers monitor element
EXT_TABLE_SEND_WAIT_TIME	BIGINT	ext_table_send_wait_time - Total agent wait time for external table writers monitor element
EXT_TABLE_SENDS_TOTAL	BIGINT	ext_table_sends_total - Total row batches sent to external table writers monitor element
EXT_TABLE_SEND_VOLUME	BIGINT	ext_table_send_volume - Total data sent to external table writers monitor element
EXT_TABLE_WRITE_VOLUME	BIGINT	ext_table_write_volume - Total data written by external table writers monitor element

Usage notes

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.

Rows are returned only for tables accessed since the database was activated.

The metrics returned by this function are collected at two levels. Some metrics are always collected, while the collection of other metrics is controlled at the database level using the mon_obj_metrics configuration parameter. Refer to the descriptions provided in the preceding table to determine if any settings must be active for data to be collected for a particular metric.

Examples

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 is an example of 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

Get the explicit hierarchical locking state for tables in the schema JOE from member 1:

SELECT SUBSTR(TABNAME,1,8) AS TABNAME,
       MEMBER,
       DATA_SHARING_STATE AS DS_STATE,
       DATA_SHARING_REMOTE_LOCKWAIT_COUNT AS DSRL_COUNT,
       DATA_SHARING_REMOTE_LOCKWAIT_TIME AS DSRL_TIME
     FROM
       TABLE(MON_GET_TABLE('JOE','',1))

This query returns the following output:

TABNAME  MEMBER DS_STATE            DSRL_COUNT           DSRL_TIME
-------- ------ ------------------- -------------------- --------------------
T1            1 NOT_SHARED                             0                    0
T2            1 SHARED                                 2                21844
T3            1 SHARED                                 0                    0

This output shows that table T1 is currently in a "NOT_SHARED" state and has never been forced out of this state during the current database activation. Table T3 has never entered a "NOT_SHARED" state. Table T2 has been in a "NOT_SHARED" state twice and has had to exit both times.