package_id - Package identifier monitor element

A unique identifier for the package.

Table 1. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of work Reported in the package list. Always collected

Usage

The value of this element matches a value from column PKGID of view SYSCAT.PACKAGES.

package_elapsed_time - Package elapsed time monitor element

The elapsed time spent executing sections within the package. Value is in milliseconds.

Table 2. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of work Reported in the package list. Always collected

package_list_count - Package list count monitor element

The number of entries that are present within the package listing for a particular unit of work

Table 3. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch

Unit of work

-

Always collected

package_list_exceeded - Package list exceeded monitor element

Indicates whether the number of packages used within the unit of work has exceeded the capacity of the package list. Possible values are YES and NO.

Table 4. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch

Unit of work

-

Always collected

package_list_size - Size of package list monitor element

The count of the number of package identifiers included in the package list.

Table 5. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of Work uow  

package_name - Package name monitor element

The name of the package that contains the SQL statement.

Table 7. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
DCS Statement dcs_stmt Statement
Table 8. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks with Details1 event_detailed_dlconn -
Statements event_stmt -
Activities event_activitystmt Always collected
Package cache - Always collected
1
This event monitor has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR for locking statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.

Usage

You may use this element to help identify the application program and the SQL statement that is executing.

package_schema - Package schema monitor element

The schema name of the package associated with an SQL statement.

Table 10. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Package cache - Always collected

package_version_id - Package version monitor element

For a particular package name and creator, there might be more than one version. The package version identifies the version identifier of the package that contains the SQL statement currently executing. The version of a package is determined at precompile (PREP) time of the embedded SQL program using the VERSION keyword. If not specified at precompile time the package version has a value of "" (empty string).
Table 12. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
Table 13. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Statements event_stmt Always collected
Activities event_activitystmt Always collected
Package cache - Always collected

Usage

Use this element to help identify the package and the SQL statement that is currently executing.

page_allocations - Page allocations monitor element

Number of pages that have been allocated to the index.

Table 18. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - Get index metrics Always collected

page_reorgs - Page reorganizations monitor element

The number of page reorganizations executed for a table.

Table 19. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics Always collected
Table 20. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
For snapshot monitoring, this counter can be reset.
Table 21. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Tables event_table Always collected

Usage

Although a page might have enough space, the page could become fragmented in the following situations:
  • When a new row is inserted
  • When an existing row is updated, and the update results in an increased record size
A page might require reorganization when it becomes fragmented. Reorganization moves all fragmented space to a contiguous area, where the new record can be written. Such a page reorganization (page reorg) might require thousands of instructions. It also generates a log record of the operation.

Too many page reorganizations can result in less than optimal insert performance. You can use the REORG TABLE utility to reorganize a table and eliminate fragmentation. You can also use the APPEND parameter for the ALTER TABLE statement to indicate that all inserts are appended at the end of a table to avoid page reorganizations.

In situations where updates to rows causes the row length to increase, the page may have enough space to accommodate the new row, but a page reorg may be required to defragment that space. If the page does not have enough space for the new larger row, an overflow record is created causing overflow_accesses during reads. You can avoid both situations by using fixed length columns instead of varying length columns.

page_reclaims_x - Page reclaims exclusive access monitor element

The number of times a page related to the object was reclaimed by another member in the Db2® pureScale® instance before its planned release, where the member that reclaimed the page required exclusive access.

Table 22. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected

Usage

Page reclaims related to internally maintained object space maps are counted separately.

page_reclaims_s - Page reclaims shared access monitor element

The number of times a page related to the object was reclaimed by another member in the Db2 pureScale instance before its planned release, where the member that reclaimed the page required shared access.

Table 23. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected

Usage

Page reclaims related to internally maintained object space maps are counted separately.

page_reclaims_initiated_x - Page reclaims initiated exclusive access monitor element

The number of times a page accessed in exclusive mode caused the page to be reclaimed from another member. Page reclaims related to internally maintained object space maps are counted separately.

Table 24. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected

page_reclaims_initiated_s - Page reclaims initiated shared access monitor element

The number of times a page accessed in shared mode caused the page to be reclaimed from another member. Page reclaims related to internally maintained object space maps are counted separately.

Table 25. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected

pages_from_block_ios - Total number of pages read by block I/O monitor element

The total number of pages read by block I/O into the block area of the buffer pool.

Table 26. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONTAINER table function - Get table space container metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 27. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool Buffer Pool

Usage

If block-based buffer pool is enabled, this element reports the total number of pages read by block I/O. Otherwise, this element returns 0.

To calculate the average number of pages sequentially prefetched per block-based I/O, divide the value of the pages_from_block_ios monitor element by the value of the block_ios monitor element. If this value is much less than the BLOCKSIZE option you have defined for the block-based buffer pool in the CREATE BUFFERPOOL or ALTER BUFFERPOOL statement, then block-based I/O is not being used to its full advantage. One possible cause for this is a mismatch between the extent size for the table space being sequentially prefetched and the block size of the block-based buffer pool.

pages_from_vectored_ios - Total number of pages read by vectored I/O monitor element

The total number of pages read by vectored I/O into the page area of the buffer pool.

Table 28. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONTAINER table function - Get table space container metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 29. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool Buffer Pool

pages_merged - Pages merged monitor element

Number of index pages that have been merged.

Table 30. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - Get index metrics Always collected

parent_activity_id - Parent activity ID monitor element

The unique ID of the activity's parent activity within the parent activity's unit of work. If there is no parent activity, the value of this monitor element is NULL.

Table 34. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity Always collected

Usage

Use this element along with the parent_uow_id element and appl_id element to uniquely identify the parent activity of the activity described in this activity record.

parent_uow_id - Parent unit of work ID monitor element

The unique unit of work identifier within an application handle. The ID of the unit of work in which the activity's parent activity originates. If there is no parent activity, the value is NULL.

Table 36. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity Always collected

Usage

Use this element along with the parent_activity_id element and appl_id element to uniquely identify the parent activity of the activity described in this activity record.

partial_record - Partial Record monitor element

Indicates that an event monitor record is only a partial record.

Table 37. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db -
Tables event_table -
Tablespaces event_tablespace -
Bufferpools event_bufferpool -
Connection event_conn -
Statements event_stmt -
Statements event_subsection -
Transactions event_xact -
Activities event_activity -

Usage

Most event monitors do not output their results until database deactivation. You can use the FLUSH EVENT MONITOR <monitorName> statement to force monitor values to the event monitor output writer. This allows you to force event monitor records to the writer without needing to stop and restart the event monitor. This element indicates whether an event monitor record was the result of flush operation and so is a partial record.

Flushing an event monitor does not cause its values to be reset. This means that a complete event monitor record is still generated when the event monitor is triggered.

At the event_activity logical data grouping, the possible values of partial_record monitor element are:
0
The activity record was generated normally at the end of activity.
1
The activity record was generated as a result of calling the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure.
2
Information is missing for this activity because not enough storage was available to create the records. Information may be missing from the event_activity, event_activitystmt, or event_activityvals records.

participant_no - Participant within Deadlock monitor element

A sequence number uniquely identifying this participant within this deadlock.

Element identifier
participant_no
Element type
information
Table 38. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks event_dlconn Always collected
Deadlocks with Details event_detailed_dlconn Always collected
Usage
Use this in your monitoring application to correlate deadlock connection event records with deadlock event records.

participant_no_holding_lk - Participant Holding a Lock on the Object Required by Application monitor element

The participant number of the application that is holding a lock on the object that this application is waiting to obtain.

Element identifier
participant_no_holding_lk
Element type
information
Table 39. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks event_dlconn Always collected
Deadlocks with Details event_detailed_dlconn Always collected
Usage
This element can help you determine which applications are in contention for resources.

participant_type - Participant type monitor element

The type of lock participant which can be either Requestor or Owner.

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

partition_key - Partitioning key monitor element

The partitioning key for the event monitor tables. This value is chosen so that each event monitor process inserts data into the database partition on which the process is running; that is, insert operations are performed locally on the database partition where the event monitor process is running.

Table 41. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Threshold Violation event_thresholdviolations  
Threshold Violation control  
Statistics event_qstats  
Statistics event_scstats  
Statistics event_superclassstats  
Statistics event_histogrambin  
Statistics event_wcstats  
Statistics event_wlstats  
Statistics control  
Locking lock  
Locking lock_participants  
Locking lock_participant_activities  
Locking lock_activity_values  
Locking control  
Package Cache pkgcache_metrics  
Package Cache pkgcache_stmt_args  
Package Cache control  
Unit of Work uow  
Unit of Work uow_metrics  
Unit of Work uow_package_list  
Unit of Work uow_executable_list  
Unit of Work control  
Activities event_activity  
Activities event_activitystmt  
Activities event_activityvals  
Activities event_activitymetrics  
Activities control  
Change History changesummary
dbdbmcfg
ddlstmtexec
evmonstart
regvar
txncompletion
utillocation
utilphase
utilstart
utilstop
Always collected

partition_number - Partition Number monitor element

This element is only used in the target SQL tables by write-to-table event monitors in a partitioned database environment or Db2 pureScale environment. This value indicates the number of the member where event monitor data is inserted.

Table 42. Table Function Monitoring Information
Table Function Monitor Element Collection Level
DB_MEMBERS table function Always collected
Table 43. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics Always collected

passthru_time - Pass-Through Time monitor element

This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to PASSTHRU statements from all applications or a single application running on this federated server instance since the start of the federated server instance or the last reset of the database monitor counters. The monitor stores the most recent of the values. The response time is measured as the difference between the time the federated server submits a PASSTHRU statement to the data source, and the time it takes the data source to respond, indicating that the statement has been processed.
Table 44. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase_remote Timestamp
Application appl_remote Timestamp
For snapshot monitoring, this counter can be reset.

Usage

Use this element to determine how much actual time is spent at this data source processing statements in pass-through mode.

passthrus - Pass-Through monitor element

This element contains a count of the total number of SQL statements that the federated server has passed through directly to this data source on behalf of any application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest.

Table 45. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase_remote Basic
Application appl_remote Basic
For snapshot monitoring, this counter can be reset.
Usage
Use this element to determine what percentage of your SQL statements can be handled natively by the federated server, and what percentage requires pass-through mode. If this value is high, you should determine the cause and investigate ways to better use the native support.

past_activities_wrapped - Past activities list wrapped monitor element

Indicates whether the activities list has wrapped. The default limit on the number of past activities to be kept by any one application is 250. This default can be overridden using the DB2_MAX_INACT_STMTS registry variable. Users may want to choose a different value for the limit to increase or reduce the amount of system monitor heap used for inactive statement information.
Table 46. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking lock_participants  

peer_window - Peer window monitor element

Note: The hadr_peer_window and peer_window monitor elements are aliases that represent the same information in different monitoring interfaces. hadr_peer_window is returned by the snapshot monitor interfaces, and peer_window is returned by the MON_GET_HADR table function and db2pd interfaces.

The value of the HADR_PEER_WINDOW database configuration parameter. Units are milliseconds.

Table 48. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

peer_window_end - Peer window end monitor element

Note: The hadr_peer_window_end and peer_window_end monitor elements are aliases that represent the same information in different monitoring interfaces. hadr_peer_window_end is returned by the snapshot monitor interfaces, and peer_window_end is returned by the MON_GET_HADR table function and db2pd interfaces.

