object_col_caching_tier_l_reads - Total logical reads of column-organized pages from the caching tier monitor element

For remote storage table spaces, the total number of columnar-type pages synchronously read by agents from the caching tier and the remote storage for the table.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 1. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_col_caching_tier_pages_found - Table column-organized pages found in caching tier monitor element

The number of times a columnar-type page is read from the caching tier for the table, without needing to access remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 2. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_col_gbp_indep_pages_found_in_lbp - Group buffer pool column-organized independent data pages found in local buffer pool monitor element

The number of group buffer pool (GBP) independent column-organized pages found in a local buffer pool (LBP) by an agent.

Table 3. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_col_gbp_invalid_pages - Group buffer pool column-organized invalid data pages monitor element

The number of times that a column-organized page is requested from the group buffer pool (GBP) for a table. The page is requested because the version of the page in the local buffer pool (LBP) is invalid. Outside of a Db2® pureScale® environment, this value is null.

Table 4. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_col_gbp_l_reads - Group buffer pool column-organized logical reads monitor element

The number of times that a group buffer pool (GBP) dependent column-organized page is requested from the GBP for a table. The page is requested because a valid version of the page does not exist in the local buffer pool (LBP).

Table 5. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_col_gbp_p_reads - Group buffer pool column-organized physical reads monitor element

The number of times that a group buffer pool (GBP) dependent column-organized page is read into the local buffer pool (LBP) from disk for a table. The page is read from disk into the LBP because the page is not in the GBP.

Table 6. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_col_l_reads - Column-organized logical reads monitor element

The number of column-organized pages that are logically read from the buffer pool for a table.

Table 7. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_col_lbp_pages_found - Local buffer pool column-organized pages found monitor element

The number of times that a column-organized page for a table is present in the local buffer pool (LBP).

Table 8. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_col_p_reads - Column-organized physical reads monitor element

The number of column-organized pages that are physically read for a table.

Table 9. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_data_caching_tier_l_reads - Total logical reads of data pages from the caching tier monitor element

For remote storage table spaces, the total number of data-type pages synchronously read by agents from the caching tier and the remote storage for the table.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 10. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_data_caching_tier_pages_found - Table data pages found in caching tier monitor element

The number of times a data-type page is read from the caching tier for the table, without needing to access remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 11. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_data_gbp_indep_pages_found_in_lbp - Group buffer pool independent data pages found in local buffer pool monitor element

The number of group buffer pool (GBP) independent data pages found in a local buffer pool (LBP) by an agent. Outside of a Db2 pureScale environment, this value is null.

Table 12. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_data_gbp_invalid_pages - GBP invalid data pages for a table monitor element

The number of times that a data page is requested from the group buffer pool (GBP) for a table. The page is requested because the version of the page in the local buffer pool (LBP) is invalid. Outside of a Db2 pureScale environment, this value is null.

Table 13. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested data page is found in the LBP, use the following formula, which uses the values of monitor elements:
(object_data_lbp_pages_found + object_xda_lbp_pages_found) / (object_data_l_reads + object_xda_l_reads)
To determine how often a requested data page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_data_gbp_l_reads + object_xda_gbp_l_reads - object_data_gbp_p_reads - object_xda_gbp_p_reads) /
(object_data_gbp_l_reads + object_xda_gbp_l_reads)

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_data_gbp_l_reads - GBP data logical reads for a table monitor element

The number of times that a group buffer pool (GBP) dependent data page is requested from the GBP for a table. The page is requested because a valid version of the page does not exist in the local buffer pool (LBP). Outside of a Db2 pureScale environment, this value is null.

Table 14. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested data page is found in the LBP, use the following formula, which uses the values of monitor elements:
(object_data_lbp_pages_found + object_xda_lbp_pages_found) / (object_data_l_reads + object_xda_l_reads)
To determine how often a requested data page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_data_gbp_l_reads + object_xda_gbp_l_reads - object_data_gbp_p_reads - object_xda_gbp_p_reads) /
(object_data_gbp_l_reads + object_xda_gbp_l_reads)

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_data_gbp_p_reads - GBP data physical reads for a table monitor element

The number of times that a group buffer pool (GBP) dependent data page is read into the local buffer pool (LBP) from disk for a table. The page is read from disk into the LBP because the page is not in the GBP. Outside of a Db2 pureScale environment, this value is null.

