MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list

The MON_GET_TABLE_USAGE_LIST table function returns information from a usage list defined for a table.

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

Syntax

Read syntax diagramSkip visual syntax diagramMON_GET_TABLE_USAGE_LIST(usagelistschema,usagelistname,member)

The schema is SYSPROC.

Routine parameters

usagelistschema
An input argument of type VARCHAR(128) that specifies a valid schema name in the currently connected database when calling this function. If the argument is null or an empty string, usage lists are retrieved in all schemas in the database. If the argument is specified, usage lists are only returned for the specified schema.
usagelistname
An input argument of type VARCHAR(128) that specifies a usage list defined for a table that resides in the currently connected database when calling this function. If usagelistname is null or an empty string, then all usage lists defined for a table from the schemas identified by the usagelistschema that exist are retrieved. If specified, only the usage list specified from the schemas identified by the usagelistschema is returned.
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.

Information returned

Table 1. Information returned for MON_GET_TABLE_USAGE_LIST
Column Name Data Type Description
USAGELISTSCHEMA VARCHAR (128) usage_list_schema - Usage list schema
USAGELISTNAME VARCHAR (128) usage_list_name - Usage list name
TABSCHEMA VARCHAR(128) table_schema - Table schema name
TABNAME VARCHAR(128) table_name - Table name
MEMBER SMALLINT member - Database member
DATA_PARTITION_ID INTEGER data_partition_id - Data partition identifier
EXECUTABLE_ID VARCHAR(32) FOR BIT DATA executable_id - Executable ID
MON_INTERVAL_ID BIGINT mon_interval_id - Monitor interval identifier
LAST_UPDATED TIMESTAMP last_updated - Last update time stamp
NUM_REFERENCES BIGINT num_references - Number of references
NUM_REF_WITH_METRICS BIGINT num_ref_with_metrics - Number of references with metrics
ROWS_INSERTED BIGINT rows_inserted - Rows inserted
ROWS_DELETED BIGINT rows_deleted - Rows deleted
ROWS_UPDATED BIGINT rows_updated - Rows updated
ROWS_READ BIGINT rows_read - Rows read
OVERFLOW_CREATES BIGINT overflow_creates - Overflow creates
OVERFLOW_ACCESSES BIGINT overflow_accesses - Accesses to overflowed records
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
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 physical data reads for a table
OBJECT_DATA_GBP_L_READS BIGINT object_data_gbp_l_reads - GBP data logical reads for a table
OBJECT_DATA_GBP_P_READS BIGINT object_data_gbp_p_reads - GBP data physical reads for a table
OBJECT_DATA_GBP_INVALID_PAGES BIGINT object_data_gbp_invalid_pages - GBP invalid data pages for a table
OBJECT_DATA_LBP_PAGES_FOUND BIGINT object_data_lbp_pages_found - LBP data pages found 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 - GBP XDA data logical read requests for a table
OBJECT_XDA_GBP_P_READS BIGINT object_xda_gbp_p_reads - GBP XDA data physical read requests for a table
OBJECT_XDA_GBP_INVALID_PAGES BIGINT object_xda_gbp_invalid_pages - GBP invalid XDA data pages for a table
OBJECT_XDA_LBP_PAGES_FOUND BIGINT object_xda_lbp_pages_found - LBP 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
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
OBJECT_DATA_CACHING_TIER_L_READS BIGINT object_data_caching_tier_l_reads - Total logical reads of data pages from the caching tier monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
OBJECT_DATA_CACHING_TIER_PAGES_FOUND BIGINT object_data_caching_tier_pages_found - Table data pages found in caching tier monitor element
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 object_xda_caching_tier_l_reads - Total logical reads of XDA data pages from the caching tier monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
OBJECT_XDA_CACHING_TIER_PAGES_FOUND BIGINT object_xda_caching_tier_pages_found - Table XDA data pages found in caching tier monitor element
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 object_col_caching_tier_l_reads - Total logical reads of column-organized pages from the caching tier monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
OBJECT_COL_CACHING_TIER_PAGES_FOUND BIGINT object_col_caching_tier_pages_found - Table column-organized pages found in caching tier monitor element
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
CACHING_TIER_DIRECT_READS BIGINT caching_tier_direct_reads - Direct reads from caching tier monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
CACHING_TIER_DIRECT_READ_REQS BIGINT caching_tier_direct_read_reqs - Cache direct read requests monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
CACHING_TIER_DIRECT_READ_TIME BIGINT caching_tier_direct_read_time - Cache direct read time monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
CACHING_TIER_PAGE_READ_TIME BIGINT caching_tier_page_read_time - Cache page read time monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
OBJECT_INDEX_CACHING_TIER_PAGES_FOUND BIGINT object_index_caching_tier_pages_found - Table index-type page cache read ratio monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.

Usage notes

Each row returned by this function represents the total number of times (num_references) a unique section (DML statement only, executable ID) has referenced a particular object during a particular time interval (monitor interval ID) since being added to the list. The statistics collected for this row represents the total aggregated value across these executions during this time interval.

Use the num_ref_with_metrics column instead of the num_references column when computing averages, since the num_references column counts all executions of the section, regardless of whether or not the execution of the section contributed to the metrics that are reported.

Metrics collected by this function are controlled at the database level by using the mon_obj_metrics configuration parameter. By default, metrics collection is enabled.

Example

Retrieve the usage list USL_MON_PAYROLL from member 3

SELECT * FROM TABLE(
   MON_GET_TABLE_USAGE_LIST(NULL, 'USL_MON_PAYROLL', 3))

USAGELISTSCHEMA USAGELISTNAME   TABSCHEMA TABNAME  
--------------- --------------- --------- -------  
ISAYYID         USL_MON_PAYROLL ISAYYID   T1       
ISAYYID         USL_MON_PAYROLL ISAYYID   T1       

MEMBER  . . .  LAST_UPDATED                . . .
------  . . .  --------------------------  . . .
     3  . . .  2011-07-06-10.20.22.727803  . . .
     3  . . .  2011-07-06-10.20.58.202161  . . .

2 record(s) selected.