End time of current peer window. The value is NULL if peer window is not enabled.

Table 49. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

phase_start_event_id - Phase start event ID monitor element

The EVENT_ID of corresponding UTILPHASESTART.

Table 50. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History UTILPHASE Always collected

Usage

For the change history event monitor:
  • If the event is the stopping of a utility phase or processing stage (UTILPHASESTOP), this is the EVENT_ID of the corresponding start of the utility phase (UTILPHASESTART), otherwise -1.

Use with the PHASE_START_EVENT_TIMESTAMP and member elements to associate the phase stop record with the corresponding start record.

phase_start_event_timestamp - Phase start event timestamp monitor element

Time of the corresponding UTILPHASESTART

Table 51. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History UTILPHASE Always collected

Usage

For the change history event monitor:
  • If the event is the stopping of a utility phase or processing stage (UTILPHASESTOP), this is the time of the corresponding start of the utility phase (UTILPHASESTART), otherwise empty.
Use with the PHASE_START_EVENT_ID and member elements to associate the phase stop record with the corresponding start record.

physical_move_time - Physical extent move time monitor element

During extent movement, this monitor element shows the cumulative time taken to do disk I/O on an extent to move it from one position to another. The value is given in milliseconds.

Table 52. Table function monitoring information
Table function Monitor element collection level
MON_GET_EXTENT_MOVEMENT_STATUS - get extent movement progress Always collected

piped_sorts_accepted - Piped Sorts Accepted monitor element

The number of piped sorts that have been accepted.

Table 53. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic
For snapshot monitoring, this counter can be reset.
Usage
Each active sort on the system allocates memory, which may result in sorting taking up too much of the available system memory.
When the number of accepted piped sorts is low compared to the number requested, you can improve sort performance by adjusting one or both of the following configuration parameters:
  • sortheap
  • sheapthres
If piped sorts are being rejected, you might consider decreasing your sort heap or increasing your sort heap threshold. You should be aware of the possible implications of either of these options. If you increase the sort heap threshold, then there is the possibility that more memory will remain allocated for sorting. This could cause the paging of memory to disk. If you decrease the sort heap, you might require an extra merge phase that could slow down the sort.

piped_sorts_requested - Piped Sorts Requested monitor element

The number of piped sorts that have been requested.

Table 54. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic
For snapshot monitoring, this counter can be reset.
Usage
Each active sort on the system allocates memory, which may result in sorting taking up too much of the available system memory.

The sort list heap (sortheap) and sort heap threshold (sheapthres) configuration parameters help to control the amount of memory used for sort operations. These parameters are also used to determine whether a sort will be piped.

Since piped sorts may reduce disk I/O, allowing more piped sorts can improve the performance of sort operations and possibly the performance of the overall system. A piped sort is not be accepted if the sort heap threshold will be exceeded when the sort heap is allocated for the sort. See piped_sorts_accepted for more information if you are experiencing piped sort rejections.

The SQL EXPLAIN output will show whether the optimizer requests a piped sort.

pkg_cache_inserts - Package cache inserts monitor element

The total number of times that a requested section was not available for use and had to be loaded into the package cache. This count includes any implicit prepares performed by the system.

Table 55. Table Function Monitoring Information
Table Function Monitor Element Collection Level
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_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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 (reported in DETAILS XML document) REQUEST METRICS BASE
Table 56. 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 57. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

In conjunction with the pkg_cache_lookups monitor element, use this monitor element to calculate the package cache hit ratio using the following formula:
 
    1 - (Package Cache Inserts / Package Cache Lookups)

pkg_cache_lookups - Package cache lookups monitor element

The package cache lookups monitor element counts the number of times that an application looked for a section or package in the package cache. At a database level, it indicates the overall number of references since the database was started, or monitor data was reset. This counter includes the cases where the section is already loaded in the cache and when the section has to be loaded into the cache. In a concentrator environment where agents are being associated with different applications, additional package cache lookups may be required as a result of a new agent not having the required section or package available in local storage.
Table 58. Table Function Monitoring Information
Table Function Monitor Element Collection Level
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_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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 (reported in DETAILS XML document) REQUEST METRICS BASE
Table 59. 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 60. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

To calculate the package cache hit ratio use the following formula:
 
    1 - (Package Cache Inserts / Package Cache Lookups)

The package cache hit ratio tells you whether or not the package cache is being used effectively. If the hit ratio is high (more than 0.8), the cache is performing well. A smaller hit ratio may indicate that the package cache should be increased.

You will need to experiment with the size of the package cache to find the optimal number for the pckcachesz configuration parameter. For example, you might be able to use a smaller package cache size if there is no increase in the pkg_cache_inserts element when you decrease the size of the cache. Decreasing the package cache size frees up system resources for other work. It is also possible that you could improve overall system performance by increasing the size of the package cache if by doing so, you decrease the number of pkg_cache_inserts. This experimentation is best done under full workload conditions.

You can use this element with ddl_sql_stmts to determine whether or not the execution of DDL statements is impacting the performance of the package cache. Sections for dynamic SQL statements can become invalid when DDL statements are executed. Invalid sections are implicitly prepared by the system when next used. The execution of a DDL statement could invalidate a number of sections and the resulting additional processing time required when preparing those sections could significantly impact performance. In this case, the package cache hit ratio reflects the implicit recompilation of invalid sections. It does not reflect the insertion of new sections into the cache, so increasing the size of the package cache will not improve overall performance. You might find it less confusing to tune the cache for an application on its own before working in the full environment.

It is necessary to determine the role that DDL statements are playing in the value of the package cache hit ratio before deciding on what action to take. If DDL statements rarely occur, then cache performance may be improved by increasing its size. If DDL statements are frequent, then improvements may require that you limit the use of DDL statements (possibly to specific time periods).

The static_sql_stmts and dynamic_sql_stmts counts can be used to help provide information about the quantity and type of sections being cached.

Note: You may want to use this information at the database level to calculate the average package cache hit ratio all each applications. You should look at this information at an application level to find out the exact package cache hit ratio for a given application. It may not be worthwhile to increase the size of the package cache in order to satisfy the cache requirements of an application that only executes infrequently.

pkg_cache_num_overflows - Package Cache Overflows monitor element

The number of times that the package cache overflowed the bounds of its allocated memory.

Table 61. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_DATABASE table function - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics Always collected
Table 62. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 63. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

Use this element with the pkg_cache_size_top monitor element to determine whether the size of the package cache needs to be increased to avoid overflowing.

pkg_cache_size_top - Package cache high watermark monitor element

The largest size reached by the package cache.

Note: The pkg_cache_size_top monitor element is deprecated. Using this monitor element will not generate an error. However, it does not return a valid value and might be removed in a future release.
Table 64. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 65. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

If the package cache overflowed, then this element contains the largest size reached by the package cache during the overflow.

Check the pkg_cache_num_overflows monitor element to determine if such a condition occurred.

You can determine the minimum size of the package cache required by your workload by:
   maximum package cache size / 4096
Rounding the result up to a whole number, indicates the minimum number of 4K pages required by the package cache to avoid overflow.

planid - Query plan ID monitor element

The hash key value which identifies a query plan for a section.

The planid monitor element tracks important performance sensitive aspects of the access plan. Such aspects include the list and layout of access plan operators, identifiers of the objects that are being accessed, the number of each type of predicate for each operator, and performance sensitive operator arguments.

Table 67. Event monitoring information
Event type Logical data grouping Monitor switch
Activities event_activitystmt Always collected
Package cache pkgcache Always collected

Usage

Use this monitor element with the stmtid and semantic_env_id monitor elements to detect changes in access plan that might affect performance.

pool_async_caching_tier_page_read_time - Buffer pool caching tier page read time monitor element

The total amount of time spent by prefetchers reading pages of all types from the caching tier for remote table space containers. Time is measured in milliseconds. This excludes time spent reading from remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 68. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_caching_tier_pages_found - Buffer pool asynchronous column-organized pages found in caching tier monitor element

The number of times an asynchronous column-organized page read request was serviced by using the caching tier and excluding the remote storage layer.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 69. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_data_caching_tier_pages_found - Buffer pool asynchronous data pages found in caching tier monitor element

The number of times an asynchronous data page read request was serviced by using the caching tier and excluding the remote storage layer.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 70. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_index_caching_tier_pages_found - Buffer pool asynchronous index pages found in caching tier monitor element

The number of times an asynchronous index page read request was serviced by using the caching tier and excluding the remote storage layer.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 71. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_xda_caching_tier_pages_found - Buffer pool asynchronous XDA data pages found in caching tier monitor element

The number of times an asynchronous XML-type page read request was serviced by using the caching tier and excluding the remote storage layer.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 72. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_caching_tier_reads - Buffer pool asynchronous column-organized caching tier page reads monitor element

The number of column-organized pages that have been asynchronously read by prefetchers from the caching tier. This excludes pages that were serviced the buffer pool and remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 73. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_data_caching_tier_reads - Buffer pool asynchronous data caching tier page reads monitor element

The number of data pages that have been asynchronously read by prefetchers from the caching tier. This excludes pages that were serviced using the buffer pool and remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 74. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_index_caching_tier_reads - Buffer pool asynchronous index caching tier page reads monitor element

The number of index pages that have been asynchronously read by prefetchers from the caching tier. This excludes pages that were serviced using the buffer pool and remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 75. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_xda_caching_tier_reads - Buffer pool asynchronous XML caching tier page reads monitor element

The number of XML pages that have been asynchronously read by prefetchers from the caching tier. This excludes pages that were serviced using the buffer pool and remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 76. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_gbp_indep_pages_found_in_lbp - Asynchronous 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 by a prefetcher.

Table 77. Table function monitoring information
Table function Monitor element collection level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_gbp_invalid_pages - Asynchronous group buffer pool invalid data pages monitor element

The number of times a column-organized page was attempted to be read from the group buffer pool by a prefetcher because the page was invalid in the local buffer pool.

Table 78. Table function monitoring information
Table function Monitor element collection level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_gbp_l_reads - Asynchronous group buffer pool column-organized logical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent column-organized page was attempted to be read from the group buffer pool by a prefetcher because the page was either invalid or not present in the local buffer pool.

Table 79. Table function monitoring information
Table function Monitor element collection level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_gbp_p_reads - Asynchronous group buffer pool column-organized physical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent column-organized page was read into the local buffer pool by a prefetcher from disk because it was not found in the GBP.

Table 80. Table function monitoring information
Table function Monitor element collection level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_lbp_pages_found - Asynchronous local buffer pool column-organized pages found monitor element

The number of times a data page was present in the local buffer pool when a prefetcher attempted to access it.

Table 81. Table function monitoring information
Table function Monitor element collection level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_read_reqs - Buffer pool asynchronous column-organized read requests monitor element

The number of asynchronous column-organized read requests by the prefetcher to the operating system.

Table 82. Table function monitoring information
Table function Monitor element collection level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

To calculate the average number of column-organized pages in each read request, use the following formula:
 pool_async_col_reads / pool_async_col_read_reqs

This average can help you determine the average read I/O size used by the prefetcher.