Table 15. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested data page is found in the LBP, use the following formula, which uses the values of monitor elements:
(object_data_lbp_pages_found + object_xda_lbp_pages_found) / (object_data_l_reads + object_xda_l_reads)
To determine how often a requested data page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_data_gbp_l_reads + object_xda_gbp_l_reads - object_data_gbp_p_reads - object_xda_gbp_p_reads) /
(object_data_gbp_l_reads + object_xda_gbp_l_reads)

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_data_lbp_pages_found - LBP data pages found for a table monitor element

The number of times that a data page for a table is present in the local buffer pool (LBP).

Table 16. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested data page is found in the LBP, use the following formula, which uses the values of monitor elements:
(object_data_lbp_pages_found + object_xda_lbp_pages_found) / (object_data_l_reads + object_xda_l_reads)
To determine how often a requested data page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_data_gbp_l_reads + object_xda_gbp_l_reads - object_data_gbp_p_reads - object_xda_gbp_p_reads) /
(object_data_gbp_l_reads + object_xda_gbp_l_reads)

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_data_l_reads - Buffer pool data logical reads for a table monitor element

The number of data pages that are logically read from the buffer pool for a table.

Table 17. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

This monitor element tracks the number of accesses to the following data:
  • Data that is in the buffer pool when the database manager needs to process the page
  • Data that is read into the buffer pool before the database manager can process the page
Calculate the data page hit ratio by using the following formula, which uses the values of monitor elements:
(1 - (object_data_p_reads + object_xda_p_reads) /
(object_data_l_reads + object_xda_l_reads))
If the hit ratio is low, increasing the number of buffer pool pages might improve performance.

object_data_p_reads - Buffer pool physical data reads for a table monitor element

The number of data pages that are physically read for a table.

Table 18. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

Calculate the data page hit ratio by using the following formula, which uses the values of monitor elements:
(1 - (object_data_p_reads + object_xda_p_reads) /
(object_data_l_reads + object_xda_l_reads))
If the hit ratio is low, increasing the number of buffer pool pages might improve performance.

object_index_gbp_indep_pages_found_in_lbp - Group buffer pool independent index pages found in local buffer pool monitor element

The number of Group Buffer Pool (GBP) independent index pages found in Local Buffer Pool (LBP) by agent. Outside of a Db2 pureScale environment, this value is null.

Table 19. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - get index metrics DATA OBJECT METRICS EXTENDED
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list DATA OBJECT METRICS EXTENDED

object_index_gbp_invalid_pages - GBP invalid index pages for an index monitor element

The number of times that an index page is requested from the group buffer pool (GBP) for an index. The page is requested because the version of the page in the local buffer pool (LBP) is invalid. Outside of a Db2 pureScale environment, this value is null.

Table 20. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - get index metrics DATA OBJECT METRICS EXTENDED
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested index page is found in the LBP, use the following formula, which uses the values of monitor elements:

object_index_lbp_pages_found / object_index_l_reads
To determine how often a requested index page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_index_gbp_l_reads - object_index_gbp_p_reads) / object_index_gbp_l_reads

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_index_gbp_l_reads - GBP index logical reads for an index monitor element

The number of times that a group buffer pool (GBP) dependent index page is requested from the GBP for an index. The page is requested because a valid version of the page does not exist in the local buffer pool (LBP). Outside of a Db2 pureScale environment, this value is null.

Table 21. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - get index metrics DATA OBJECT METRICS EXTENDED
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested index page is found in the LBP, use the following formula, which uses the values of monitor elements:

object_index_lbp_pages_found / object_index_l_reads
To determine how often a requested index page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_index_gbp_l_reads - object_index_gbp_p_reads) / object_index_gbp_l_reads

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_index_gbp_p_reads - GBP index physical reads for an index monitor element

The number of times that a group buffer pool (GBP) dependent index page is read into the local buffer pool (LBP) from disk for an index. The page is read from disk into the LBP because the page is not in the GBP. Outside of a Db2 pureScale environment, this value is null.

Table 22. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - get index metrics DATA OBJECT METRICS EXTENDED
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested index page is found in the LBP, use the following formula, which uses the values of monitor elements:

object_index_lbp_pages_found / object_index_l_reads
To determine how often a requested index page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_index_gbp_l_reads - object_index_gbp_p_reads) / object_index_gbp_l_reads

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_index_lbp_pages_found - LBP index pages found for an index monitor element

The number of times that an index page for an index is present in the local buffer pool (LBP).

Table 23. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - get index metrics DATA OBJECT METRICS EXTENDED
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested index page is found in the LBP, use the following formula, which uses the values of monitor elements:

object_index_lbp_pages_found / object_index_l_reads
To determine how often a requested index page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_index_gbp_l_reads - object_index_gbp_p_reads) / object_index_gbp_l_reads

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_index_l_reads - Buffer pool index logical reads for an index monitor element

The number of index pages that are logically read from the buffer pool for an index.

Table 24. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - get index metrics DATA OBJECT METRICS EXTENDED
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list DATA OBJECT METRICS EXTENDED

Usage

This monitor element tracks the number of accesses to the following pages:
  • Index pages that are in the buffer pool when the database manager needs to process the pages
  • Index pages that are read into the buffer pool before the database manager can process the pages
Calculate the index page hit ratio by using the following formula, which uses the values of monitor elements:
(1 - (object_index_p_reads / object_index_l_reads))
If the hit ratio is low, increasing the number of buffer pool pages might improve performance.

object_index_p_reads - Buffer pool index physical reads for an index

The number of index pages that are physically read for an index.

Table 25. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - get index metrics DATA OBJECT METRICS EXTENDED
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list DATA OBJECT METRICS EXTENDED

Usage

Calculate the index page hit ratio by using the following formula, which uses the values of monitor elements:
(1 - (object_index_p_reads / object_index_l_reads))
If the hit ratio is low, increasing the number of buffer pool pages might improve performance.

object_name - Object name monitor element

The name of a database object. Refer to the monitor interface for the type of database object.

Table 28. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change history utilphase
utilstart
Always collected

Usage

For the change history event monitor, if the object_type element is INDEX, PARTIONGROUP, or TABLE, then this is the name of the index, partition group, or table.

When this element is returned by the MON_GET_UTILITY function, this object refers to the table for the automatic dictionary creation (ADC) operation.

object_requested - Requested object monitor element

The type of lock the requester is trying to acquire from the owner. Values can be LOCK for database locks, or TICKET for WLM tickets..

Table 29. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking lock_participants  

object_schema - Object schema monitor element

The schema of a database object. Refer to the monitor interface for the type of database object.

Table 31. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change history utilphase
utilstart
Always collected

Usage

For the change history event monitor, if the object_type element is INDEX or TABLE, then this is the schema of the index or table, otherwise it is an empty string.

When this element is returned by the MON_GET_UTILITY function, this object refers to the table schema for the automatic dictionary creation (ADC) operation.

object_xda_caching_tier_l_reads - Total logical reads of XDA data pages from the caching tier monitor element

For remote storage table spaces, the total number of XML pages synchronously read by agents from the caching tier and the remote storage for the table.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 32. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_xda_caching_tier_pages_found - Table XDA data pages found in caching tier monitor element

The number of times an XML page is read from the caching tier for the table, without needing to access remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 33. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_xda_gbp_indep_pages_found_in_lbp - Group buffer pool XDA independent pages found in local buffer pool monitor element

The number of group buffer pool (GBP) independent XML storage object (XDA) data pages found in a local buffer pool (LBP) by an agent. Outside of a Db2 pureScale environment, this value is null.

Table 34. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

object_xda_gbp_invalid_pages - GBP invalid XDA data pages for a table monitor element

The number of times that a data page for an XML storage object (XDA) is requested from the group buffer pool (GBP) for a table. The page is requested because the version of the page in the local buffer pool (LBP) is invalid. Outside of a Db2 pureScale environment, this value is null.

Table 35. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested XDA page is found in the LBP, use the following formula, which uses the values of monitor elements:

object_xda_lbp_pages_found / object_xda_l_reads
To determine how often a requested XDA page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_xda_gbp_l_reads - object_xda_gbp_p_reads) / object_xda_gbp_l_reads

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_xda_gbp_l_reads - GBP XDA data logical read requests for a table monitor element