The maximum size of a prefetcher read I/O is the value specified on the EXTENTSIZE option of the CREATE TABLESPACE statement for the table space involved, but it can be smaller under some circumstances:
  • when some pages of the extent are already in the buffer pool
  • when exceeding operating system capabilities
  • when the EXTENTSIZE option value is very large, such that doing a large I/O would be detrimental to overall performance

pool_async_col_reads - Buffer pool asynchronous column-organized reads monitor element

Indicates the number of column-organized pages which have been asynchronously read by prefetchers from the table space containers (physical) for all types of table spaces.

Table 83. Table function monitoring information
Table function Monitor element collection level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_col_writes - Buffer pool asynchronous column-organized writes monitor element

The number of times a buffer pool data page was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher might have written dirty pages to disk to make space for the pages being prefetched.

Table 84. Table function monitoring information
Table function Monitor element collection level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

You can use this element with the pool_col_writes monitor element to calculate the number of columnar page writes that were performed synchronously (that is, physical data page writes that were performed by database manager agents). Use the following formula:
  pool_col_writes - pool_async_col_writes

By comparing the ratio of asynchronous to synchronous writes, you can gain insight into how well the buffer pool page cleaners are performing. This ratio can be helpful when you are tuning the num_iocleaners configuration parameter.

pool_async_data_gbp_indep_pages_found_in_lbp - Group buffer pool independent data pages found by asynchronous EDUs in a local buffer pool monitor element monitor element

The number of group buffer pool (GBP) independent data pages found in a local buffer pool by asynchronous EDUs.

Table 85. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_data_gbp_invalid_pages - Asynchronous group buffer pool invalid data pages monitor element

The number of times a data page was attempted to be read from the group buffer pool by a prefetcher because the page was invalid in the local buffer pool. Outside of a Db2 pureScale environment, this value is null.

Table 86. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) data page hit ratios can be computed as follows:
GBP = ( pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads ) / pool_async_data_gbp_l_reads

Buffer pool hit rates are important factors in the overall performance of the IBM® Db2 pureScale Feature. Using this formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_data_gbp_l_reads - Asynchronous group buffer pool data logical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent data page was attempted to be read from the group buffer pool by a prefetcher because the page was either invalid or not present in the local buffer pool. Outside of a Db2 pureScale environment, this value is null.

Table 87. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics Always collected
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) data page hit ratios can be computed as follows:
GBP = ( pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads ) / pool_async_data_gbp_l_reads

Buffer pool hit rates are important factors in the overall performance of the IBM Db2 pureScale Feature. Using this formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_data_gbp_p_reads - Asynchronous group buffer pool data physical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent data page was read into the local buffer pool by a prefetcher from disk because it was not found in the GBP. Outside of a Db2 pureScale environment, this value is null.

Table 88. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) data page hit ratios can be computed as follows:
GBP = ( pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads ) / pool_async_data_gbp_l_reads

Buffer pool hit rates are important factors in the overall performance of the IBM Db2 pureScale Feature. Using this formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_data_lbp_pages_found - Asynchronous local buffer pool data pages found monitor element

The number of times a data page was present in the local buffer pool when a prefetcher attempted to access it.

Table 89. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) data page hit ratios can be computed as follows:
GBP = ( pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads ) / pool_async_data_gbp_l_reads

Buffer pool hit rates are important factors in the overall performance of the IBM Db2 pureScale Feature. Using this formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_data_read_reqs - Buffer pool asynchronous read requests monitor element

The number of asynchronous read requests by the prefetcher to the operating system. These requests are typically large block I/Os of multiple pages.

Table 90. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 91. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 92. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected

Usage

To calculate the average number of data pages in each read request, use the following formula:
 pool_async_data_reads / pool_async_data_read_reqs

This average can help you determine the average read I/O size used by the prefetcher. This data can also be helpful in understanding the large block I/O requirements of the measured workload.

The maximum size of a prefetcher read I/O is the value specified on the EXTENTSIZE option of the CREATE TABLESPACE statement for the table space involved, but it can be smaller under some circumstances:
  • when some pages of the extent are already in the buffer pool
  • when exceeding operating system capabilities
  • when the EXTENTSIZE option value is very large, such that doing a large I/O would be detrimental to overall performance

pool_async_data_reads - Buffer pool asynchronous data reads monitor element

Indicates the number of data pages which have been asynchronously read by prefetchers from the table space containers (physical) for all types of table spaces.

Table 93. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 94. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 95. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected

Usage

You can use this element with pool_data_p_reads to calculate the number of physical reads that were performed synchronously (that is, physical data page reads that were performed by database manager agents). Use the following formula to calculate the number of synchronous data reads:

# of sync data reads = pool_data_p_reads + pool_temp_data_p_reads - pool_async_data_reads
By comparing the ratio of asynchronous reads to total physical reads, you can gain insight into how well the prefetchers are working. This element can be helpful when you are tuning the num_ioservers configuration parameter. You can calculate the ratio of asynchronous reads to physical reads by using the following formula:
(pool_async_data_reads + pool_async_index_reads)/
(pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads)

pool_async_data_writes - Buffer pool asynchronous data writes monitor element

The number of times a buffer pool data page was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher may have written dirty pages to disk to make space for the pages being prefetched.

Table 96. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 97. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 98. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected
Usage
You can use this element with the pool_data_writes monitor element to calculate the number of physical write requests that were performed synchronously (that is, physical data page writes that were performed by database manager agents). Use the following formula:
  pool_data_writes - pool_async_data_writes

By comparing the ratio of asynchronous to synchronous writes, you can gain insight into how well the buffer pool page cleaners are performing. This ratio can be helpful when you are tuning the num_iocleaners configuration parameter.

pool_async_index_gbp_indep_pages_found_in_lbp - Group buffer pool independent index pages found by asynchronous EDUs in a local buffer pool monitor element monitor element

The number of group buffer pool (GBP) independent index pages found in a local buffer pool by asynchronous EDUs.

Table 99. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_index_gbp_invalid_pages - Asynchronous group buffer pool invalid index pages monitor element

The number of times an index page was attempted to be read from the group buffer pool by a prefetcher because the page was invalid in the local buffer pool. Outside of a Db2 pureScale environment, this value is null.

Table 100. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) index page hit ratios can be computed as follows:
GBP = ( pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads

Buffer pool hit rates are important factors in the overall performance of the Db2 pureScale instance. Using the previously mentioned formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_index_gbp_l_reads - Asynchronous group buffer pool index logical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent index page was attempted to be read from the group buffer pool by a prefetcher because the page was either invalid or not present in the local buffer pool. Outside of a Db2 pureScale environment, this value is null.

Table 101. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) index page hit ratios can be computed as follows:
GBP = ( pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads

Buffer pool hit rates are important factors in the overall performance of the Db2 pureScale instance. Using the previously mentioned formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_index_gbp_p_reads - Asynchronous group buffer pool index physical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent index page was read into the local buffer pool by a prefetcher from disk because it was not found in the GBP. Outside of a Db2 pureScale environment, this value is null.

Table 102. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) index page hit ratios can be computed as follows:
GBP = ( pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads

Buffer pool hit rates are important factors in the overall performance of the Db2 pureScale instance. Using the previously mentioned formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_index_lbp_pages_found - Asynchronous local buffer pool index pages found monitor element

The number of times an index page was present in the local buffer pool when a prefetcher attempted to access it.

Table 103. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) index page hit ratios can be computed as follows:
GBP = ( pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads

Buffer pool hit rates are important factors in the overall performance of the Db2 pureScale instance. Using the previously mentioned formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_index_read_reqs - Buffer pool asynchronous index read requests monitor element

The number of asynchronous read requests for index pages.

Table 104. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 105. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 106. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected
Usage
To calculate the number of index pages read per asynchronous request, use the following formula:
  pool_async_index_reads / pool_async_index_read_reqs
This average can help you determine the amount of asynchronous I/O done for index pages in each interaction with the prefetcher.

pool_async_index_reads - Buffer pool asynchronous index reads monitor element

Indicates the number of index pages which have been asynchronously read by prefetchers from the table space containers (physical) for all types of table spaces.

Table 107. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 108. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 109. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected

Usage

You can use this element with the pool_index_p_reads monitor element to calculate the number of physical reads that were performed synchronously (that is, physical index page reads that were performed by database manager agents). Use the following formula to calculate the number of synchronous index reads:
# of sync index reads = pool_index_p_reads + pool_temp_index_p_reads - pool_async_index_reads
By comparing the ratio of asynchronous reads to total physical reads, you can gain insight into how well the prefetchers are working. This element can be helpful when you are tuning the num_ioservers configuration parameter. You can calculate the ratio of asynchronous index reads to total physical index reads by using the following formula:
(pool_async_index_reads) / (pool_index_p_reads + pool_temp_index_p_reads)

pool_async_index_writes - Buffer pool asynchronous index writes monitor element

The number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher may have written dirty pages to disk to make space for the pages being prefetched.

Table 110. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 111. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 112. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected

Usage

You can use this element with the pool_index_writes monitor element to calculate the number of physical index write requests that were performed synchronously (that is, physical index page writes that were performed by database manager agents). Use the following formula:
  pool_index_writes - pool_async_index_writes

By comparing the ratio of asynchronous to synchronous writes, you can gain insight into how well the buffer pool page cleaners are performing. This ratio can be helpful when you are tuning the num_iocleaners configuration parameter.

pool_async_read_time - Buffer Pool Asynchronous Read Time monitor element

Indicates the total amount of time spent reading in data and index pages from the table space containers (physical) by asynchronous engine dispatchable units (EDUs) for all types of table spaces. This value is given in milliseconds.

Table 113. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 114. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 115. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected
Usage
You can use this element to calculate the elapsed time for synchronous reading, using the following formula:
  pool_read_time - pool_async_read_time
You can also use this element to calculate the average asynchronous read time using the following formula:
  pool_async_read_time / pool_async_data_reads

These calculations can be used to understand the I/O work being performed.

pool_async_write_time - Buffer pool asynchronous write time monitor element

Cumulative elapsed time for each asynchronous write to complete. This value is reported in milliseconds.

Table 116. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 117. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 118. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected

Usage

To calculate the elapsed time spent writing pages synchronously, use the following formula:
 pool_write_time - pool_async_write_time
You can also use this element to calculate the average asynchronous write time using the following formula:
 pool_async_write_time
  / (pool_async_data_writes 
    + pool_async_index_writes)

These calculations can be used to understand the I/O work being performed.

pool_async_xda_gbp_indep_pages_found_in_lbp - Group buffer pool independent XML storage object(XDA) pages found by asynchronous EDUs in a local buffer pool monitor element monitor element

The number of group buffer pool (GBP) independent XML storage object (XDA) pages found in a local buffer pool by asynchronous EDUs.

Table 119. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

pool_async_xda_gbp_invalid_pages - Asynchronous group buffer pool invalid XDA data pages monitor element

The number of times a request for a data page for an XML storage object (XDA) was made from the group buffer pool by a prefetcher due to the page being marked invalid in the local buffer pool. Outside of a Db2 pureScale environment, this value is null.

Table 120. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) XDA page hit ratios can be computed as follows:
GBP = ( pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads
Buffer pool hit rates are important factors in the overall performance of the Db2 pureScale instance. Using the previously mentioned formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_xda_gbp_l_reads - Group buffer pool XDA data asynchronous logical read requests monitor element

The number of times a GBP dependent data page for an XML storage object (XDA) was attempted to be read from the group buffer pool by a prefetcher because the page was either invalid or not present in the local buffer pool. Outside of a Db2 pureScale environment, this value is null.

Table 121. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) XDA page hit ratios can be computed as follows:
GBP = ( pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads
Buffer pool hit rates are important factors in the overall performance of the Db2 pureScale instance. Using the previously mentioned formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_xda_gbp_p_reads - Group buffer pool XDA data asynchronous physical read requests monitor element

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

Table 122. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) XDA page hit ratios can be computed as follows:
GBP = ( pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads
Buffer pool hit rates are important factors in the overall performance of the Db2 pureScale instance. Using the previously mentioned formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_xda_lbp_pages_found - Asynchronous local buffer pool XDA data pages found monitor element

The number of times a data page for an XML storage object (XDA) was requested by a prefetcher from and found in the local buffer pool.

Table 123. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

The prefetcher (or asynchronous) XDA page hit ratios can be computed as follows:
GBP = ( pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads
Buffer pool hit rates are important factors in the overall performance of the Db2 pureScale instance. Using the previously mentioned formula can help you determine whether the group buffer pool may be a limiting factor on your database's throughput.

pool_async_xda_read_reqs - Buffer pool asynchronous XDA read requests monitor element

The number of asynchronous read requests for XML storage object (XDA) data.

Table 124. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 125. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 126. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected
Usage
To calculate the average number of XML storage object data pages read per asynchronous request, use the following formula:
 pool_async_xda_reads / pool_async_xda_read_reqs

This average can help you determine the amount of asynchronous I/O done in each interaction with the prefetcher.

pool_async_xda_reads - Buffer pool asynchronous XDA data reads monitor element

Indicates the number of XML storage object (XDA) data pages which have been asynchronously read by prefetchers from the table space containers (physical) for all types of table spaces.

Table 127. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 128. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 129. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected

Usage

Use the pool_async_xda_reads and pool_xda_p_reads monitor elements to calculate the number of physical reads that were performed synchronously on XML storage object data pages (that is, physical data page reads that were performed by database manager agents on XML data). Use the following formula:
 
 pool_xda_p_reads + pool_temp_xda_p_reads - pool_async_xda_reads

By comparing the ratio of asynchronous to synchronous reads, you can gain insight into how well the prefetchers are working. This element can be helpful when you are tuning the num_ioservers configuration parameter.

pool_async_xda_writes - Buffer pool asynchronous XDA data writes monitor element

The number of times a buffer pool data page for an XML storage object (XDA) was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher may have written dirty pages to disk to make space for the pages being prefetched.

Table 130. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
Table 131. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 132. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespaces event_tablespace Always collected
Usage
You can use this element with the pool_xda_writes monitor element to calculate the number of physical write requests that were performed synchronously on XML storage object data pages (that is, physical data page writes that were performed by database manager agents on XML data). Use the following formula:
  pool_xda_writes - pool_async_xda_writes

By comparing the ratio of asynchronous to synchronous writes, you can gain insight into how well the buffer pool page cleaners are performing. This ratio can be helpful when you are tuning the num_iocleaners configuration parameter.

pool_caching_tier_page_read_time - Buffer pool caching tier page read time monitor element

The total amount of time spent by agents synchronously reading-in pages of all types from the caching tier for remote table space containers. Time is measured in milliseconds. This excludes time spent reading from remote storage.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 133. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
Table 134. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

pool_col_caching_tier_l_reads - Buffer pool column-organized caching tier logical reads monitor element

For remote table spaces, the total number of columnar-type pages synchronously read by agents from the caching tier and remote storage. This value does not include temp pages, async or direct reads, or pages that are read by using the buffer pool.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 135. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get service subclass metrics as an XML document REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get service superclass metrics as an XML document REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities REQUEST METRICS BASE
Table 136. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

pool_col_caching_tier_pages_found - Buffer pool column-organized pages found in caching tier monitor element

For remote table spaces, the number of times a data-type page type was found in the caching tier.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 137. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get service subclass metrics as an XML document REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get service superclass metrics as an XML document REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities REQUEST METRICS BASE
Table 138. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

pool_col_gbp_indep_pages_found_in_lbp - Buffer pool column-organized GBP independent 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 139. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 140. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE
Locking - Always collected

pool_col_gbp_invalid_pages - Buffer pool column-organized GBP invalid data pages monitor element

The number of times a column-organized page was invalid in the local buffer pool and was read from the group buffer pool instead. Outside of a Db2 pureScale environment, this value is null.

Table 141. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 142. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE
Locking - Always collected

pool_col_gbp_l_reads - Buffer pool column-organized GBP logical reads monitor element

The number of column-organized pages which have been requested from the buffer pool (logical) for regular and large table spaces.

Table 143. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 144. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE
Locking - Always collected

Usage

To determine how often a requested column-organized page was found in the local buffer pool, use the following formula:
(pool_col_lbp_pages_found - pool_async_col_lbp_pages_found) / pool_col_l_reads
To determine how many times a requested page was found in the group bufferpool, use the following formula
(pool_col_gbp_l_reads - pool_col_gbp_p_reads) / pool_col_gbp_l_reads
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_col_gbp_p_reads - Buffer pool column-organized GBP physical reads monitor element

Indicates the number of column-organized pages read in from the table space containers (physical) for regular and large table spaces.

Table 145. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 146. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE
Locking - Always collected

Usage

To determine how often a requested column-organized page was found in the local buffer pool, use the following formula:
(pool_col_lbp_pages_found - pool_async_col_lbp_pages_found) / pool_col_l_reads
To determine how many times a requested page was found in the group bufferpool, use the following formula
(pool_col_gbp_l_reads - pool_col_gbp_p_reads) / pool_col_gbp_l_reads
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_col_l_reads - Buffer pool column-organized logical reads monitor element

Indicates the number of column-organized pages which have been synchronously read by agents from the buffer pool (logical) for regular and large table spaces.

Table 147. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 148. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Activities event_activity Buffer Pool, Statement
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

This count includes accesses to data that is:
  • Already in the buffer pool when the database manager needs to process the page.
  • Read into the buffer pool before the database manager can process the page.

Column-organized monitor elements can help you understand what portion of the I/O is being driven by access to column-organized tables when a workload includes a mixture of both row-organized and column-organized tables.

To determine how often a requested page was found in the local buffer pool, use the following formula:
(pool_col_lbp_pages_found - pool_async_col_lbp_pages_found) / pool_col_l_reads

Increasing buffer pool size will generally improve the hit ratio, but you will reach a point of diminishing return. Ideally, if you could allocate a buffer pool large enough to store your entire database, then once the system is up and running you would get a hit ratio of 100%. However, this is unrealistic in most cases. The significance of the hit ratio really depends on the size of your data, and the way it is accessed. A very large database where data is accessed evenly would have a poor hit ratio. There is little you can do with very large tables.

To improve hit ratios for smaller, frequently accessed tables and indexes, assign them to individual buffer pools.

pool_col_lbp_pages_found - Buffer pool column-organized LBP pages found monitor element

Indicates the number of column-organized pages which have been synchronously read by agents and asynchronously read by prefetchers from the local buffer pool (logical) for regular and large table spaces.

Table 149. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 150. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the activity_metrics document. REQUEST METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE
Locking - Always collected

Usage

To determine how often a requested page was found in the local buffer pool, use the following formula:
(pool_col_lbp_pages_found - pool_async_col_lbp_pages_found) / pool_col_l_reads
To determine how many times a requested page was found in the group bufferpool, use the following formula
(pool_col_gbp_l_reads - pool_col_gbp_p_reads) / pool_col_gbp_l_reads
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

Column-organized monitor elements can help you understand what portion of the I/O is being driven by access to column-organized tables when a workload includes a mixture of both row-organized and column-organized tables.

pool_col_p_reads - Buffer pool column-organized physical reads monitor element

Indicates the number of column-organized pages which have been synchronously read by agents and asynchronously read by prefetchers from the table space containers (physical) for regular and large table spaces.

Table 151. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 152. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Activities event_activity Buffer Pool, Statement
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

pool_col_writes - Buffer pool column-organized writes monitor element

The number of times a buffer pool column-organized page was physically written to disk.

Table 153. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 154. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

If a buffer pool data page is written to disk for a high percentage of the value of the pool_col_p_reads monitor element, you may be able to improve performance by increasing the number of buffer pool pages available for the database.

A buffer pool data page is written to disk for the following reasons:
  • To free a page in the buffer pool so another page can be read
  • To flush the buffer pool

The system does not always write a page to make room for a new one. If the page has not been updated, it can simply be replaced. This replacement is not counted for this element.

The data page can be written by an asynchronous page-cleaner agent before the buffer pool space is required, as reported by the pool_async_col_writes monitor element. These asynchronous page writes are included in the value of this element in addition to synchronous page writes.

When calculating this percentage, disregard the number of physical reads required to initially fill the buffer pool. To determine the number of pages written:
  1. Run your application (to load the buffer).
  2. Note the value of this element.
  3. Run your application again.
  4. Subtract the value recorded in step 2 from the new value of this element.
In order to prevent the buffer pool from being deallocated between the runnings of your application, you should do one of the following:
  • Activate the database with the ACTIVATE DATABASE command.
  • Have an idle application connected to the database.

If all applications are updating the database, increasing the size of the buffer pool may not have much impact on performance since most of the buffer pool pages contain updated data, which must be written to disk. However, if the updated pages can be used by other units of work before being written out, the buffer pool can save a write and a read, which will improve your performance.

pool_config_size - Configured Size of Memory Pool monitor element

The internally configured size of a memory pool in the database system. The value is in bytes.

Table 155. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager memory_pool Basic
Database memory_pool Basic
Application memory_pool Basic
Table 156. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_dbmemuse Always collected
Connection event_connmemuse Always collected
Usage
To track system memory usage, use this value in conjunction with pool_cur_size, pool_id, and pool_watermark.

To see if a memory pool is nearly full, compare pool_config_size to pool_cur_size. For example, assume that the utility heap is too small. You can diagnose this specific problem by taking snapshots at regular intervals, and looking in the utility heap section of the snapshot output. If required, the pool_cur_size might be allowed to exceed the pool_config_size to prevent an out of memory failure. If this occurs very infrequently, no further action is likely required. However if pool_cur_size is consistently close to or larger than pool_config_size, you might consider increasing the size of the utility heap.

pool_cur_size - Current Size of Memory Pool monitor element

The current size of a memory pool. The value is given in bytes.

Table 157. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager memory_pool Basic
Database memory_pool Basic
Application memory_pool Basic
Table 158. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_dbmemuse Always collected
Connection event_connmemuse Always collected
Usage
To track system memory usage, use this value in conjunction with pool_config_size, pool_id, and pool_watermark.

To see if a memory pool is nearly full, compare pool_config_size to pool_cur_size. For example, assume that the utility heap is too small. You can diagnose this specific problem by taking snapshots at regular intervals, and looking in the utility heap section of the snapshot output. If the value of pool_cur_size is consistently close to pool_config_size, you may want to consider increasing the size of the utility heap.

pool_data_caching_tier_l_reads - Buffer pool data caching tier logical reads monitor element

For remote table spaces, the total number of data-type pages synchronously read by agents from the caching tier and remote storage. This value does not include temp pages, async or direct reads, or pages that are read by using the buffer pool.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 159. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get service subclass metrics as an XML document REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get service superclass metrics as an XML document REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities REQUEST METRICS BASE
Table 160. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

pool_data_caching_tier_pages_found - Buffer pool data pages found in caching tier monitor element

For remote table spaces, the number of times a data-type page was found in the caching tier.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 161. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get service subclass metrics as an XML document REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get service superclass metrics as an XML document REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities REQUEST METRICS BASE
Table 162. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

pool_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 163. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 164. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache_metrics ACTIVITY METRICS BASE
Statistics event_scmetrics
event_wlmetrics
REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE

pool_data_gbp_invalid_pages - Group buffer pool invalid data pages monitor element

The number of times a data page was invalid in the local buffer pool and was read from the group buffer pool instead. Outside of a Db2 pureScale environment, this value is null.

Table 165. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 166. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work uow_metrics- REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE

Usage

To determine how often a requested page was found in the local buffer pool, use the following formula:
(pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / pool_data_l_reads
To determine how many times a requested page was found in the group bufferpool, use the following formula
(pool_data_gbp_l_reads - pool_data_gbp_p_reads) / pool_data_gbp_l_reads
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_data_gbp_l_reads - Group buffer pool data logical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent data page was attempted to be read from the group buffer pool because the page was either invalid or not present in the Local Buffer Pool (LBP). Outside of a Db2 pureScale environment, this value is null.

Table 167. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 168. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work uow_metrics- REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE

Usage

To determine how often a requested page was found in the local buffer pool, use the following formula:
(pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / pool_data_l_reads
To determine how many times a requested page was found in the group bufferpool, use the following formula
(pool_data_gbp_l_reads - pool_data_gbp_p_reads) / pool_data_gbp_l_reads
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_data_gbp_p_reads - Group buffer pool data physical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent data page was read into the local buffer pool from disk because it was not found in the GBP. Outside of a Db2 pureScale environment, this value is null.

Table 169. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 170. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work uow_metrics- REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE

Usage

To determine how often a requested page was found in the local buffer pool, use the following formula:
(pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / pool_data_l_reads
To determine how many times a requested page was found in the group bufferpool, use the following formula
(pool_data_gbp_l_reads - pool_data_gbp_p_reads) / pool_data_gbp_l_reads
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_data_lbp_pages_found - Local buffer pool found data pages monitor element

Indicates the number of data pages which have been synchronously read by agents and asynchronously read by prefetchers from the local buffer pool (logical) for regular and large table spaces.

Table 171. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 172. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work uow_metrics- REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE

Usage

To determine how often a requested page was found in the local buffer pool, use the following formula:
(pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / pool_data_l_reads
To determine how many times a requested page was found in the group bufferpool, use the following formula
(pool_data_gbp_l_reads - pool_data_gbp_p_reads) / pool_data_gbp_l_reads
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_data_l_reads - Buffer pool data logical reads monitor element

Indicates the number of data pages which have been synchronously read by agents from the buffer pool (logical) for regular and large table spaces.

Table 173. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 174. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
Application stmt Buffer Pool
Dynamic SQL dynsql Buffer Pool, Statement
For snapshot monitoring, this counter can be reset.
Table 175. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Tablespaces event_tablespace Always collected
Connection event_conn Always collected
Statement event_stmt Always collected
Activities event_activity Buffer Pool, Statement
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

This count includes accesses to data that is:
  • Already in the buffer pool when the database manager needs to process the page.
  • Read into the buffer pool before the database manager can process the page.

You can use the pool_data_l_reads and pool_data_p_reads monitor elements to calculate the data page hit ratios.

For example, the following formula returns the data page hit ratios in a Db2 environment without the IBM Db2 pureScale Feature:

((pool_data_lbp_pages_found
- pool_async_data_lbp_pages_found) / (pool_data_l_reads + pool_temp_data_l_reads))
× 100 

For more information, see Formulas for calculating buffer pool hit ratios.

Increasing buffer pool size will generally improve the hit ratio, but you will reach a point of diminishing return. Ideally, if you could allocate a buffer pool large enough to store your entire database, then once the system is up and running you would get a hit ratio of 100%. However, this is unrealistic in most cases. The significance of the hit ratio really depends on the size of your data, and the way it is accessed. A very large database where data is accessed evenly would have a poor hit ratio. There is little you can do with very large tables.

To improve hit ratios for smaller, frequently accessed tables and indexes, assign them to individual buffer pools.

pool_data_p_reads - Buffer pool data physical reads monitor element

Indicates the number of data pages which have been synchronously read by agents and asynchronously read by prefetchers from the table space containers (physical) for regular and large table spaces.

Table 176. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 177. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
Application stmt Buffer Pool
Dynamic SQL dynsql Buffer Pool, Statement
For snapshot monitoring, this counter can be reset.
Table 178. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Tablespaces event_tablespace Always collected
Connection event_conn Always collected
Statement event_stmt Always collected
Activities event_activity Buffer Pool, Statement
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

By comparing the ratio of asynchronous reads to total physical reads, you can gain insight into how well the prefetchers are working. This element can be helpful when you are tuning the num_ioservers configuration parameter. You can calculate the ratio of asynchronous reads to physical reads by using the following formula:
(pool_async_data_reads + pool_async_index_reads)/
(pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads)

pool_data_writes - Buffer pool data writes monitor element

The number of times a buffer pool data page was physically written to disk.

Table 179. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 180. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 181. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Tablespaces event_tablespace Always collected
Connection event_conn Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

If a buffer pool data page is written to disk for a high percentage of the value of the pool_data_p_reads monitor element, you may be able to improve performance by increasing the number of buffer pool pages available for the database.

A buffer pool data page is written to disk for the following reasons:
  • To free a page in the buffer pool so another page can be read
  • To flush the buffer pool

The system does not always write a page to make room for a new one. If the page has not been updated, it can simply be replaced. This replacement is not counted for this element.

The data page can be written by an asynchronous page-cleaner agent before the buffer pool space is required, as reported by the pool_async_data_writes monitor element. These asynchronous page writes are included in the value of this element in addition to synchronous page writes.

When calculating this percentage, disregard the number of physical reads required to initially fill the buffer pool. To determine the number of pages written:
  1. Run your application (to load the buffer).
  2. Note the value of this element.
  3. Run your application again.
  4. Subtract the value recorded in step 2 from the new value of this element.
In order to prevent the buffer pool from being deallocated between the runnings of your application, you should do one of the following:
  • Activate the database with the ACTIVATE DATABASE command.
  • Have an idle application connected to the database.

If all applications are updating the database, increasing the size of the buffer pool may not have much impact on performance since most of the buffer pool pages contain updated data, which must be written to disk. However, if the updated pages can be used by other units of work before being written out, the buffer pool can save a write and a read, which will improve your performance.

pool_drty_pg_steal_clns - Buffer pool victim page cleaners triggered monitor element

The number of times a page cleaner was invoked because a synchronous write was needed during the victim buffer replacement for the database.

Table 182. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
Table 183. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 184. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

Using the following formula, you may calculate what percentage of all cleaner invocations are represented by this element:
        pool_drty_pg_steal_clns
     / (pool_drty_pg_steal_clns
      + pool_drty_pg_thrsh_clns
      + pool_lsn_gap_clns)

If this ratio is low, it may indicate that you have defined too many page cleaners. If your chngpgs_thresh configuration parameter is set too low, you may be writing out pages that you will dirty later. Aggressive cleaning defeats one purpose of the buffer pool, that is to defer writing to the last possible moment.

If this ratio is high, it may indicate that you have not defined enough page cleaners. Not having enough page cleaners increases recovery time after failures.

When the DB2_USE_ALTERNATE_PAGE_CLEANING registry variable is OFF:
  • The pool_drty_pg_steal_clns monitor element is inserted into the monitor stream.
  • The pool_drty_pg_steal_clns monitor element counts the number of times a page cleaner was invoked because a synchronous write was needed during the victim buffer replacement for the database.
When the DB2_USE_ALTERNATE_PAGE_CLEANING registry variable is ON:
  • The pool_drty_pg_steal_clns monitor element inserts 0 into the monitor stream.
  • There is no explicit triggering of the page cleaners when a synchronous write is needed during victim buffer replacement. To determine whether or not the right number of page cleaners is configured for the database or for specific buffer pools, refer to the pool_no_victim_buffer monitor element.
Note: Although dirty pages are written out to disk, the pages are not removed from the buffer pool right away, unless the space is needed to read in new pages.

pool_drty_pg_thrsh_clns - Buffer pool threshold cleaners triggered monitor element

The number of times a page cleaner was invoked because a buffer pool had reached the dirty page threshold criterion for the database.

Table 185. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
Table 186. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 187. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
The threshold is set by the chngpgs_thresh configuration parameter. It is a percentage applied to the buffer pool size. When the number of dirty pages in the pool exceeds this value, the cleaners are triggered.

If the chngpgs_thresh configuration parameter value is set too low, pages might be written out too early, requiring them to be read back in. If it is set too high, then too many pages may accumulate, requiring users to write out pages synchronously.

When the DB2_USE_ALTERNATE_PAGE_CLEANING registry variable is OFF:
  • The pool_drty_pg_thrsh_clns monitor element is inserted into the monitor stream.
  • The pool_drty_pg_thrsh_clns monitor element counts the number of times a page cleaner was invoked because a buffer pool had reached the dirty page threshold criterion for the database.
When the DB2_USE_ALTERNATE_PAGE_CLEANING registry variable is ON:
  • The pool_drty_pg_thrsh_clns monitor element inserts 0 into the monitor stream.
  • Page cleaners are always active, attempting to ensure there are sufficient free buffers for victims available instead of waiting to be triggered by the criterion value.

pool_failed_async_col_reqs - Failed column-organized prefetch requests monitor element

The number of times an attempt to queue a column-organized prefetch request was made but failed. One possible reason is the prefetch queue is full.

Table 188. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 189. Event monitoring information
Event type Logical data grouping Monitor switch
Activities event_activity (reported in the details_xml document) event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document) pkgcache_metrics ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document) uow_metrics REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

pool_failed_async_data_reqs - Failed data prefetch requests monitor element

The number of times an attempt to queue a data prefetch request was made but failed. One possible reason is the prefetch queue is full.

Table 190. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 191. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_failed_async_..._reqs elements tells you how many prefetch requests could not be added to a prefetch queue. Requests can fail to be added to the prefetch queue if the prefetch queue is too small, or if the prefetcher is running too slowly. When requests cannot be added to a prefetch queue, a database agent typically performs disk IO synchronously, which is less efficient than prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS 
    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS 
      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS + 
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS 
      )
    ) × 100
This formula calculates the ratio of successful prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. Requests can fail to be added to the prefetch queue if there are a large number of requests being created, or if the prefetcher is running too slowly due to poor configuration or poor tuning. If the percentage of successful requests is low, this can indicate a bottleneck in the prefetching mechanism. You might need to configure more prefetchers by modifying the value for the configuration parameter num_ioservers. The condition of prefetch queues being full can also be caused by agents submitting too many small requests; you can use the related monitor elements pool_queued_async_..._pages and pool_queued_async_..._reqs to determine the average prefetch request size.

pool_failed_async_index_reqs - Failed index prefetch requests monitor element

The number of times an attempt to queue an index prefetch request was made but failed. One possible reason is the prefetch queue is full and a request could not be obtained from the free list.

Table 192. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 193. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_failed_async_..._reqs elements tells you how many prefetch requests could not be added to a prefetch queue. Requests can fail to be added to the prefetch queue if the prefetch queue is too small, or if the prefetcher is running too slowly. When requests cannot be added to a prefetch queue, a database agent typically performs disk IO synchronously, which is less efficient than prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS 
    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS 
      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS + 
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS 
      )
    ) × 100