The number of times that a group buffer pool (GBP) dependent data page for an XML storage object (XDA) is requested from the GBP for a table. The page is requested because a valid version of the page does not exist in the local buffer pool (LBP). Outside of a Db2 pureScale environment, this value is null.

Table 36. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested XDA page is found in the LBP, use the following formula, which uses the values of monitor elements:

object_xda_lbp_pages_found / object_xda_l_reads
To determine how often a requested XDA page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_xda_gbp_l_reads - object_xda_gbp_p_reads) / object_xda_gbp_l_reads

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_xda_gbp_p_reads - GBP XDA data physical read requests for a table monitor element

The number of times that a group buffer pool (GBP) dependent data page for an XML storage object (XDA) is read into the local buffer pool (LBP) from disk for a table. The page is read from disk into the LBP because the page is not in the GBP. Outside of a Db2 pureScale environment, this value is null.

Table 37. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested XDA page is found in the LBP, use the following formula, which uses the values of monitor elements:

object_xda_lbp_pages_found / object_xda_l_reads
To determine how often a requested XDA page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_xda_gbp_l_reads - object_xda_gbp_p_reads) / object_xda_gbp_l_reads

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_xda_lbp_pages_found - LBP XDA data pages found for a table monitor element

The number of times that an XML storage object (XDA) data page for a table is present in the local buffer pool (LBP).

Table 38. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

To determine how often a requested data page is found in the LBP, use the following formula, which uses the values of monitor elements:
(object_data_lbp_pages_found + object_xda_lbp_pages_found) / (object_data_l_reads + object_xda_l_reads)
To determine how often a requested data page is found in the GBP, use the following formula, which also uses the values of monitor elements:
(object_data_gbp_l_reads + object_xda_gbp_l_reads - object_data_gbp_p_reads - object_xda_gbp_p_reads) /
(object_data_gbp_l_reads + object_xda_gbp_l_reads)

LBP and GBP hit rates are important factors in the overall performance of a cluster caching facility. Using these formulas can help you determine whether the LBP or GBP might be limiting the throughput of your database.

object_xda_l_reads - Buffer pool XDA data logical reads for a table monitor element

The number of data pages for XML storage objects (XDAs) that are logically read from the buffer pool for a table.

Table 39. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

This monitor element tracks the number of accesses to the following data:
  • Data that is in the buffer pool when the database manager needs to process the page.
  • Data that is read into the buffer pool before the database manager can process the page
Calculate the data page hit ratio by using the following formula, which uses the values of monitor elements:
(1 - (object_data_p_reads + object_xda_p_reads) /
(object_data_l_reads + object_xda_l_reads))
If the hit ratio is low, increasing the number of buffer pool pages might improve performance.

object_xda_p_reads - Buffer pool XDA data physical reads for a table monitor element

The number of data pages for XML storage objects (XDAs) that are physically read for a table.

Table 40. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED

Usage

Calculate the data page hit ratio by using the following formula, which uses the values of monitor elements:
(1 - (object_data_p_reads + object_xda_p_reads) /
(object_data_l_reads + object_xda_l_reads))
If the hit ratio is low, increasing the number of buffer pool pages might improve performance.

objtype - Object type monitor element

The type of object for which monitoring data is being reported.

This element is a synonym for the object_type monitor element.

Table 41. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change history utilphase
utilstart
Always collected

Usage Notes

When this element is returned by the change history event monitor, the returned value for the objtype monitor element can be 'DATABASE', 'INDEX', 'PARTITIONGROUP', 'TABLE', 'TABLESPACE', or 'VIEW'.

olap_func_overflows - OLAP Function Overflows monitor element

The number of times that OLAP function data exceeded the available sort heap space.

Table 42. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 43. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
For snapshot monitoring, this counter can be reset.
Table 44. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Connection event_conn Always collected
Database event_db Always collected
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

Usage

At the database level, use this element in conjunction with total_olap_funcs to calculate the percentage of OLAP functions that overflowed to disk. If this percentage is high and the performance of applications using OLAP functions needs to be improved, then you should consider increasing the sort heap size.

At the application level, use this element to evaluate OLAP function performance for individual applications.

open_cursors - Number of Open Cursors monitor element

The number of cursors currently open for an application.