This formula calculates the ratio of successful prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. Requests can fail to be added to the prefetch queue if there are a large number of requests being created, or if the prefetcher is running too slowly due to poor configuration or poor tuning. If the percentage of successful requests is low, this can indicate a bottleneck in the prefetching mechanism. You might need to configure more prefetchers by modifying the value for the configuration parameter num_ioservers. The condition of prefetch queues being full can also be caused by agents submitting too many small requests; you can use the related monitor elements pool_queued_async_..._pages and pool_queued_async_..._reqs to determine the average prefetch request size.

pool_failed_async_temp_col_reqs - Failed column-organized temporary prefetch requests monitor element

The number of times an attempt to queue a column-organized prefetch request for temporary table spaces was made but failed. One possible reason is the prefetch queue is full and a request could not be obtained from the free list.

Note: In Db2 version 10.5, this element returns 0 as column-organized temporary tables are not currently supported.
Table 194. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 195. Event monitoring information
Event type Logical data grouping Monitor switch
Activities event_activity (reported in the details_xml document) event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document) pkgcache_metrics ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document) uow_metrics REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_failed_async_*_reqs elements tells you how many prefetch requests could not be added to a prefetch queue. Requests can fail to be added to the prefetch queue if the prefetch queue is too small, or if the prefetcher is running too slowly. When requests cannot be added to a prefetch queue, a database agent typically performs disk IO synchronously, which is less efficient than prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
1 - 
    (
    pool_failed_async_col_reqs + 
    pool_failed_async_data_reqs + 
    pool_failed_async_index_reqs + 
    pool_failed_async_xda_reqs + 
    pool_failed_async_temp_col_reqs +
    pool_failed_async_temp_data_reqs + 
    pool_failed_async_temp_index_reqs + 
    pool_failed_async_temp_xda_reqs
    ) 
    ÷ 
    (
      (
      pool_failed_async_col_reqs + 
      pool_failed_async_data_reqs + 
      pool_failed_async_index_reqs + 
      pool_failed_async_xda_reqs + 
      pool_failed_async_temp_col_reqs +
      pool_failed_async_temp_data_reqs + 
      pool_failed_async_temp_index_reqs + 
      pool_failed_async_temp_xda_reqs 
      )
    + 
      (
      pool_queued_async_col_reqs + 
      pool_queued_async_data_reqs + 
      pool_queued_async_index_reqs + 
      pool_queued_async_xda_reqs + 
      pool_queued_async_temp_col_reqs +
      pool_queued_async_temp_data_reqs + 
      pool_queued_async_temp_index_reqs + 
      pool_queued_async_temp_xda_reqs
      )
    ) × 100
This formula calculates the ratio of successful prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. Requests can fail to be added to the prefetch queue if there are many requests being created, or if the prefetcher is running too slowly due to poor configuration or poor tuning. If the percentage of successful requests is low, this can indicate a bottleneck in the prefetching mechanism. You might need to configure more prefetchers by modifying the value for the num_ioservers configuration parameter. The condition of prefetch queues being full can also be caused by agents submitting too many small requests; you can use the related monitor elements pool_queued_async_*_pages and pool_queued_async_*_reqs to determine the average prefetch request size.

pool_failed_async_other_reqs - Failed non-prefetch requests monitor element

The number of times an attempt to queue a non-prefetch request was made but failed. This element is for non-prefetch work done by prefetchers. One possible reason for the failed request is that the prefetch queue is full.

Table 196. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 197. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This monitor element reports the number of requests for work that is not related to the prefetching dictated by an access plan that could not be added to the prefetch queue. Utilities like the backup utility use the prefetcher mechanism to perform their tasks, but in a way that is different from the way an access plan for an SQL statement does. A request might fail to be added to a prefetch queue because the queue is full.

pool_failed_async_temp_data_reqs - Failed data prefetch requests for temporary table spaces monitor element

The number of times an attempt to queue a data prefetch request for temporary table spaces was made but failed. One possible reason is the prefetch queue is full and a request could not be obtained from the free list.

Table 198. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 199. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_failed_async_..._reqs elements tells you how many prefetch requests could not be added to a prefetch queue. Requests can fail to be added to the prefetch queue if the prefetch queue is too small, or if the prefetcher is running too slowly. When requests cannot be added to a prefetch queue, a database agent typically performs disk IO synchronously, which is less efficient than prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS 
    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS 
      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS + 
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS 
      )
    ) × 100
This formula calculates the ratio of successful prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. Requests can fail to be added to the prefetch queue if there are a large number of requests being created, or if the prefetcher is running too slowly due to poor configuration or poor tuning. If the percentage of successful requests is low, this can indicate a bottleneck in the prefetching mechanism. You might need to configure more prefetchers by modifying the value for the configuration parameter num_ioservers. The condition of prefetch queues being full can also be caused by agents submitting too many small requests; you can use the related monitor elements pool_queued_async_..._pages and pool_queued_async_..._reqs to determine the average prefetch request size.

pool_failed_async_temp_index_reqs - Failed index prefetch requests for temporary table spaces monitor element

The number of times an attempt to queue an index prefetch request for temporary table spaces was made but failed. One possible reason is the prefetch queue is full and a request could not be obtained from the free list.

Table 200. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 201. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_failed_async_..._reqs elements tells you how many prefetch requests could not be added to a prefetch queue. Requests can fail to be added to the prefetch queue if the prefetch queue is too small, or if the prefetcher is running too slowly. When requests cannot be added to a prefetch queue, a database agent typically performs disk IO synchronously, which is less efficient than prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS 
    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS 
      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS + 
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS 
      )
    ) × 100
This formula calculates the ratio of successful prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. Requests can fail to be added to the prefetch queue if there are a large number of requests being created, or if the prefetcher is running too slowly due to poor configuration or poor tuning. If the percentage of successful requests is low, this can indicate a bottleneck in the prefetching mechanism. You might need to configure more prefetchers by modifying the value for the configuration parameter num_ioservers. The condition of prefetch queues being full can also be caused by agents submitting too many small requests; you can use the related monitor elements pool_queued_async_..._pages and pool_queued_async_..._reqs to determine the average prefetch request size.

pool_failed_async_temp_xda_reqs - Failed XDA prefetch requests for temporary table spaces monitor element

The number of times an attempt to queue a XML storage object (XDA) data prefetch request for temporary table spaces was made but failed. One possible reason is the prefetch queue is full and a request could not be obtained from the free list.

Table 202. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 203. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_failed_async_..._reqs elements tells you how many prefetch requests could not be added to a prefetch queue. Requests can fail to be added to the prefetch queue if the prefetch queue is too small, or if the prefetcher is running too slowly. When requests cannot be added to a prefetch queue, a database agent typically performs disk IO synchronously, which is less efficient than prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS 
    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS 
      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS + 
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS 
      )
    ) × 100
This formula calculates the ratio of successful prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. Requests can fail to be added to the prefetch queue if there are a large number of requests being created, or if the prefetcher is running too slowly due to poor configuration or poor tuning. If the percentage of successful requests is low, this can indicate a bottleneck in the prefetching mechanism. You might need to configure more prefetchers by modifying the value for the configuration parameter num_ioservers. The condition of prefetch queues being full can also be caused by agents submitting too many small requests; you can use the related monitor elements pool_queued_async_..._pages and pool_queued_async_..._reqs to determine the average prefetch request size.

pool_failed_async_xda_reqs - Failed XDA prefetch requests monitor element

The number of times an attempt to queue an XML storage object (XDA) data prefetch request was made but failed. One possible reason is the prefetch queue is full and a request could not be obtained from the free list.

Table 204. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 205. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_failed_async_..._reqs elements tells you how many prefetch requests could not be added to a prefetch queue. Requests can fail to be added to the prefetch queue if the prefetch queue is too small, or if the prefetcher is running too slowly. When requests cannot be added to a prefetch queue, a database agent typically performs disk IO synchronously, which is less efficient than prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS 
    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS 
      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS + 
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS 
      )
    ) × 100
This formula calculates the ratio of successful prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. Requests can fail to be added to the prefetch queue if there are a large number of requests being created, or if the prefetcher is running too slowly due to poor configuration or poor tuning. If the percentage of successful requests is low, this can indicate a bottleneck in the prefetching mechanism. You might need to configure more prefetchers by modifying the value for the configuration parameter num_ioservers. The condition of prefetch queues being full can also be caused by agents submitting too many small requests; you can use the related monitor elements pool_queued_async_..._pages and pool_queued_async_..._reqs to determine the average prefetch request size.

pool_id - Memory Pool Identifier monitor element

The type of memory pool.

Table 206. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager memory_pool Basic
Database memory_pool Basic
Application memory_pool Basic
Table 207. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_dbmemuse -
Connection event_connmemuse -

Usage

To track system memory usage, use this value in conjunction with pool_config_size, pool_cur_size, and pool_watermark.

Use pool_id to identify the memory pools discussed in the system monitor output. The various memory pool identifiers can be found in sqlmon.h. Under normal operating conditions, one or more of each of the following pools can be expected.
API Constant Description
SQLM_HEAP_APPLICATION Application Heap
SQLM_HEAP_DATABASE Database Heap
SQLM_HEAP_LOCK_MGR Lock Manager Heap
SQLM_HEAP_UTILITY Backup/Restore/Utility Heap
SQLM_HEAP_STATISTICS Statistics Heap
SQLM_HEAP_PACKAGE_CACHE Package Cache Heap
SQLM_HEAP_CAT_CACHE Catalog Cache Heap
SQLM_HEAP_MONITOR Database Monitor Heap
SQLM_HEAP_STATEMENT Statement Heap
SQLM_HEAP_FCMBP FCMBP Heap
SQLM_HEAP_IMPORT_POOL Import Pool
SQLM_HEAP_OTHER Other Memory
SQLM_HEAP_BP Buffer Pool Heap
SQLM_HEAP_APPL_SHARED Applications Shared Heap
SQLM_HEAP_SHARED_SORT Sort Shared Heap

pool_index_caching_tier_l_reads - Buffer pool index caching tier logical reads monitor element

For remote table spaces, the total number of index-type pages synchronously read by agents from the caching tier and remote storage. This value does not include temp pages, async or direct reads, or pages that are read by using the buffer pool.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 208. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get service subclass metrics as an XML document REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get service superclass metrics as an XML document REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities REQUEST METRICS BASE
Table 209. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