Table 45. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Application dcs_appl Statement
Usage
Use this element to assess how much memory is being allocated. The amount of memory allocated by the database client, Db2 Connect, or the database agent on the target database is related to the number of cursors that are currently open. Knowing this information can help with capacity planning. For example, each open cursor that is doing blocking has a buffer size of RQRIOBLK. If deferred_prepare is enabled, then two buffers will be allocated.

This element does not include cursors that were closed by an early close. An early close occurs when the host database returns the last record to the client. The cursor is closed at the host and gateway, but is still open at the client. Early close cursors can be set using the Call Level Interface.

open_loc_curs - Open Local Cursors monitor element

The number of local cursors currently open for this application, including those cursors counted by open_loc_curs_blk.

Element identifier
open_loc_curs
Element type
gauge
Table 46. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Usage
You may use this element in conjunction with open_loc_curs_blk to calculate the percentage of local cursors that are blocking cursors. If the percentage is low, you may be able to improve performance by improving the row blocking in the application.

For cursors used by remote applications, see open_rem_curs.

open_loc_curs_blk - Open Local Cursors with Blocking monitor element

The number of local blocking cursors currently open for this application.

Element identifier
open_loc_curs_blk
Element type
gauge
Table 47. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Usage
You may use this element in conjunction with open_loc_curs to calculate the percentage of local cursors that are blocking cursors. If the percentage is low, you may be able to improve performance by improving the row blocking in the application:
  • Check the pre-compile options for record blocking for treatment of ambiguous cursors
  • Redefine cursors to allow for blocking (for example, if possible, specify FOR FETCH ONLY on your cursors).

rej_curs_blk and acc_curs_blk provide additional information that may help you tune your configuration parameters to improve row blocking in your application.

For blocking cursors used by remote applications, see open_rem_curs_blk.

open_rem_curs - Open Remote Cursors monitor element

The number of remote cursors currently open for this application, including those cursors counted by open_rem_curs_blk.

Element identifier
open_rem_curs
Element type
gauge
Table 48. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Usage
You may use this element in conjunction with open_rem_curs_blk to calculate the percentage of remote cursors that are blocking cursors. If the percentage is low, you may be able to improve performance by improving the row blocking in the application. See open_rem_curs_blk for more information.

For the number of open cursors used by applications connected to a local database, see open_loc_curs.

open_rem_curs_blk - Open Remote Cursors with Blocking monitor element

The number of remote blocking cursors currently open for this application.

Element identifier
open_rem_curs_blk
Element type
gauge
Table 49. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Usage
You can use this element in conjunction with open_rem_curs to calculate the percentage of remote cursors that are blocking cursors. If the percentage is low, you may be able to improve performance by improving the row blocking in the application:
  • Check the pre-compile options for record blocking for treatment of ambiguous cursors
  • Redefine cursors to allow for blocking (for example, if possible, specify FOR FETCH ONLY on your cursors).

rej_curs_blk and acc_curs_blk provide additional information that may help you tune your configuration parameters to improve row blocking in your application.

For the number of open blocking cursors used by applications connected to a local database see open_loc_curs_blk.

os_level - Operating system level monitor element

The modification level of the operating system running on this host. Reported for Linux® systems only.

Table 53. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected

outbound_appl_id - Outbound Application ID monitor element

This identifier is generated when the application connects to the DRDA host database. It is used to connect the Db2 Connect gateway to the host, while the appl_id monitor element is used to connect a client to the Db2 Connect gateway.

Element identifier
outbound_appl_id
Element type
information
Table 57. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Application dcs_appl_info Basic

Usage

You may use this element in conjunction with appl_id to correlate the client and server parts of the application information.

This identifier is unique across the network.

This element will be blank when the gateway concentrator is on, or if the DCS application is not in a logical unit of work.
Format
Network.LU Name.Application instance
Example
CAIBMTOR.OSFDBM0.930131194520

outbound_bytes_received - Outbound Number of Bytes Received monitor element

The number of bytes received by the Db2 Connect gateway from the host, excluding communication protocol usage (for example, TCP/IP). For the data transmission level: Number of bytes received by the Db2 Connect gateway from the host during the processing of all the statements that used this number of data transmissions.

Table 58. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Database dcs_dbase Basic
DCS Application dcs_appl Basic
DCS Statement dcs_stmt Statement
Data Transmission stmt_transmissions Statement

For snapshot monitoring at the statement level, this counter cannot be reset. This counter can be reset at other levels.

Usage

Use this element to measure the throughput from the host databases to the Db2 Connect gateway.

outbound_bytes_received_bottom - Minimum Outbound Number of Bytes Received monitor element

The lowest number of bytes received per statement or chain by the Db2 Connect gateway from the host during the processing of all the statements or chains against this DCS database, or in this DCS application, that used this number of data transmissions.

Table 59. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Data Transmission stmt_transmissions Statement
Usage
Use this element in conjunction with "outbound number of bytes received" as yet another parameter that illustrates the throughput from the host database to the Db2 Connect gateway.

outbound_bytes_received_top - Maximum Outbound Number of Bytes Received monitor element

Maximum number of bytes received per statement or chain by the Db2 Connect gateway from the host during the processing of all the statements or chains against this DCS database, or in this DCS application, that used this number of data transmissions.

Table 60. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Data Transmission stmt_transmissions Statement
Usage
Use this element in conjunction with "outbound number of bytes received" as yet another parameter that illustrates the throughput from the host database to the Db2 Connect gateway.

outbound_bytes_sent - Outbound Number of Bytes Sent monitor element

The number of bytes sent by the Db2 Connect gateway to the host, excluding communication protocol usage (for example, TCP/IP). For the data transmission level: Number of bytes sent by the Db2 Connect gateway to the host during the processing of all the statements that used this number of data transmissions.

Table 61. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Database dcs_dbase Basic
DCS Application dcs_appl Basic
DCS Statement dcs_stmt Statement
Data Transmission stmt_transmissions Statement
For snapshot monitoring at the statement level, this counter cannot be reset. This counter can be reset at other levels.
Usage
Use this element to measure the throughput from the Db2 Connect gateway to the host database.

outbound_bytes_sent_bottom - Minimum Outbound Number of Bytes Sent monitor element

The lowest number of bytes sent per statement or chain by the Db2 Connect gateway to the host during the processing of all the statements or chains against this DCS database, or in this DCS application, that used this number of data transmissions.

Table 62. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Data Transmission stmt_transmissions Statement
Usage
Use this element in conjunction with "outbound number of bytes sent" as yet another parameter that illustrates the throughput from the Db2 Connect Gateway to the host database.

outbound_bytes_sent_top - Maximum Outbound Number of Bytes Sent monitor element

Maximum number of bytes sent per statement or chain by the Db2 Connect gateway to the host during the processing of all the statements or chains against this DCS database, or in this DCS application, that used this number of data transmissions.

Table 63. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Data Transmission stmt_transmissions Statement
Usage
Use this element in conjunction with "outbound number of bytes sent" as yet another parameter that illustrates the throughput from the Db2 Connect Gateway to the host database.

outbound_comm_address - Outbound Communication Address monitor element

This is the communication address of the target database. For example, it could be an IP address and port number for TCP/IP.

Element identifier
outbound_comm_address
Element type
information
Table 64. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
DCS Application dcs_appl_info Basic
Usage
Use this element for problem determination on DCS applications.

outbound_comm_protocol - Outbound Communication Protocol monitor element

The communication protocol used between the Db2 Connect gateway and the host.

Table 65. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Application dcs_appl_info Basic

Usage

Use this element for problem determination on DCS applications. The valid value is:
  • SQLM_PROT_TCPIP

outbound_sequence_no - Outbound Sequence Number monitor element

This element will be blank when the gateway concentrator is on, or if the DCS application is not in a logical unit of work.

Table 66. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Application dcs_appl_info Basic

overflow_accesses - Accesses to overflowed records monitor element

The number of accesses (reads and writes) to overflowed rows of this table.

Table 67. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics Always collected
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS BASE
Table 68. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
For snapshot monitoring, this counter can be reset.
Table 69. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Tables event_table Always collected

Usage

Overflowed rows indicate that data fragmentation has occurred. If this number is high, you may be able to improve table performance by reorganizing the table using the REORG utility, which cleans up this fragmentation.

A row overflows if it is updated and no longer fits in the data page where it was originally written. This usually happens as a result of an update of a VARCHAR or an ALTER TABLE statement.