pool_index_caching_tier_pages_found - Buffer pool index pages found in caching tier monitor element

For remote table spaces, the number of times an index-type page was found in the caching tier.

Attention: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse on Cloud products.
Table 210. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get service subclass metrics as an XML document REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get service superclass metrics as an XML document REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities REQUEST METRICS BASE
Table 211. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

pool_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 a local buffer pool (LBP) by an agent. Outside of a Db2 pureScale environment, this value is null.

Table 212. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 213. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache_metrics ACTIVITY METRICS BASE
Statistics event_scmetrics
event_wlmetrics
REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE

pool_index_gbp_invalid_pages - Group buffer pool invalid index pages monitor element

The number of times an index page was attempted to be read from the group bufferpool because the page was invalid in the local buffer pool. Outside of a Db2 pureScale environment, this value is null.

Table 214. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 215. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work uow_metrics- REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE

Usage

To determine how often a requested index page was found in the local buffer pool, use the following formula:
(POOL_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND) / POOL_INDEX_L_READS
To determine how many times a requested index page was found in the group bufferpool, use the following formula
(POOL_INDEX_GBP_L_READS - POOL_INDEX_GBP_P_READS) / POOL_INDEX_GBP_L_READS
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_index_gbp_l_reads - Group buffer pool index logical reads monitor element

The number of times a Group Buffer Pool (GBP) dependent index page was attempted to be read from the group buffer pool because the page was either invalid or not present in the local buffer pool. Outside of a Db2 pureScale environment, this value is null.

Table 216. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 217. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work uow_metrics- REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE

Usage

To determine how often a requested index page was found in the local buffer pool, use the following formula:
(POOL_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND) / POOL_INDEX_L_READS
To determine how many times a requested index page was found in the group bufferpool, use the following formula
(POOL_INDEX_GBP_L_READS - POOL_INDEX_GBP_P_READS) / POOL_INDEX_GBP_L_READS
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_index_gbp_p_reads - Group buffer pool index physical reads monitor elements

The number of times a Group Buffer Pool (GBP) dependent index page was read into the local buffer pool from disk because it was not found in the GBP. Outside of a Db2 pureScale environment, this value is null.

Table 218. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 219. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work uow_metrics- REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE

Usage

To determine how often a requested index page was found in the local buffer pool, use the following formula:
(POOL_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND) / POOL_INDEX_L_READS
To determine how many times a requested index page was found in the group bufferpool, use the following formula
(POOL_INDEX_GBP_L_READS - POOL_INDEX_GBP_P_READS) / POOL_INDEX_GBP_L_READS
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_index_lbp_pages_found - Local buffer pool index pages found monitor element

Indicates the number of index pages which have been synchronously read by agents and asynchronously read by prefetchers from the local buffer pool (logical) for regular and large table spaces.

Table 220. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 221. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work uow_metrics- REQUEST METRICS BASE
Package cache pkgcache_metrics ACTIVITY METRICS BASE

Usage

To determine how often a requested index page was found in the local buffer pool, use the following formula:
(POOL_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND) / POOL_INDEX_L_READS
To determine how many times a requested index page was found in the group bufferpool, use the following formula
(POOL_INDEX_GBP_L_READS - POOL_INDEX_GBP_P_READS) / POOL_INDEX_GBP_L_READS
Local buffer pool and group buffer pool hit rates are both important factors in the overall performance of the cluster caching facility. Using these formulas can help you determine whether the local or group buffer pool may be a limiting factor on your database's throughput.

pool_index_l_reads - Buffer pool index logical reads monitor element

Indicates the number of index pages which have been synchronously read by agents from the buffer pool (logical) for regular and large table spaces.

Table 222. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 223. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
Application stmt Buffer Pool
Dynamic SQL dynsql Buffer Pool, Statement
For snapshot monitoring, this counter can be reset.
Table 224. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Tablespaces event_tablespace Always collected
Connection event_conn Always collected
Statement event_stmt Always collected
Activities event_activity Buffer Pool, Statement
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

This count includes accesses to index pages that are:
  • Already in the buffer pool when the database manager needs to process the page
  • Read into the buffer pool before the database manager can process the page.

In conjunction with pool_index_p_reads and pool_async_index_reads, you can use pool_index_l_reads to calculate the index page hit ratio for the buffer pool.

For example, the following formula returns the index page hit ratios in a Db2 environment without the IBM Db2 pureScale Feature

((pool_index_lbp_pages_found
- pool_async_index_lbp_pages_found )  / (pool_index_l_reads
+ pool_temp_index_l_reads)) × 100

For more information, see Formulas for calculating buffer pool hit ratios.

If the hit ratio is low, increasing the number of buffer pool pages may improve performance.

pool_index_p_reads - Buffer pool index physical reads monitor element

Indicates the number of index pages which have been synchronously read by agents and asynchronously read by prefetchers from the table space containers (physical) for regular and large table spaces.

Table 225. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 226. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
Application stmt Buffer Pool
Dynamic SQL dynsql Buffer Pool, Statement
For snapshot monitoring, this counter can be reset.
Table 227. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Tablespaces event_tablespace Always collected
Connection event_conn Always collected
Statement event_stmt Always collected
Activities event_activity Buffer Pool, Statement
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

In conjunction with the pool_index_l_reads and pool_async_index_reads, you can use pool_index_p_reads to calculate the index page hit ratio for the buffer pool.

For example, the following formula returns the index page hit ratios in a Db2 environment without the IBM Db2 pureScale Feature

((pool_index_lbp_pages_found
- pool_async_index_lbp_pages_found )  / (pool_index_l_reads
+ pool_temp_index_l_reads)) × 100

For more information, see Formulas for calculating buffer pool hit ratios.

pool_index_writes - Buffer pool index writes monitor element

Indicates the number of times a buffer pool index page was physically written to disk.

Table 228. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function Always collected
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 229. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 230. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Tablespaces event_tablespace Always collected
Connection event_conn Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Like a data page, a buffer pool index page is written to disk for the following reasons:
  • To free a page in the buffer pool so another page can be read
  • To flush the buffer pool

The system does not always write a page to make room for a new one. If the page has not been updated, it can simply be replaced. This replacement is not counted for this element.

The index page can be written by an asynchronous page-cleaner agent before the buffer pool space is required. These asynchronous index page writes are included in the value of this element in addition to synchronous index page writes (see the pool_async_index_writes monitor element).

If a buffer pool index page is written to disk for a high percentage of the value of the pool_index_p_reads monitor element, you may be able to improve performance by increasing the number of buffer pool pages available for the database.

When calculating this percentage, disregard the number of physical reads required to initially fill the buffer pool. To determine the number of pages written:
  1. Run your application (to load the buffer).
  2. Note the value of this element.
  3. Run your application again.
  4. Subtract the value recorded in step 2 from the new value of this element.
In order to prevent the buffer pool from being deallocated between the runnings of your application, you should do one of the following:
  • Activate the database with the ACTIVATE DATABASE command.
  • Have an idle application connected to the database.

If all applications are updating the database, increasing the size of the buffer pool may not have much impact on performance, since most of the pages contain updated data which must be written to disk.

pool_lsn_gap_clns - Buffer pool log space cleaners triggered monitor element

The number of times a page cleaner was invoked because the logging space used had reached a predefined criterion for the database.

Table 231. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
Table 232. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 233. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

This element can be used to help evaluate whether you have enough space for logging, and whether you need more log files or larger log files.

The page cleaning criterion is determined by the setting for the page_age_trgt_mcr configuration parameter. Page cleaners are triggered when the oldest page in the buffer pool exceeds the configured time for the page_age_trgt_mcr configuration parameter.

When the DB2_USE_ALTERNATE_PAGE_CLEANING registry variable is OFF:
  • The pool_lsn_gap_clns monitor element is inserted into the monitor stream.
  • Page cleaners are triggered if the oldest page in the buffer pool contains an update described by a log record that is older than the current log position by the criterion value.
When the DB2_USE_ALTERNATE_PAGE_CLEANING registry variable is ON:
  • The pool_lsn_gap_clns monitor element inserts 0 into the monitor stream.
  • Page cleaners write pages proactively instead of waiting to be triggered by the criterion value.

pool_no_victim_buffer - Buffer pool no victim buffers monitor element

Number of times an agent did not have a preselected victim buffer available.

Table 234. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
Table 235. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Tablespace tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 236. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Tablespace event_tablespace Always collected
Usage
This element can be used to help evaluate whether you have enough page cleaners for a given buffer pool when using proactive page cleaning.

When the DB2_USE_ALTERNATE_PAGE_CLEANING registry variable is ON, the pool_no_victim_buffer element counts the number of times that an agent did not find a preselected victim buffer available for immediate use, and was forced to search the buffer pool for a suitable victim buffer.

If the value of pool_no_victim_buffer element is high relative to the number of logical reads in the buffer pool, the database system is having difficulty ensuring that sufficient numbers of good victims are available for use. Increasing the number of page cleaners will increase the ability of the database system to provide preselected victim buffers.

When the DB2_USE_ALTERNATE_PAGE_CLEANING registry variable is OFF, the pool_no_victim_buffer element has no predictive value, and can be safely ignored. In this configuration, the database system does not attempt to ensure that agents have preselected victim buffers available to them, so most accesses to the buffer pool will require that the agent search the buffer pool to find a victim buffer.

pool_queued_async_col_pages - Column-organized page prefetch requests monitor element

The number of column-organized pages successfully requested for prefetching.

Table 237. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 238. Event monitoring information
Event type Logical data grouping Monitor switch
Activities event_activity (reported in the details_xml document) event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document) pkgcache_metrics ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document) uow_metrics REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

pool_queued_async_col_reqs - Column-organized prefetch requests monitor element

The number of column-organized prefetch requests successfully added to the prefetch queue.

Table 239. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 240. Event monitoring information
Event type Logical data grouping Monitor switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache_metrics ACTIVITY METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE

pool_queued_async_data_pages - Data pages prefetch requests monitor element

The number of data pages successfully requested for prefetching.

Table 241. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 242. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This monitor element, along with the other pool_queued_async_..._pages elements tells you how many pages of data were retrieved by prefetch requests. You can use this information to determine whether prefetch requests are being performed efficiently on your system. For example, you can calculate the average number of pages per prefetch request using a formula like the one that follows:
  (POOL_QUEUED_ASYNC_DATA_PAGES +
   POOL_QUEUED_ASYNC_INDEX_PAGES +
   POOL_QUEUED_ASYNC_XDA_PAGES +
   POOL_QUEUED_ASYNC_COL_PAGES +
   POOL_QUEUED_ASYNC_TEMP_DATA_PAGES +
   POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES +
   POOL_QUEUED_ASYNC_TEMP_XDA_PAGES +
   POOL_QUEUED_ASYNC_TEMP_COL_PAGES)
÷
  (POOL_QUEUED_ASYNC_DATA_REQS +
   POOL_QUEUED_ASYNC_INDEX_REQS +
   POOL_QUEUED_ASYNC_XDA_REQS +
   POOL_QUEUED_ASYNC_COL_REQS +
   POOL_QUEUED_ASYNC_TEMP_DATA_REQS +
   POOL_QUEUED_ASYNC_TEMP_INDEX_REQS +
   POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
   POOL_QUEUED_ASYNC_TEMP_COL_REQS)
If the average number of pages per request is low, and there is a significant amount of prefetching on the system, then your system might be performing more IO operations than necessary. In general, request size is based on prefetch size, which should be at least as large as extent size. So a small average request size might indicate that prefetch size is set too low, and that increasing prefetch size to a multiple of extent size may improve performance. Also note that a small average request size may mean that the prefetch queues fill up too quickly, so it is worthwhile to also monitor the associated pool_failed_async_..._reqs monitor element

pool_queued_async_data_reqs - Data prefetch requests monitor element

The number of data prefetch requests successfully added to the prefetch queue.

Table 243. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 244. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_queued_async_*_reqs elements tells you how many prefetch requests have been added to the prefetch queue. You can use this information to see how often the database manager does prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS +       
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS      )
    ) * 100
This formula calculates the ratio of failed prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. If the percentage is low, you might need to configure more prefetchers by modifying the num_ioservers configuration parameter.

pool_queued_async_index_pages - Index pages prefetch requests monitor element

The number of index pages successfully requested for prefetching.

Table 245. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 246. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This monitor element, along with the other pool_queued_async_..._pages elements tells you how many pages of data were retrieved by prefetch requests. You can use this information to determine whether prefetch requests are being performed efficiently on your system. For example, you can calculate the average number of pages per prefetch request using a formula like the one that follows:
  (POOL_QUEUED_ASYNC_DATA_PAGES +
   POOL_QUEUED_ASYNC_INDEX_PAGES +
   POOL_QUEUED_ASYNC_XDA_PAGES +
   POOL_QUEUED_ASYNC_COL_PAGES +
   POOL_QUEUED_ASYNC_TEMP_DATA_PAGES +
   POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES +
   POOL_QUEUED_ASYNC_TEMP_XDA_PAGES +
   POOL_QUEUED_ASYNC_TEMP_COL_PAGES)
÷
  (POOL_QUEUED_ASYNC_DATA_REQS +
   POOL_QUEUED_ASYNC_INDEX_REQS +
   POOL_QUEUED_ASYNC_XDA_REQS +
   POOL_QUEUED_ASYNC_COL_REQS +
   POOL_QUEUED_ASYNC_TEMP_DATA_REQS +
   POOL_QUEUED_ASYNC_TEMP_INDEX_REQS +
   POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
   POOL_QUEUED_ASYNC_TEMP_COL_REQS)
If the average number of pages per request is low, and there is a significant amount of prefetching on the system, then your system might be performing more IO operations than necessary. In general, request size is based on prefetch size, which should be at least as large as extent size. So a small average request size might indicate that prefetch size is set too low, and that increasing prefetch size to a multiple of extent size may improve performance. Also note that a small average request size may mean that the prefetch queues fill up too quickly, so it is worthwhile to also monitor the associated pool_failed_async_..._reqs monitor element

pool_queued_async_index_reqs - Index prefetch requests monitor element

The number of index prefetch requests successfully added to the prefetch queue.

Table 247. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 248. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_queued_async_*_reqs elements tells you how many prefetch requests have been added to the prefetch queue. You can use this information to see how often the database manager does prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS +       
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS      )
    ) * 100
This formula calculates the ratio of failed prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. If the percentage is low, you might need to configure more prefetchers by modifying the num_ioservers configuration parameter.

pool_queued_async_other_reqs - Other requests handled by prefetchers monitor element

The number of requests for non-prefetch work successfully added to the prefetch queue. This is for other work done by prefetchers.

Table 249. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 250. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This monitor element reports the number of requests added to the prefetch queue for IO work that is not related to the prefetching dictated by an access plan. Utilities like the backup utility use the prefetcher mechanism to perform their tasks, but in a way that is different from the way an access plan for an SQL statement does.

pool_queued_async_temp_col_pages - Column-organized page temporary prefetch requests monitor element

The number of column-organized pages for temporary table spaces successfully requested for prefetching.

Note: In Db2 version 10.5, this element returns 0 as column-organized temporary tables are not currently supported.
Table 251. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics 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 DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 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 252. Event monitoring information
Event type Logical data grouping Monitor switch
Activities event_activity (reported in the details_xml document) event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document) pkgcache_metrics ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document) uow_metrics REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This monitor element, along with the other pool_queued_async_*_pages elements tells you how many pages of data were retrieved by prefetch requests. You can use this information to determine whether prefetch requests are being performed efficiently on your system. For example, you can calculate the average number of pages per prefetch request using a formula like the one that follows:
  (pool_queued_async_col_pages +
   pool_queued_async_data_pages +
   pool_queued_async_index_pages +
   pool_queued_async_xda_pages +
   pool_queued_async_temp_col_pages +
   pool_queued_async_temp_data_pages +
   pool_queued_async_temp_index_pages +
   pool_queued_async_temp_xda_pages)
/
  (pool_queued_async_col_reqs +
   pool_queued_async_data_reqs +
   pool_queued_async_index_reqs +
   pool_queued_async_xda_reqs +
   pool_queued_async_temp_col_reqs +
   pool_queued_async_temp_data_reqs +
   pool_queued_async_temp_index_reqs +
   pool_queued_async_temp_xda_reqs)
If the average number of pages per request is low, and there is a significant amount of prefetching on the system, then your system might be performing more IO operations than necessary. In general, request size is based on prefetch size, which should be at least as large as extent size. So a small average request size might indicate that prefetch size is set too low, and that increasing prefetch size to a multiple of extent size may improve performance. Also note that a small average request size may mean that the prefetch queues fill up too quickly, so it is worthwhile to also monitor the associated pool_failed_async_*_reqs monitor element.

pool_queued_async_temp_col_reqs - Column-organized temporary prefetch requests monitor element

The number of column-organized prefetch requests for temporary table spaces successfully added to the prefetch queue.

Note: In Db2 version 10.5, this element returns 0 as column-organized temporary tables are not currently supported.
Table 253. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 unit of work metrics as an 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 254. Event monitoring information
Event type Logical data grouping Monitor switch
Activities event_activity (reported in the details_xml document) event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document) pkgcache_metrics ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document) uow_metrics REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_superclassstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

pool_queued_async_temp_data_pages - Data pages prefetch requests for temporary table spaces monitor element

The number of data pages for temporary table spaces successfully requested for prefetching.

Table 255. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 256. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This monitor element, along with the other pool_queued_async_..._pages elements tells you how many pages of data were retrieved by prefetch requests. You can use this information to determine whether prefetch requests are being performed efficiently on your system. For example, you can calculate the average number of pages per prefetch request using a formula like the one that follows:
  (POOL_QUEUED_ASYNC_DATA_PAGES +
   POOL_QUEUED_ASYNC_INDEX_PAGES +
   POOL_QUEUED_ASYNC_XDA_PAGES +
   POOL_QUEUED_ASYNC_COL_PAGES +
   POOL_QUEUED_ASYNC_TEMP_DATA_PAGES +
   POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES +
   POOL_QUEUED_ASYNC_TEMP_XDA_PAGES +
   POOL_QUEUED_ASYNC_TEMP_COL_PAGES)
÷
  (POOL_QUEUED_ASYNC_DATA_REQS +
   POOL_QUEUED_ASYNC_INDEX_REQS +
   POOL_QUEUED_ASYNC_XDA_REQS +
   POOL_QUEUED_ASYNC_COL_REQS +
   POOL_QUEUED_ASYNC_TEMP_DATA_REQS +
   POOL_QUEUED_ASYNC_TEMP_INDEX_REQS +
   POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
   POOL_QUEUED_ASYNC_TEMP_COL_REQS)
If the average number of pages per request is low, and there is a significant amount of prefetching on the system, then your system might be performing more IO operations than necessary. In general, request size is based on prefetch size, which should be at least as large as extent size. So a small average request size might indicate that prefetch size is set too low, and that increasing prefetch size to a multiple of extent size may improve performance. Also note that a small average request size may mean that the prefetch queues fill up too quickly, so it is worthwhile to also monitor the associated pool_failed_async_..._reqs monitor element

pool_queued_async_temp_data_reqs - Data prefetch requests for temporary table spaces monitor element

The number of data prefetch requests for temporary table spaces successfully added to the prefetch queue.

Table 257. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 258. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This element, along with the other pool_queued_async_*_reqs elements tells you how many prefetch requests have been added to the prefetch queue. You can use this information to see how often the database manager does prefetching. You can use these elements in conjunction with other prefetcher monitor elements to determine how effectively prefetching is done on your system. For example, you can see what the percentage of requests were successfully added to the prefetch queue using a formula like the one that follows:
   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS +       
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS      )
    ) * 100
This formula calculates the ratio of failed prefetch requests to the total number of requests made. A failed prefetch request is one that could not be added to the prefetch queue. If the percentage is low, you might need to configure more prefetchers by modifying the num_ioservers configuration parameter.

pool_queued_async_temp_index_pages - Index pages prefetch requests for temporary table spaces monitor element

The number of index pages for temporary table spaces successfully requested for prefetching.

Table 259. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table function - Get table space metrics DATA OBJECT 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 260. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in details_xml document)
event_activitymetrics
ACTIVITY METRICS BASE
Package Cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE

Usage

This monitor element, along with the other pool_queued_async_..._pages elements tells you how many pages of data were retrieved by prefetch requests. You can use this information to determine whether prefetch requests are being performed efficiently on your system. For example, you can calculate the average number of pages per prefetch request using a formula like the one that follows:
  (POOL_QUEUED_ASYNC_DATA_PAGES +
   POOL_QUEUED_ASYNC_INDEX_PAGES +
   POOL_QUEUED_ASYNC_XDA_PAGES +
   POOL_QUEUED_ASYNC_COL_PAGES +
   POOL_QUEUED_ASYNC_TEMP_DATA_PAGES +
   POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES +
   POOL_QUEUED_ASYNC_TEMP_XDA_PAGES +
   POOL_QUEUED_ASYNC_TEMP_COL_PAGES)
÷
  (POOL_QUEUED_ASYNC_DATA_REQS +
   POOL_QUEUED_ASYNC_INDEX_REQS +
   POOL_QUEUED_ASYNC_XDA_REQS +
   POOL_QUEUED_ASYNC_COL_REQS +
   POOL_QUEUED_ASYNC_TEMP_DATA_REQS +
   POOL_QUEUED_ASYNC_TEMP_INDEX_REQS +
   POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
   POOL_QUEUED_ASYNC_TEMP_COL_REQS)
If the average number of pages per request is low, and there is a significant amount of prefetching on the system, then your system might be performing more IO operations than necessary. In general, request size is based on prefetch size, which should be at least as large as extent size. So a small average request size might indicate that prefetch size is set too low, and that increasing prefetch size to a multiple of extent size may improve performance. Also note that a small average request size may mean that the prefetch queues fill up too quickly, so it is worthwhile to also monitor the associated pool_failed_async_..._reqs monitor element

pool_queued_async_temp_index_reqs - Index prefetch requests for temporary table spaces monitor element

The number of index prefetch requests for temporary table spaces successfully added to the prefetch queue.

Table 261. 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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT 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 DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT 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 table function - get detailed metrics for package cache entries (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function 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 (reported in DETAILS XML document) 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_TABLESPACE table f