T

tab_organization - Data organization in table monitor element

This element reports the organization of data in the table.

Table 1. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics Always collected

Usage

Possible returned values are:
C
Indicates that data is column-organized.
R
Indicates that data is row-organized.
This element can be used to help interpret other monitoring data.

table_file_id - Table file ID monitor element

The file ID (FID) for the table.

Table 3. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock
Table table Basic
Lock appl_lock_list Lock
Lock lock Lock
Table 4. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks lock Always collected

Usage

For snapshot monitoring, this element is provided for information purposes only. It is returned for compatibility with previous versions of the database system monitor, and it may not uniquely identify the table. Use table_name and table_schema monitor elements to identify the table.

In MON_GET_LOCKS and MON_GET_APPL_LOCKWAIT table functions, this element represents the file ID (FID) for the table that the lock references.

table_name - Table name monitor element

The name of the table.

Table 6. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
Application appl Lock
Lock appl_lock_list Lock
Lock lock Lock
Lock lock_wait Lock
Table 7. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Tables event_table Always collected
Deadlocks1 lock Always collected
Deadlocks1 event_dlconn Always collected
Deadlocks with Details1 event_detailed_dlconn 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

Along with table_schema, this element can help you determine the source of contention for resources.

At the application-level, application-lock level, and deadlock-monitoring-level, this is the table that the application is waiting to lock, because it is currently locked by another application. For snapshot monitoring, this item is only valid when the lock monitor group information is set to ON, and when lock_object_type indicates that the application is waiting to obtain a table lock.

For snapshot monitoring at the object-lock level, this item is returned for table-level and row-level locks. The table reported at this level is the table against which this application holds these locks.

For snapshot and event monitoring at the table level, this is the table for which information has been collected. For temporary tables, the format for table_name is TEMP (n, m), where:
  • n is the table space ID
  • m is the table_file_id element

table_schema - Table schema name monitor element

The schema of the table.

Table 10. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
Application appl Lock
Lock appl_lock_list Lock
Lock lock Lock
Lock lock_wait Lock
Table 11. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Tables event_table Always collected
Deadlocks1 lock Always collected
Deadlocks1 event_dlconn Always collected
Deadlocks with Details1 event_detailed_dlconn 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

Along with table_name, this element can help you determine the source of contention for resources.

For application-level, application-lock-level, deadlock-monitoring-level, this is the schema of the table that the application is waiting to lock, because it is currently locked by another application. This element is only set if lock_object_type indicates that the application is waiting to obtain a table lock. For snapshot monitoring at the application-level and application-lock levels, this item is only valid when the lock monitor group information is set to ON.

For snapshot monitoring at the object-lock level, this item is returned for table and row level locks. The table reported at this level is the table against which this application holds these locks.

For snapshot and event monitoring at the table level, this element identifies the schema of the table for which information has been collected. For temporary tables, the format for table_schema is <agent_id><auth_id>, where:
  • agent_id is the Application Handle of the application creating the temporary table
  • auth_id is the authorization ID used by the application to connect to the database

table_type - Table type monitor element

The type of table for which information is returned.

Table 12. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics Always collected
Table 13. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
Table 14. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Tables event_table Always collected

Usage

Use this element to help identify the table for which information is returned. If the table is a user table or a system catalog table, you can use table_name and table_schema to identify the table.

The type of table may be one of the following values. The possible values are text strings based on defines in the sqlmon.h file.
CATALOG_TABLE
System catalog table.
SYNOPSIS_TABLE
Synopsis table.
TEMP_TABLE
Temporary table. Information regarding temporary tables is returned, even though the tables are not kept in the database after being used. You may still find information about this type of table useful.
USER_TABLE
User table.

tablespace_auto_resize_enabled - Table space automatic resizing enabled monitor element

This element describes whether automatic resizing is enabled for the table space. A value of 1 means "Yes"; a value of 0 means "No".

Table 15. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 16. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic

Usage

This element is only applicable to DMS table spaces and non-temporary automatic storage table spaces. If this element is set to 1, then automatic resizing is enabled. See the following monitor elements for information about the rate of increase and the maximum size for the table space.
  • tablespace_max_size
  • tablespace_increase_size
  • tablespace_increase_size_percent

tablespace_content_type - Table space content type monitor element

The type of content in a table space.

Table 17. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 18. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic

Usage

The type of content in the table space (defined in sqlmon.h) can be one of the following values:
  • All types of permanent data.
    • Regular table space: SQLM_TABLESPACE_CONTENT_ANY
    • Large table space: SQLM_TABLESPACE_CONTENT_LARGE
  • System temporary data: SQLM_TABLESPACE_CONTENT_SYSTEMP
  • User temporary data: SQLM_TABLESPACE_CONTENT_USRTEMP

tablespace_cur_pool_id - Buffer pool currently being used monitor element

The buffer pool identifier for a buffer pool that a table space is currently using.

Table 19. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 20. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic
Usage
Each buffer pool is identified by a unique integer. The value of this element matches a value from column BUFFERPOOLID of view SYSCAT.BUFFERPOOLS.

tablespace_current_size - Current table space size monitor element

This element shows the current size of the table space in bytes.

Table 21. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
For DMS and automatic storage table spaces, this element represents the total size of all table space containers in bytes. This value is equal to the total pages for the table space (tablespace_total_pages) multiplied by the table space's page size (tablespace_page_size). This element is not applicable for SMS table spaces, or for temporary automatic storage table spaces.

On table space creation for an automatic storage table space, the current size might not match the initial size. The value of current size will be within page size multiplied by extent size multiplied by the number of storage paths of the initial size on creation (usually greater, but sometimes smaller). It will always be less than or equal to tablespace_max_size (if set). This is because containers can only grow by full extents, and must be grown as a set.

tablespace_extent_size - Table space extent size monitor element

The extent size used by a table space.

Table 22. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 23. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic

tablespace_free_pages - Free pages in table space monitor element

The total number of pages that are currently free in a table space.

Table 24. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 25. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic

Usage

This is applicable only to a DMS table space.

tablespace_id - Table space identification monitor element

An integer that uniquely represents a table space used by the current database.

Table 27. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic
Table table Basic
Table 28. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Tables event_table Always collected

Usage

The value of this element matches a value from column TBSPACEID of view SYSCAT.TABLESPACES.

tablespace_increase_size - Increase size in bytes monitor element

This element shows the size that an auto-resize table space will increase by in bytes when the table space becomes full and more space is required.

Table 29. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 30. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
This represents the amount of space that will be added to a table space that can be automatically resized when it becomes full, more space is being requested, and the maximum table space size has not been reached. If the value of this element is -1 (or AUTOMATIC in the snapshot output), then DB2® automatically determines the value when space needs to be added. This element is only applicable to table spaces that are enabled to be automatically resized.

tablespace_increase_size_percent - Increase size by percent monitor element

This element shows the amount by which an auto-resize table space will increase when the table space becomes full and more space is required. The actual number of bytes is determined at the time the table space is resized based on the size of the table space at that time.

Table 31. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 32. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
This represents the amount of space that will be added to a table space that can be automatically resized when it becomes full, more space is being requested, and the maximum table space size has not been reached. The growth rate is based on a percentage of the current table space size (tablespace_current_size) at the time the table space is resized. This element is only applicable to table spaces that are enabled to be automatically resized.

tablespace_initial_size - Initial table space size monitor element

The initial size of the automatic storage table space in bytes.

Table 33. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 34. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
For non-temporary automatic storage table spaces, this monitor element represents the initial size in bytes for the table space when it was created.

tablespace_last_resize_failed - Last resize attempt failed monitor element

This element describes whether or not the last attempt to automatically increase the size of the table space failed. A value of 1 means yes, 0 means no.

Table 35. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 36. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
For an automatic storage table space, this element may show that there is no space left on any of the database's storage paths. For a non-automatic storage table space, a failure means that one of the containers could not be extended because its filesystem was full. Another reason for failure is that the maximum size of the table space has been reached. This element is only applicable to table spaces that are enabled to be automatically resized.

tablespace_last_resize_time - Time of last successful resize monitor element

This element shows a timestamp representing the last time that the size of the table space was successfully increased.

Table 37. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 38. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
For table spaces that can be automatically resized, this element represents the last time that space was automatically added to the table space when it became full, more space was being requested, and the maximum table space size had not been reached. This element is only applicable to table spaces that are enabled to be automatically resized.

tablespace_max_size - Maximum table space size monitor element

This element shows the maximum size in bytes to which the table space can automatically resize or increase.

Table 39. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 40. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
This represents the maximum size in bytes to which a table space that can be automatically resized can automatically increase. If this value is equal to the tablespace_current_size element, then there is no room for the table space to grow. If the value of this element is -1, then the maximum size is considered to be unlimited and the table space can automatically resize until the file systems are full or the architectural size limit of the table space is reached. (This limit is described in the SQL Limits appendix of the SQL Reference ). This element is only applicable to table spaces that are enabled for automatic resizing.

tablespace_min_recovery_time - Minimum recovery time for rollforward monitor element

A timestamp showing the earliest point in time to which a table space can be rolled forward. The timestamp reflects local time.

Table 41. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 42. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic

Usage

Displayed only if non zero.

tablespace_name - Table space name monitor element

The name of a table space.

Table 44. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic
Lock appl_lock_list Basic
Lock lock Lock
Lock lock_wait Lock
Table 45. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks1 lock -
Deadlocks1 event_dlconn -
Deadlocks with Details1 event_detailed_dlconn -
Table Space tablespace_list -
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

This element can help you determine the source of contention for resources.

It is equivalent to the TBSPACE column in the database catalog table SYSCAT.TABLESPACES. At the application level, application-lock level, and deadlock monitoring level, this is the name of the table space that the application is waiting to lock. Another application currently holds a lock on this table space.

At the lock level, this is the name of the table space against which the application currently holds a lock.

At the table space level (when the buffer pool monitor group is ON), this is the name of the table space for which information is returned.

This element will not be returned for a table lock held on a partitioned table.

tablespace_next_pool_id - Buffer pool that will be used at next startup monitor element

The buffer pool identifier for a buffer pool that a table space will use at the next database startup.

Table 46. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 47. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic
Usage
Each buffer pool is identified by a unique integer. The value of this element matches a value from column BUFFERPOOLID of view SYSCAT.BUFFERPOOLS

tablespace_num_containers - Number of Containers in Table Space monitor element

Total number of containers in the table space.

Table 48. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 49. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic

tablespace_num_quiescers - Number of Quiescers monitor element

The number of users quiescing the table space (can be in the range of 0 to 5).

Table 50. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 51. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
This value represents the number of agents that have quiesced the table space (either in "SHARE", "UPDATE", or "EXCLUSIVE" mode). For each quiescer, the following information is returned in a tablespace_quiescer logical data group:
  • User authorization ID of the quiescer
  • Agent ID of the quiescer
  • Table space ID of the object that was quiesced that resulted in this table space being quiesced
  • Object ID of the object that was quiesced that resulted in this table space being quiesced
  • Quiesce state

tablespace_num_ranges - Number of Ranges in the Table Space Map monitor element

The number of ranges (entries) in the table space map. This can be in the range of 1 to 100's (but is usually less than a dozen). The table space map only exists for DMS table spaces.

Table 52. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 53. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic

tablespace_page_size - Table space page size monitor element

Page size used by a table space in bytes.

Table 54. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 55. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic

tablespace_page_top - Table space high watermark monitor element

The page in a table space that is holding the high watermark.

Table 56. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 57. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic

Usage

For DMS, this element represents the page number of the first free extent following the last allocated extent of a table space. Note that this is not really a "high watermark", but rather a "current watermark", since the value can decrease. For SMS, this is not applicable.

tablespace_paths_dropped - Table space using dropped path monitor element

Indicates that the table space is using a storage path that has been dropped.

Table 58. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 59. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic

Usage

For table spaces using automatic storage, use this monitor element to determine whether any of the table space containers reside on a storage path that has been dropped. Before storage paths are physically dropped from the database, all table spaces must stop using them. To stop using a dropped storage path, either drop the table space or rebalance the table space using the REBALANCE clause of the ALTER TABLESPACE statement.

tablespace_pending_free_pages - Pending free pages in table space monitor element

The number of pages in a table space which would become free if all pending transactions are committed or rolled back and new space is requested for an object.

Table 60. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 61. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic

Usage

This is applicable only to a DMS table space.

tablespace_prefetch_size - Table space prefetch size monitor element

The maximum number of pages the prefetcher gets from the disk at a time.

Table 62. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 63. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic
Table Space tablespace_nodeinfo Basic

Usage

  • For table function monitoring, this element always reports the actual value for the table space prefetch size.
  • For snapshot monitoring, if automatic prefetch size is enabled, this element reports the value "-1" in the tablespace Logical Data Grouping, and the actual value is reported in the tablespace_nodeinfo Logical Data Grouping.
  • For snapshot monitoring, if automatic prefetch size is not enabled, this element reports the actual value in the tablespace Logical Data Grouping, and the element does not appear in the tablespace_nodeinfo Logical Data Grouping.

tablespace_state - Table space state monitor element

This element describes the current state of a table space.

Table 64. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 65. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic

Usage

In administrative views and table functions, this monitor element returns a text identifier based on defines in sqlutil.h, and is combination of the following values separated by a '+' sign:
  • BACKUP_IN_PROGRESS
  • BACKUP_PENDING
  • DELETE_PENDING
  • DISABLE_PENDING
  • DROP_PENDING
  • LOAD_IN_PROGRESS
  • LOAD_PENDING
  • MOVE_IN_PROGRESS
  • NORMAL
  • OFFLINE
  • PSTAT_CREATION
  • PSTAT_DELETION
  • QUIESCED_EXCLUSIVE
  • QUIESCED_SHARE
  • QUIESCED_UPDATE
  • REBAL_IN_PROGRESS
  • REDIST_IN_PROGRESS
  • REORG_IN_PROGRESS
  • RESTORE_IN_PROGRESS
  • RESTORE_PENDING
  • ROLLFORWARD_IN_PROGRESS
  • ROLLFORWARD_PENDING
  • STORDEF_ALLOWED
  • STORDEF_CHANGED
  • STORDEF_FINAL_VERSION
  • STORDEF_PENDING
  • SUSPEND_WRITE
This element contains a hexadecimal value indicating the current table space state. The externally visible state of a table space is composed of the hexadecimal sum of certain state values. For example, if the state is "quiesced: EXCLUSIVE" and "Load pending", the value is 0x0004 + 0x0008, which is 0x000c. Use the db2tbst command to obtain the table space state associated with a given hexadecimal value.
Table 66. Bit definitions listed in sqlutil.h
Hexadecimal Value Decimal Value State
0x0 0 Normal (see the definition SQLB_NORMAL in sqlutil.h)
0x1 1 Quiesced: SHARE
0x2 2 Quiesced: UPDATE
0x4 4 Quiesced: EXCLUSIVE
0x8 8 Load pending
0x10 16 Delete pending
0x20 32 Backup pending
0x40 64 Roll forward in progress
0x80 128 Roll forward pending
0x100 256 Restore pending
0x100 256 Recovery pending (not used)
0x200 512 Disable pending
0x400 1024 Reorg in progress
0x800 2048 Backup in progress
0x1000 4096 Storage must be defined
0x2000 8192 Restore in progress
0x4000 16384 Offline and not accessible
0x8000 32768 Drop pending
0x10000 65536 No write is allowed
0x20000 131072 Load in progress
0x40000 262144 Redistribute in progress
0x80000 524288 Move in progress
0x2000000 33554432 Storage may be defined
0x4000000 67108864 Storage Definition is in 'final' state
0x8000000 134217728 Storage Definition was changed before rollforward
0x10000000 268435456 DMS rebalancer is active
0x20000000 536870912 TBS deletion in progress
0x40000000 1073741824 TBS creation in progress
Note: DB2 LOAD does not set the table space state to Load pending or Delete pending.

tablespace_state_change_object_id - State Change Object Identification monitor element

The object that caused the table space state to be set to "Load pending" or "Delete pending".

Element identifier
tablespace_state_change_object_id
Element type
information
Table 67. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
This element is meaningful only if the table space state is "Load pending" or "Delete pending". If nonzero, the value of this element matches a value from column TABLEID of view SYSCAT.TABLES.
Note: DB2 LOAD does not set the table space state to Load pending or Delete pending.

tablespace_state_change_ts_id - State Change Table Space Identification monitor element

If the table space state is "Load pending" or "Delete pending", this shows the table space ID of the object that caused the table space state to be set.

Element identifier
tablespace_state_change_ts_id
Element type
information
Table 68. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic
Usage
This element is meaningful only if the table space state is "Load pending" or "Delete pending". If nonzero, the value of this element matches a value from column TABLESPACEID of view SYSCAT.TABLES.
Note: DB2 LOAD does not set the table space state to Load pending or Delete pending.

tablespace_total_pages - Total pages in table space monitor element

Total number of pages in a table space.

Table 69. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 70. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic (DMS table spaces)

Buffer Pool (SMS table spaces)

Usage

Total operating system space occupied by a table space. For DMS, this is the sum of the container sizes. For SMS, this is the sum of all file space used for the tables stored in this table space (and is only collected if the buffer pool switch is on).

tablespace_type - Table space type monitor element

The type of a table space.

Table 71. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 72. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic

Usage

This element shows whether this table space is a database managed table space (DMS), or system managed table space (SMS).

The values for tablespace_type (defined in sqlmon.h) are as follows:
  • For DMS: SQLM_TABLESPACE_TYP_DMS
  • For SMS: SQLM_TABLESPACE_TYP_SMS

tablespace_usable_pages - Usable pages in table space monitor element

The total number of pages in a table space minus overhead pages.

Table 73. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 74. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic (DMS table spaces)

Buffer Pool (SMS table spaces)

Usage

This element is applicable to DMS table spaces only. For SMS table spaces, this element will have the same value as the tablespace_total_pages monitor element.

During a table space rebalance, the number of usable pages will include pages for the newly added container, but these new pages may not be reflected in the number of free pages until the rebalance is complete. When a table space rebalance is not taking place, the number of used pages plus the number of free pages, plus the number of pending free pages will equal the number of usable pages.

tablespace_used_pages - Used pages in table space monitor element

The total number of pages that are currently used (not free) in a table space.

Table 75. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 76. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_nodeinfo Basic (DMS table spaces)

Buffer Pool (SMS table spaces)

Usage

This is the total number of pages in use for a DMS table space. For an SMS table space it is equal to the value of tablespace_total_pages monitor element.

tablespace_using_auto_storage - Table space enabled for automatic storage monitor element

This element describes whether the table space was created as an automatic storage table space. A value of 1 means "Yes"; a value of 0 means "No".

Table 77. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected
Table 78. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace Basic

Usage

You can use this element to determine whether the given table space was created using automatic storage (that is, created with the MANAGED BY AUTOMATIC STORAGE clause), rather than with containers that are explicitly provided. The table space can have containers that exist on some or all of the storage paths associated with the database.

target_cf_gbp_size - Target cluster caching facility group buffer pool size monitor element

During a dynamic resize, this monitor element shows the group buffer pool memory target value, in pages with a page size of 4 KB. A resize is complete when the target value matches the configured value.

Table 81. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

target_cf_lock_size - Target cluster caching facility lock size monitor element

During a dynamic resize, this monitor element shows the global lock memory target value, in pages with a page size of 4 KB. A resize is complete when the target value matches the configured value.

Table 82. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

target_cf_sca_size - Target cluster caching facility shared communications area size monitor element

During a dynamic resize, this monitor element shows the shared communications area memory target value, in pages with a page size of 4 KB. A resize is complete when the target value matches the configured value.

Table 83. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

tbsp_datatag - Table space data tag monitor element

This element identifies the effective data tag value for a table space. The effective data tag is the data tag value that was specified explicitly for the table space or inherited from the table space storage group. Valid user-specified range is 1 through 9; 0 indicates no data tag specified.
Table 84. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - get table space metrics Always collected

Usage note

A data tag is used to identify and group data that can be referenced within WLM configurations. The WLM configurations determine the effect of the tagging which may affect the processing priority of user work.

tbsp_last_consec_page - Last consecutive object table page monitor element

Object relative page number of the last contiguous meta-data page for the table space. This value is only valid for DMS table spaces. It is 0 otherwise.

Table 85. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected

tbsp_max_page_top - Maximum table space page high watermark monitor element

The highest allocated page number for a DMS table space since the database was activated.

Table 86. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always collected

Usage

This value changes whenever the value of the tablespace_page_top monitor element increases.

tbsp_names - Table space names monitor element

This element lists the table space names that the utility acts on.

Table 87. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_UTILITY table function - Get utilities running on the database Always collected
Table 88. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History UTILSTART Always collected

Usage

For the change history event monitor , if the object_type element is DATABASE or TABLESPACE, this is a comma delimited list of table space names that the utility acts on.

tbsp_trackmod_state - Table space trackmod state monitor element

The modification state that a table space is in with respect to the last or next backup.

Table 89. Table function monitoring information
Table Function Monitor Element Collection Level
MON_GET_TABLESPACE table function - Get table space metrics Always

Usage

You can use this monitor element to determine the modification status of a table space. The status of a table space can be in one of the following states:
CLEAN
No modifications occurred in the table space since the previous backup. If an incremental or delta backup is executed at this time, no data pages from this table space would be backed up.
DIRTY
Table space contains data that needs to be picked up by the next backup.
ININCREMENTAL
Table space contains modifications that were copied into an incremental backup. This state is in a DIRTY state relative to a full backup such that a future incremental backup needs to include some pages from this pool. This state is also in a CLEAN state such that a future delta backup does not need to include any pages from this pool.
READFULL
The latest table space modification state change was caused by a dirty table space that is being read by a full backup that might not have completed successfully, or is currently in progress.
READINCREMENTAL
The latest table space modification state change was caused by a dirty table space that is being read by an incremental backup that might not have completed successfully, or is currently in progress.
UNAVAILABLE
The trackmod configuration parameter is set to No. Therefore, no table space modification status information is available.

tcpip_recv_volume - TCP/IP received volume monitor element

The amount of data received by the data server from clients over TCP/IP. This value is reported in bytes.

Table 91. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

tcpip_recv_wait_time - TCP/IP received wait time monitor element

The time spent waiting for an incoming client request over TCP/IP excluding idle time. The value is given in milliseconds.

Table 93. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

tcpip_recvs_total - TCP/IP receives total monitor element

The number of times data was received by the database server from the client application over TCP/IP.

Table 95. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

tcpip_send_volume - TCP/IP send volume monitor element

The amount of data sent by data server to client. This value is reported in bytes.

Table 97. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

tcpip_send_wait_time - TCP/IP send wait time monitor element

Time spent blocking on a TCP/IP send to the client. The value is given in milliseconds.

Table 99. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

tcpip_sends_total - TCP/IP sends total monitor element

The number of times data was sent from the database server to the client application over TCP/IP.

Table 101. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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 uow_metrics REQUEST METRICS BASE

temp_tablespace_top - Temporary table space top monitor element

The temp_tablespace_top monitor element is a high watermark, in KB, for the temporary table space usage of DML activities at all nesting levels in a service class or work class. For service classes, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service class is set to NONE. For work classes, this monitor elements returns -1 if no COLLECT AGGREGATE ACTIVITY DATA work action is specified for the work class. For workloads, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the workload is set to NONE.

For service classes, when you remap activities between service subclasses with a REMAP ACTIVITY action, only the temp_tablespace_top high watermark of the service subclass where an activity completes is changed. High watermarks of service subclasses an activity is mapped to but does not complete in are unaffected.

Table 102. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats -
Statistics event_wcstats -
Statistics event_wlstats -

Usage

Use this element to determine the highest DML activity system temporary table space usage reached on a member for a service class, workload, or work class in the time interval collected.

This element is only updated by activities that have a temporary table space threshold applied to them. If no temporary table space threshold is applied to an activity, a value of 0 is returned.

territory_code - Database Territory Code monitor element

The territory code of the database for which the monitor data is collected. This monitor element was formerly known as country_code.

Table 103. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
Application appl Basic
Table 104. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Event Log Header event_log_header Always collected
Connections event_connheader Always collected
Usage
Territory code information is recorded in the database configuration file.

For DRDA AS connections, this element will be set to 0.

thresh_violations - Number of threshold violations monitor element

Number of times a threshold was violated. This monitor element is an alias of the num_threshold_violations - Number of threshold violations monitor element monitor element, which is returned by snapshot monitoring routines and the Database event monitor.
Table 105. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 106. 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_wlstats (reported in the metrics document) REQUEST METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

Use this element to quickly determine if there have been any WLM thresholds that have been violated. If thresholds have been violated you can then use the threshold violations event monitor (if created and active) to obtain details about the threshold violations.

For example, to obtain details which threshold was violated.

threshold_action - Threshold action monitor element

The action of the threshold to which this threshold violation record applies. Possible values include Stop, Continue and Remap.

Table 107. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Threshold violations event_thresholdviolations -

Usage

Use this element to determine whether the activity that violated the threshold was stopped when the violation occurred, was allowed to continue executing, or was remapped to another service subclass. If the activity was stopped, the application that submitted the activity will have received an SQL4712N error. If the activity was remapped to another service subclass, agents working for the activity on the member will be moving to the target service subclass of the threshold.

threshold_domain - Threshold domain monitor element

The domain of the threshold responsible for this queue.

Possible values are
  • Database
  • Work Action Set
  • Service Superclass
  • Service Subclass
  • Workload
Table 108. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
WLM_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
Table 109. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_qstats Always collected

Usage

This element can be used for distinguishing the queue statistics of thresholds that have the same predicate but different domains.

threshold_maxvalue - Threshold maximum value monitor element

For non-queuing thresholds, this monitor element represents the value that was exceeded to cause this threshold violation. For queuing thresholds, this monitor element represents the level of concurrency that caused the queuing. The level of concurrency that caused the violation of the queuing threshold is the sum of threshold_maxvalue and threshold_queuesize monitor elements.
Table 110. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Threshold violations event_thresholdviolations Always collected

Usage

For activity thresholds, this element provides a historical record of what the threshold's maximum value was at the time the threshold was violated. This is useful when the threshold's maximum value has changed since the time of the violation and the old value is no longer available from the SYSCAT.THRESHOLDS view. For the DATATAGINSC IN and DATATAGINSC NOT IN thresholds, this element contains the value of the data tag that violated the threshold.

threshold_name - Threshold name monitor element

The unique name of the threshold responsible for this queue.

Table 111. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
WLM_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
Table 112. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_qstats Always collected

Usage

Use this element to uniquely identify the queuing threshold whose statistics this record represents.

threshold_predicate - Threshold predicate monitor element

Identifies the type of threshold that was violated or for which statistics were collected.

Table 113. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
WLM_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
Table 114. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Threshold violations event_thresholdviolations Always collected
Statistics event_qstats Always collected

Usage

Use this monitor element in conjunction with other statistics or threshold violation monitor elements for analysis of a threshold violation.

The valid values for this monitor element when reported in the event_thresholdviolations logical group are:
AggSQLTempSpace
SQLTempSpace
SQLRowsReturned
ActivityTotalTime
EstimatedSQLCost
TotalMemberConnections
ConnectionIdleTime
ConcurrentWorkloadOccurrences
ConcurrentWorkloadActivities
ConcurrentDBCoordActivities
TotalSCMemberConnections
SQLRowsRead
SQLRowsReadInSC
CPUTime
CPUTimeInSC
UowTotalTime
DataTagInSC
DataTagNotInSC
The valid values for this monitor element when reported in the event_qstats logical group are:
TotalMemberConnections
ConcurrentDBCoordActivities
TotalSCMemberConnections

threshold_queuesize - Threshold queue size monitor element

The size of the queue for a queuing threshold. An attempt to exceed this size causes a threshold violation. For a non-queuing threshold, this value is 0.

Table 115. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Threshold violations event_thresholdviolations -

Usage

Use this element to determine the number of activities or connections in the queue for this threshold at the time the threshold was violated.

thresholdid - Threshold ID monitor element

Identifies the threshold to which a threshold violation record applies or for which queue statistics were collected.

Table 116. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
WLM_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
Table 117. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Threshold violations event_thresholdviolations Always collected
Statistics event_qstats Always collected

Usage

Use this monitor element in conjunction with other activity history monitor elements for analysis of a threshold queue or for analysis of the activity that violated a threshold.

time_completed - Time completed monitor element

The time at which the activity described by this activity record finished executing. This element is a local timestamp.

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

Usage

Use this element in conjunction with other activity history elements for analysis of the behavior of an activity.

This field has a value of "0000-00-00-00.00.00.000000" when a full activity record could not be written to a table event monitor due to memory limitations. If the activity was captured while it was in progress, then this field represents the time that activity was collected.

time_completed - Time completed monitor element

The time at which the activity described by this activity record finished executing. This element is a local timestamp.

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

Usage

Use this element in conjunction with other activity history elements for analysis of the behavior of an activity.

This field has a value of "0000-00-00-00.00.00.000000" when a full activity record could not be written to a table event monitor due to memory limitations. If the activity was captured while it was in progress, then this field represents the time that activity was collected.

time_created - Time created monitor element

The time at which a user submitted the activity described by this activity record. This element is a local timestamp.

Table 120. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity -

Usage

Use this element in conjunction with other activity history elements for analysis of the behavior of an activity.

time_of_violation - Time of violation monitor element

The time at which the threshold violation described in this threshold violation record occurred. This element is a local timestamp.

Table 121. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Threshold violations event_thresholdviolations -

Usage

Use this element in conjunction with other threshold violations monitor elements for analysis of a threshold violation.

time_since_last_recv - Time since last message received monitor element

The time since the last message was received. Normally, this number is no more than heartbeat_interval because an HADR database sends out a heartbeat message on heartbeat interval when the channel is idle. A larger number indicates a delay in message delivery. When this number reaches hadr_timeout, the connection is closed. Units are milliseconds.

Table 122. 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

time_stamp - Snapshot Time monitor element

The date and time when the database system monitor information was collected.

Table 123. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager collected Basic
Usage
You can use this element to help relate data chronologically if you are saving the results in a file or database for ongoing analysis.

time_started - Time started monitor element

The time at which the activity described by this activity record began executing. This element is a local timestamp.

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

Usage

Use this element in conjunction with other activity history elements for analysis of the behavior of an activity.

If the activity got rejected, then the value of act_exec_time monitor element is 0. In this case, the value of time_started monitor element equals the value of time_completed monitor element.

time_zone_disp - Time Zone Displacement monitor element

Number of seconds that the local time zone is displaced from Greenwich Mean Time (GMT).

Table 125. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager collected Basic
Usage
All time reported by reported by the database system monitor is GMT, this displacement calculates the local time.

top - Histogram bin top monitor element

The inclusive top end of the range of a histogram bin. The value of this monitor element is also the bottom exclusive end of the range of the next histogram bin.

Table 128. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_histogrambin -

Usage

Use this element with the corresponding bottom element to determine the range of a bin within a histogram.

tot_log_used_top - Maximum Total Log Space Used monitor element

The maximum amount of total log space used (in bytes).

Table 129. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 130. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 131. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
You can use this element to help evaluate the amount of primary log space that you have allocated. Comparing the value of this element with the amount of primary log space you have allocated can help you to evaluate your configuration parameter settings. Your primary log space allocation can be calculated using the following formula:
 
     logprimary x logfilsiz x 4096 (see note below)

You can use this element in conjunction with sec_log_used_top and sec_logs_allocated to show your current dependency on secondary logs.

This value includes space used in both primary and secondary log files.

You may need to adjust the following configuration parameters:
  • logfilsiz
  • logprimary
  • logsecond
Note: While the database system monitor information is given in bytes, the configuration parameters are set in pages, which are each 4K bytes.

total_act_time - Total activity time monitor element

The total amount of time spent executing activities. This value is given in milliseconds.

Table 132. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_ROUTINE_EXEC_LIST table function - get list of statements executed by routine ACTIVITY 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_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 133. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE
Statistics event_scmetrics
event_wlmetrics
REQUEST METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

During its life cycle, an activity can spend time in various states, which are reported by the activity_state event monitor element. Some of the states for an activity include:
  • EXECUTING - This state indicates that the coordinator agent is working on the activity. An activity that encounters a lock wait situation is reported as executing.
  • IDLE - This state indicates that the coordinator agent is waiting for the next request from a client.
  • QUEUED - Some thresholds include a built-in queue. This state indicates that the activity is waiting in the queue for its turn to begin executing.
This monitor element returns only SQL execution time and does not include time when the statement was idle.
Use this monitor element along with the total_act_wait_time monitor element to determine the percentage of time the data server spent working on the activity.
(total_act_time - total_act_wait_time)/(total_act_time) = % of time data server is actively working on activity

total_act_wait_time - Total activity wait time monitor element

Total time spent waiting within the DB2 database server, while processing an activity. The value is given in milliseconds.

Table 134. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_ROUTINE_EXEC_LIST table function - get list of statements executed by routine ACTIVITY 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_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 135. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE
Statistics event_scmetrics
event_wlmetrics
REQUEST METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

Use this monitor element along with the total_act_time monitor element to determine the percentage of time the data server spent working on the activity.
(total_act_time - total_act_wait_time)/(total_act_time) = % of time data server is actively working on activity

total_app_commits - Total application commits monitor elements

Total number of commit statements issued by the client application.

Table 137. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_app_rollbacks - Total application rollbacks monitor element

Total number of rollback statements issued by the client application.

Table 139. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

Usage

If you issue a CONNECT RESET or TERMINATE command, you also initiate a rollback operation. This operation increments the total_app_rollbacks counter by 1.

total_app_rqst_time - Total application request time monitor element

The total elapsed time spent on application requests; this is the total time spent by coordinator agents on the server executing application requests. This value is reported in milliseconds.

Usage

Use this monitor element to determine the time that the application request spent in the DB2 data server. This value can be used to help determine if the data server is the source of an observed performance problem.

For example, if a user reports that there is a problem with an application and it has taken 20 minutes to return, and if you determine that total application request time is 1 minute and there are currently no application requests in progress for the connection, then the performance problem might lie outside of the DB2 data server.

total_app_section_executions - Total application section executions monitor element

Number of section executions performed by an application.

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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 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_wlstats (reported in the metrics document) REQUEST METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

total_async_runstats - Total number of asynchronous RUNSTATS requests monitor element

The total number of successful asynchronous RUNSTATS activities performed by real-time statistics gathering for all the applications in the database. Values reported by all the database partitions are aggregated together.

Table 143. 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 144. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Statement
For snapshot monitoring, this counter can be reset.
Table 145. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

Use this element to determine how many successful asynchronous RUNSTATS activities have been performed by real-time statistics gathering. This value changes frequently. In order to get a better view of the system usage, take a snapshot at specific intervals over an extended period of time. When used in conjunction with sync_runstats and stats_fabrications monitor elements, this element can help you to track the different types of statistics collection activities related to real-time statistics gathering and analyze their performance impact.

total_backup_proc_time - Total non-wait time for online backups monitor element

The total amount of processing (non-wait) time that was spent doing online backups. The value is in milliseconds.

Table 147. Event monitoring information
Event type Logical data grouping Monitor switch
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE

Usage

Use the total_backup_proc_time monitor element to determine the portion of the elapsed time that was spent in online backups that is spent only processing requests. The total_backup_time monitor element indicates the total amount of elapsed time that is spent in online backups.

total_backup_time - Total elapsed time for doing online backups monitor element

The total elapsed time that was spent doing online backups. The value is in milliseconds.

Table 149. Event monitoring information
Event type Logical data grouping Monitor switch
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE

Usage

Use the total_backup_time monitor element to determine the portion of the total amount of time that was spent on database requests that was used for backup operations. The total_rqst_time monitor element determines the total amount of time that is spent on database requests.

total_backups - Total online backups monitor element

The total number of online backups.

Table 151. Event monitoring information
Event type Logical data grouping Monitor switch
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE

total_buffers_rcvd - Total FCM Buffers Received monitor element

For snapshot monitor, this monitor element reports the total number of FCM buffers received by the node issuing the GET SNAPSHOT command from the node identified by the node_number monitor element. For table function monitor, this monitor element reports the total number of FCM buffers received from a remote database member.

Table 152. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM_CONNECTION_LIST - Get details for all FCM connections Always collected
Table 153. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm_node Basic

Usage

Use this element to measure the level of traffic between the current member and the remote member. If the total number of FCM buffers received from this member is high, consider redistributing the database or moving tables to reduce the traffic between members.

total_buffers_sent - Total FCM Buffers Sent monitor element

For snapshot monitor, this monitor element reports the total number of FCM buffers that have been sent from the node issuing the GET SNAPSHOT command to the node identified by the node_number monitor element. For table function monitor, this monitor element reports the total number of FCM buffers sent from the current database member to a remote database member.

Table 154. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM_CONNECTION_LIST - Get details for all FCM connections Always collected
Table 155. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm_node Basic

Usage

Use this element to measure the level of traffic between the current member and the remote member. If the total number of FCM buffers sent to this member is high, consider redistributing the database or moving tables to reduce the traffic between members.

total_col_executions - Total column-organized executions monitor element

Total number of times that data in column-organized tables was accessed.

Table 158. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_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 159. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

This element is incremented when the following operators are processed in a query plan:
  • Top level CTQ operator
  • INSERT, UPDATE, and DELETE operators on a column-organized table.

total_col_proc_time - Total column-organized processing time monitor element

The total non-wait processing time spent accessing columnar data in a query oncolumn-organized tables. The value is given in milliseconds. This element is a subset of the elapsed time returned by the monitor element total_col_time, and represents the time in which the column-organized processing subagents were not idle on a measured wait time (for example, lock wait or IO).

Table 160. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_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 161. 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 REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

total_col_time - Total column-organized time monitor element

The total elapsed time spent accessing columnar data in a query oncolumn-organized tables. The value is given in milliseconds.

Table 162. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_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 163. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

total_col_vector_consumers - Total columnar vector memory consumers monitor element

The total number of consumers of columnar vector memory that ran.

Table 164. 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 - return information about an activity as an XML document ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get connection metrics as an XML document REQUEST METRICS BASE
MON_GET_DATABASE table function - get database information metrics REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics 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 table function - get aggregated routine execution metrics REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated routine execution metrics as an XML document 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_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 workload metrics as an XML document REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 165. Event monitoring information
Event type Logical data grouping Monitor switch
Activity Metrics event_activitymetrics ACTIVITY METRICS BASE
Package Cache Metrics pkgcache_metrics ACTIVITY METRICS BASE
Statistics Metrics event_scmetrics REQUEST METRICS BASE
Statistics Metrics event_wlmetrics REQUEST METRICS BASE
Unit of Work Metrics uow_metrics REQUEST METRICS BASE

total_commit_proc_time - Total commits processing time monitor element

The total amount of processing (non-wait) time spent performing commit processing on the database server. The value is given in milliseconds.

Table 167. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_commit_time - Total commit time monitor element

The total amount of time spent performing commit processing on the database server. The value is given in milliseconds.

Table 169. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_compilations - Total compilations monitor element

The total number of explicit compiles on the database server. Explicit compiles are compilations directly initiated by a user request such as a bind, rebind, prepare or execute immediate.

Table 171. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_compile_proc_time - Total compile processing time monitor element

The total amount of processing (non-wait) time spent performing explicit compiles on the database server. Explicit compiles are compilations directly initiated by a user request such as a bind, rebind, prepare or execute immediate. The value is given in milliseconds.

Table 173. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_compile_time - Total compile time monitor element

The total amount of time spent performing explicit compiles on the database server. Explicit compiles are compilations directly initiated by a user request such as a bind, rebind, prepare or execute immediate. The value is given in milliseconds.

Table 175. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_connect_authentication_proc_time - Total connection authentication processing time monitor element

The amount of processing (non-wait) time spent performing connection or switch user authentication, in milliseconds.

Table 177. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (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

total_connect_authentications - Connections or switch user authentications performed monitor element

The number of connection or switch user authentications performed.

Table 179. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml) document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE

total_connect_authentication_time - Total connection or switch user authentication request time monitor element

The amount of time spent performing connection or switch user authentication, in milliseconds.

Table 181. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml) document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE

total_connect_request_proc_time - Total connection or switch user request processing time monitor element

The amount of processing (non-wait) time spent processing a connection or switch user request, in milliseconds.

Table 183. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml) document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE

total_connect_requests - Connection or switch user requests monitor element

The total number of connection or switch user requests.

Table 185. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml) document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE

total_connect_request_time - Total connection or switch user request time monitor element

The amount of time spent performing a connection or switch user request, in milliseconds.

Table 187. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml) document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of Work uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE

total_connections - Total connections monitor element

Current number of connections.

Table 188. Table function monitoring information
Table function Monitor element collection level
MON_GET_INSTANCE table function - Get instance level information Always collected

Usage

This element can be used to help determine the appropriate setting for the max_connections configuration parameter.

This element combines local and remote connections.

total_cons - Connects Since Database Activation monitor element

Indicates the number of connections to the database since the first connect, activate, or last reset (coordinator agents).

Table 189. 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 190. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Database dbase_remote Basic
For snapshot monitoring, this counter can be reset.
Table 191. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
You can use this element in conjunction with the db_conn_time and the db2start_time monitor elements to calculate the frequency at which applications have connected to the database.

If the frequency of connects is low, you may want to explicitly activate the database using the ACTIVATE DATABASE command before connecting any other application, because of the extra processing time that is associated with the first connect to a database (for example, initial buffer pool allocation). This will result in subsequent connects being processed at a higher rate.

Note: When you reset this element, its value is set to the number of applications that are currently connected, not to zero.

total_cpu_time - Total CPU time monitor element

The total amount of CPU time used while within DB2. Represents total of both user and system CPU time. This value is given in microseconds.

When returned by the WLM_GET_SERVICE_SUBCLASS_STATS or the WLM_GET_WORKLOAD_STATS table function, it represents the total CPU time since the last reset of statistics. When returned by the MON_SAMPLE_SERVICE_CLASS_METRICS or the MON_SAMPLE_WORKLOAD_METRICS table function, it represents the total CPU time since the function was executed.

When returned by the MON_GET_ROUTINE or MON_GET_ROUTINE_DETAILS table function, this element represents the total CPU time spent in agents and subagents of the current member for this routine. CPU time spent in fenced processes is not included.

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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_ROUTINE_EXEC_LIST table function - get list of statements executed by routine ACTIVITY 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_SUBCLASS_STATS table function - Return statistics of service subclasses 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
MON_GET_WORKLOAD_STATS table function - Return workload statistics REQUEST METRICS BASE
MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics REQUEST METRICS BASE
MON_SAMPLE_WORKLOAD_METRICS - Get sample workload metrics REQUEST METRICS BASE
WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities REQUEST METRICS BASE
WLM_GET_WORKLOAD_STATS table function - Return workload statistics REQUEST METRICS BASE
Table 193. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Together with the total_disp_run_queue_time monitor element, total_cpu_time can be used to compute a measure of the amount of contention for the CPU resource, measured on a scale from 0 to 1, with lower numbers meaning greater contention for the CPU resource. This measure, called CPU velocity, is computed by measuring the amount of time that work in the service class has access to the CPU divided by the total time spent accessing the CPU or waiting to access the CPU. CPU velocity gives a measure of how efficiently the work executing in the service class is being executed relative to how efficiently it could be executed if such work never had to wait for the CPU. The formula is as follows:
CPU velocity = total_cpu_time / (total_cpu_time + total_disp_run_queue_time)

total_disp_run_queue_time - Total dispatcher run queue time monitor element

The total time that requests, that were run in this service class, spent waiting to access the CPU. This value is given in microseconds.

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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - 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_SUBCLASS_STATS table function - Return statistics of service subclasses 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
MON_GET_WORKLOAD_STATS table function - Return workload statistics REQUEST METRICS BASE
MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics REQUEST METRICS BASE
MON_SAMPLE_WORKLOAD_METRICS - Get sample workload metrics REQUEST METRICS BASE
WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses REQUEST METRICS BASE
WLM_GET_WORKLOAD_STATS table function - Return workload statistics 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) ACTIVITY METRICS BASE
Package cache Reported in the activity_metrics document 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

Usage

Together with the total_cpu_time, the total_disp_run_queue_time monitor element can be used to compute a measure of the amount of contention for the CPU resource, measured on a scale from 0 to 1, with lower numbers meaning greater contention for the CPU resource. This measure, called CPU velocity, is computed by measuring the amount of time that work in a service class has access to the CPU divided by the total time spent accessing the CPU or waiting to access the CPU. It gives a measure of how efficiently the work is being executed relative to how efficiently it could be executed if such work never had to wait for the CPU. The formula is as follows:
CPU velocity = total_cpu_time / (total_cpu_time + total_disp_run_queue_time)

When returned by the WLM_GET_SERVICE_SUBCLASS_STATS or the WLM_GET_WORKLOAD_STATS function, this monitor element represents the total dispatcher run queue wait time since the last reset of statistics.

When returned by the MON_SAMPLE_SERVICE_CLASS_METRICS or the MON_SAMPLE_WORKLOAD_METRICS function, this monitor element represents the total dispatcher run queue wait time since the function was executed.

total_exec_time - Elapsed statement execution time monitor element

The total time in seconds and microseconds that was spent executing a particular statement in the SQL cache.

Table 196. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Dynamic SQL dynsql Statement
For snapshot monitoring, this counter can be reset.

Usage

Use this element with num_executions monitor element determine the average elapsed time for the statement and identify the SQL statements that would most benefit from a tuning of their SQL. The num_compilation monitor element must be considered when evaluating the contents of this element.

Note: Due to the differences in granularity with which the DB2 system collects statistics, the value of the total_exec_time monitor element might not equal the sum of values of system_cpu_time and user_cpu_time monitor elements. In this case, the sum of system_cpu_time and user_cpu_time monitor elements more accurately reflects the actual total execution time.

total_extended_latch_wait_time - Total extended latch wait time monitor element

The amount of time, in milliseconds, spent in extended latch waits.

Table 197. 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_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_EXTENDED_LATCH_WAITS table function - Return information for latches REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 198. 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 uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Package cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE

Usage

  • Use the following formula to determine extended latch wait time as a percentage of total wait time. This formula can be used to determine if the time spent waiting on extended latches is high relative to the total wait time.
    (TOTAL_EXTENDED_LATCH_WAIT_TIME / TOTAL_WAIT_TIME) * 100
  • Use the following formula to determine the average length of time in milliseconds of an extended latch wait.
    TOTAL_EXTENDED_LATCH_WAIT_TIME / TOTAL_EXTENDED_LATCH_WAITS

total_extended_latch_waits - Total extended latch waits monitor element

The number of extended latch waits.

Table 199. 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_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_EXTENDED_LATCH_WAITS table function - Return information for latches REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 200. 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 uow (reported in the metrics.xml document)
uow_metrics
REQUEST METRICS BASE
Package cache pkgcache (reported in the metrics.xml document)
pkgcache_metrics
ACTIVITY METRICS BASE

Usage

Use the following formula to determine the average length of time in milliseconds of an extended latch wait.
TOTAL_EXTENDED_LATCH_WAIT_TIME / TOTAL_EXTENDED_LATCH_WAITS

total_hash_grpbys - Total hash GROUP BY operations monitor element

The total number of hashed GROUP BY operations.

Table 201. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_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 202. 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 REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of work event_wlmetrics REQUEST METRICS BASE

Usage

Use this element along with the hash_grpby_overflows element to determine if a large number of hashed GROUP BY operations are overflowing to disk. If the overflow value is high and the performance of applications using hashed GROUP BY operations needs improvement, then consider increasing the size of the sort heap.

total_hash_joins - Total Hash Joins monitor element

The total number of hash joins executed.

Table 203. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 204. 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 205. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Connection event_conn Always collected
Database event_db Always collected
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE
Usage
At the database or application level, use this value in conjunction with hash_join_overflows and hash_join_small_overflows to determine if a significant percentage of hash joins would benefit from modest increases in the sort heap size.

total_hash_loops - Total Hash Loops monitor element

The total number of times that a single partition of a hash join was larger than the available sort heap space.

Table 206. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 207. 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 208. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Connection event_conn Always collected
Database event_db Always collected
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE
Usage
Values for this element indicate inefficient execution of hash joins. This might indicate that the sort heap size is too small or the sort heap threshold is too small. Use this value in conjunction with the other hash join variables to tune the sort heap size (sortheap) and sort heap threshold (sheapthres) configuration parameters.

total_implicit_compilations - Total implicit compilations monitor element

The total_implicit_compilations monitor element stores the total number of implicit compiles on the database server. Implicit compiles are compilations that are not directly requested by the user. That is, they are not a result of a bind, rebind, prepare or execute immediate request. For example, an implicit compilation may occur when executing a statement that was bound using the VALIDATE RUN option if the statement needs to be compiled at execution time.
Table 210. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_implicit_compile_proc_time - Total implicit compile processing time monitor element

The total_implicit_compile_proc_time monitor element stores the total amount of processing (non-wait) time spent performing implicit compiles on the database server. Implicit compiles are compilations that are not directly requested by the user. That is, they are not a result of a bind, rebind, prepare or execute immediate request. For example, an implicit compilation may occur when executing a statement that was bound using the VALIDATE RUN option if the statement needs to be compiled at execution time. The value is given in milliseconds.
Table 212. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_implicit_compile_time - Total implicit compile time monitor element

The total_implicit_compile_time monitor element stores the total amount of time spent performing implicit compiles on the database server. Implicit compiles are compilations that are not directly requested by the user. That is, they are not a result of a bind, rebind, prepare or execute immediate request. For example, an implicit compilation may occur when executing a statement that was bound using the VALIDATE RUN option if the statement needs to be compiled at execution time. The value is given in milliseconds.
Table 214. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_index_build_proc_time - Total non-wait time spent building indexes due to index creation or re-creation monitor element

The total amount of processing (non-wait) time that is spent building indexes due to index creation or re-creation. This time includes the time that is spent by subagents when the index creation or re-creation operation is parallelized. The value is in milliseconds.

Table 215. 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 - return information about an activity as an XML document ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get connection metrics as an XML document REQUEST METRICS BASE
MON_GET_DATABASE table function - get database information metrics REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics 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 table function - get aggregated routine execution metrics REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated routine execution metrics as an XML document 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_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 workload metrics as an XML document REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 216. 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 REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE

Usage

This element reports the numbers of both explicit and implicit index builds. A CREATE INDEX statement is an example of a way to explicitly build an index. Implicit index builds can include the following builds:
  • Creation of an index to enforce primary key or unique key constraints
  • Creation of an index for a system temporary table that is created internally to help optimize query performance
  • Re-creation of an index, which is considered implicit because it occurs only as necessary and can be caused by any access to a table, depending on the value of the INDEXREC configuration parameter

The value of this element does not include time that is spent building or rebuilding indexes during the execution of utilities, including the LOAD, REORG, and REDISTRIBUTE utilities. That time is counted as part of the time for the corresponding utility's time monitor element when one exists. For example, time for an index rebuild that occurs during a LOAD operation is counted as part of the time for the total_load_time monitor element. The value of the total_index_build_proc_time monitor element also does not include time for indexes that are built during log replay (for HADR or ROLLFORWARD operations, for example) that can occur when index builds are fully logged. For more information, see the logindexbuild configuration parameter.

total_index_build_time - Total time spent building indexes due to index creation or re-creation monitor element

The total time that is spent building indexes due to index creation or re-creation, including the time that is spent by subagents when the index creation or re-creation operation is parallelized. The value is in milliseconds.

Table 217. 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 - return information about an activity as an XML document ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get connection metrics as an XML document REQUEST METRICS BASE
MON_GET_DATABASE table function - get database information metrics REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics 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 table function - get aggregated routine execution metrics REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated routine execution metrics as an XML document 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_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 workload metrics as an XML document REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 218. 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 REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE

Usage

This value of this element includes both explicit and implicit index builds. A CREATE INDEX statement is an example of a way to explicitly build an index. Implicit index builds can include the following builds:
  • Creation of an index to enforce primary key or unique key constraints
  • Creation of an index for a system temporary table that is created internally to help optimize query performance
  • Re-creation of an index, which is considered implicit because it occurs only as necessary and can be caused by any access to a table, depending on the value of the INDEXREC configuration parameter

The value of this element does not include time that is spent building or rebuilding indexes during the execution of utilities, including the LOAD, REORG, and REDISTRIBUTE utilities. That time is counted as part of the time for the corresponding utility's time monitor element when one exists. For example, time for an index rebuild that occurs during a LOAD operation is counted as part of the time for the total_load_time monitor element. The value of the total_index_build_time element also does not include time for indexes that are built during log replay (for HADR or ROLLFORWARD operations, for example) that can occur when index builds are fully logged. For more information, see the logindexbuild configuration parameter.

total_indexes_built - Total number of indexes built monitor element

The total number of indexes that were built.

Table 219. 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 - return information about an activity as an XML document ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get connection metrics as an XML document REQUEST METRICS BASE
MON_GET_DATABASE table function - get database information metrics REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics 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 table function - get aggregated routine execution metrics REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated routine execution metrics as an XML document 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_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 workload metrics as an XML document REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 220. 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 REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of Work uow_metrics REQUEST METRICS BASE

Usage

This element reports the numbers of both explicit and implicit index builds. A CREATE INDEX statement is an example of a way to explicitly build an index. Implicit index builds can include the following builds:
  • Creation of an index to enforce primary key or unique key constraints
  • Creation of an index for a system temporary table that is created internally to help optimize query performance
  • Re-creation of an index, which is considered implicit because it occurs only as necessary and can be caused by any access to a table, depending on the value of the INDEXREC configuration parameter

The value of this element does not include time for indexes that are built during the execution of utilities, including the LOAD, REORG, and REDISTRIBUTE utilities. The value of this element also does not include time for indexes that are built during log replay (for HADR or ROLLFORWARD operations, for example) that can occur when index builds are fully logged. For more information, see the logindexbuild configuration parameter.

total_load_proc_time - Total load processing time monitor element

Total amount of processing (non-wait) time spent performing load processing on the database server. The value is given in milliseconds.

Table 222. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_load_time - Total load time monitor element

The total amount of time spent performing loads on the database server. The value is given in milliseconds.

Table 224. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_loads - Total loads monitor element

The total number of loads performed on the database server.

Table 226. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (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

total_log_available - Total Log Available monitor element

The amount of active log space in the database that is not being used by uncommitted transactions (in bytes).

Table 227. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 228. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic

Usage

Use this element in conjunction with total_log_used to determine whether you may need to adjust the following configuration parameters to avoid running out of log space:
  • logfilsiz
  • logprimary
  • logsecond

If total_log_available goes down to 0, SQL0964C will be returned. You may need to increase the above configuration parameters, or end the oldest transaction by COMMIT, ROLLBACK or FORCE APPLICATION.

If logsecond is set to -1 this element will contain SQLM_LOGSPACE_INFINITE.

Note: While the database system monitor information is given in bytes, the configuration parameters are set in pages, which are each 4K bytes.

total_log_used - Total Log Space Used monitor element

The total amount of active log space currently used (in bytes) in the database.

Table 229. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 230. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Usage
Use this element in conjunction with total_log_available to determine whether you may need to adjust the following configuration parameters to avoid running out of log space:
  • logfilsiz
  • logprimary
  • logsecond
Note: While the database system monitor information is given in bytes, the configuration parameters are set in pages, which are each 4K bytes.

total_nested_invocations - Total nested invocations monitor element

Number of times a routine is invoked at a nesting level > 1.

An example is when a routine or trigger is invoked under the context of another routine or trigger.
Table 232. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ROUTINE table function - get aggregated execution metrics for routines Always collected
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines Always collected

total_olap_funcs - Total OLAP Functions monitor element

The total number of OLAP functions executed.

Table 233. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 234. 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 235. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Connection event_conn Always collected
Database event_db Always collected
Package cache pkgcache_metrics ACTIVITY METRICS BASE
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

Usage

At the database or application level, use this value in conjunction with olap_func_overflows to determine if a significant percentage of OLAP functions would benefit from modest increases in the sort heap size.

total_peas - Total partial early aggregations monitor element

The total number of times that partial early aggregation operations have been executed.

Table 236. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 237. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) 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
Connection event_conn -
Statements event_stmt -
Transactions event_xact -
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

At the database or application level, use this value in conjunction with post_threshold_peas to determine if a significant number of partial early aggregation operations would benefit from an increase in either sort heap size or sort heap threshold. If the ratio of post_threshold_peas to total_peas is high, increasing the sort heap size or the sort heap threshold, or both, may improve database or application performance.

total_peds - Total partial early distincts monitor element

The total number of times that partial early distinct operations have been executed.

Table 238. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 239. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) 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
Connection event_conn -
Statements event_stmt -
Transactions event_xact -
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

At the database or application level, use this value in conjunction with the disabled_peds monitor element and the post_threshold_peds monitor element to determine if a significant number of partial early distinct operations would benefit from an increase in either sort heap size or sort heap threshold. If the ratio of the disabled_peds monitor element and the post_threshold_peds monitor element to the total_peds monitor element is high, increasing the sort heap size or the sort heap threshold, or both, may improve database or application performance.

total_reorg_proc_time - Total reorganization processing time monitor element

The total amount of processing (non-wait) time spent performing reorg operations on the database server. The value is given in milliseconds.

Table 241. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_reorg_time - Total reorganization time monitor element

The total amount of time spent performing reorg operations on the database server. The value is given in milliseconds.

Table 243. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_reorgs - Total reorganizations monitor element

The number of reorg operations issued against the database server.

Table 245. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_rollback_proc_time - Total rollback processing time monitor element

The total amount of processing (non-wait) time spent performing rollback operations on the database server. The value is given in milliseconds.

Table 247. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_rollback_time - Total rollback time monitor element

The total amount of time spent performing rollback operations on the database server. The value is given in milliseconds.

Table 249. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_routine_coord_time - Total routine coordinator time monitor element

Total amount of time the coordinator agent spent executing the routine.

For procedures, compiled SQL functions, compiled triggers, and dynamically prepared compound SQL statements, this element represents the total elapsed time spent in the routine. If request metrics are disabled, this value is zero.
Table 250. Table Function Monitoring Information
Table Function Monitor Element Collection Level
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

total_routine_invocations - Total routine invocations monitor elements

The total number of times a routine was invoked.

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 (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 252. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

total_routine_non_sect_proc_time - Non-section processing time monitor element

The total amount of processing time this statement spent performing non-section execution within routines. This value includes both the time spent executing user-code within routines and time spent performing non-section operations like commit or rollback. Processing time does not include wait time. The value is given in milliseconds.

Table 254. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

total_routine_non_sect_time - Non-section routine execution time monitor elements

The total amount of time this statement spent performing non-section execution within routines. This value includes both the time spent executing user-code within routines and the time spent performing non-section operations like commit or rollback. The value is given in milliseconds.

Table 256. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

total_routine_time - Total routine time monitor element

The total time spent executing routines. The value is given in milliseconds.

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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

If the collection level is set to BASE, then the value of total_routine_time monitor element does not include any time spent executing functions that were defined using the NO SQL clause.

If the collection level is set to EXTENDED, then the value of total_routine_time monitor element includes the time spent in all routines.

total_routine_user_code_proc_time - Total routine user code processing time monitor element

The total amount of processing time spent executing in routines outside of known DB2 times (typically user code in routines). The value is given in milliseconds.

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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

If the collection level is set to BASE, then this monitor element does not include any processing time spent executing functions that were defined using the NO SQL clause. Instead, this time is included in the value of the total_section_proc_time monitor element.

If the collection level is set to EXTENDED, then the value of this monitor element includes the processing time spent executing all routines.

total_routine_user_code_time - Total routine user code time monitor element

The total amount of time spent executing in routines outside of known DB2 times (typically user code in routines). The value is given in milliseconds.

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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 262. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

If the collection level is set to BASE, then the value of this monitor element does not include the time spent executing functions that were defined using the NO SQL clause. Instead, this time is included in the value of the total_section_time monitor element.

If the collection level is set to EXTENDED, then the value of this monitor element includes the time spent executing all routines.

total_rqst_mapped_in - Total request mapped-in monitor element

The total number of requests that were mapped into this service subclass via a remap threshold or a work action set.

Table 263. 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
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
Table 264. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE

total_rqst_mapped_out - Total request mapped-out monitor element

The total number of requests that were mapped out of this service subclass via a remap threshold or a work action set.

Table 265. 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
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
Table 266. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE

total_rqst_time - Total request time monitor element

The total amount of time spent working on requests. This value is reported in milliseconds.

Table 268. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_runstats - Total runtime statistics monitor element

The total number of runstats operations performed on the database server.

Table 270. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_runstats_proc_time - Total runtime statistics processing time monitor element

The total amount of processing (non-wait) time spent performing runstats operations on the database server. The value is given in milliseconds. Any time the runstats utility spends throttled does not count towards the runstats processing time.

Table 272. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_runstats_time - Total runtime statistics time monitor element

The total amount of time spent performing runstats operations on the database server. The value is given in milliseconds.

Table 274. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_sec_cons - Secondary Connections monitor element

The number of connections made by a subagent to the database at the current node.

Table 275. 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 276. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Usage
You can use this element in conjunction with the total_cons, db_conn_time, and the db2start_time monitor elements to calculate the frequency at which applications have connected to the database.

total_section_proc_time - Total section processing time monitor element

The total amount of processing time agents spent performing section execution. Processing time does not include wait time. The value is given in milliseconds.

Table 277. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 278. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

If the collection level is set to BASE, then the value of the total_section_proc_time monitor element includes processing time spent executing functions that were defined using the NO SQL clause.

If the collection level is set to EXTENDED, then the processing time spent executing these functions is not included in the value of the total_section_proc_time monitor element. It is included in the value of the total_routine_user_code_proc_time monitor element.

total_section_sort_proc_time - Total section sort processing time monitor element

Total amount of processing (non-wait) time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application. The value is given in milliseconds.

Table 279. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 280. 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_wlstats (reported in the metrics document) REQUEST METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

At the system level, use this element with the total_section_sorts monitor element to calculate the average sort processing time (does not include waits) during section execution, which can indicate whether or not sorting is an issue as far as performance is concerned.

At the activity level, use this element to identify statements that spend a large amount of time sorting. These statements may benefit from additional tuning to reduce the sort time.

total_section_sort_time - Total section sort time monitor element

Total amount of time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application. The value is given in milliseconds.

Table 281. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 282. 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_wlstats (reported in the metrics document) REQUEST METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

At the system level, use this element with the total_section_sorts monitor element to calculate the average sort time during section execution, which can indicate whether or not sorting is an issue as far as statement performance is concerned.

The total_section_sort_time element includes both wait and processing time. If the value of (total_section_sort_time - total_section_sort_proc_time) is high, sorts are spending a lot of time waiting. For example, if sorts are frequently spilling to disk, the value of the total_section_sort_time monitor element will increase due to I/O waits. This time will not be included in the total_section_sort_proc_time monitor element value, which only counts the time actively processing a sort. In this case, you may consider tuning sort memory to improve performance.

At the activity level, use this element to identify statements that spend a large amount of time sorting. These statements may benefit from additional tuning to reduce the sort time.

total_section_sorts - Total section sorts monitor element

Total number of sorts performed during section execution, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application.

Table 283. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 284. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

Use this element with thetotal_section_sort_time monitor element to calculate the average amount of time spent performing sorts during section execution.

At the activity and package cache levels, use this element to identify statements which are performing large numbers of sorts. These statements may benefit from additional tuning to reduce the number of sorts. You can also use the EXPLAIN statement to identify the number of sorts a statement performs.

total_section_time - Total section time monitor element

The total time agents spent performing section execution. The value is given in milliseconds.

Table 285. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 286. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Unit of work

Reported in the system_metrics document.

REQUEST METRICS BASE

Usage

If the collection level is set to BASE, then the value of total_section_time monitor element includes time spent executing functions that were defined using the NO SQL clause.

If the collection level is set to EXTENDED, then the time spent executing these functions is not included in the value of the total_section_time monitor element. It is included in the value of the total_routine_user_code_time monitor element instead.

total_sort_time - Total sort time monitor element

The total elapsed time for all sorts that have been executed. This value is reported in milliseconds.

Table 287. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Sort
Application appl Sort
Application stmt Sort
Dynamic SQL dynsql Sort
For snapshot monitoring, this counter can be reset.
Table 288. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Statements event_stmt Always collected
Activities event_activity Statement, Sort

Usage

At a database or application level, use this element with total_sorts to calculate the average sort time, which can indicate whether or not sorting is an issue as far as performance is concerned.

At a statement level, use this element to identify statements that spend a lot of time sorting. These statements may benefit from additional tuning to reduce the sort time.

This count also includes sort time of temporary tables created during related operations. It provides information for one statement, one application, or all applications accessing one database.

When using monitor elements providing elapsed times, you should consider:
  1. Elapsed times are affected by system load, so the more processes you have running, the higher this elapsed time value.
  2. To calculate this monitor element at a database level, the database system monitor sums the application-level times. This can result in double counting elapsed times at a database level, since more than one application process can be running at the same time.
    To provide meaningful data from the database level, you should normalize the data to a lower level. For example:
     
     total_sort_time / total_sorts

    provides information about the average elapsed time for each sort.

total_sorts - Total sorts monitor element

The total number of sorts that have been executed.

Table 289. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_ROUTINE_EXEC_LIST table function - get list of statements executed by routine ACTIVITY 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_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 290. 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 291. 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
Connection event_conn Always collected
Statements event_stmt Always collected
Activities event_activity Statement, Sort
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

At a database or application level, use this value with sort_overflows to calculate the percentage of sorts that need more heap space. You can also use it with total_sort_time to calculate the average sort time.

If the number of sort overflows is small with respect to the total sorts, then increasing the sort heap size may have little impact on performance, unless this buffer size is increased substantially.

At a statement level, use this element to identify statements which are performing large numbers of sorts. These statements may benefit from additional tuning to reduce the number of sorts. You can also use the SQL EXPLAIN statement to identify the number of sorts a statement performs.

total_stats_fabrication_proc_time - Total statistics fabrication processing time monitor element

The total non-wait time spent on statistics fabrications by real-time statistics gathering, in milliseconds. Statistics fabrication is the statistics collection activity needed to generate statistics during query compilation.

Table 293. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_stats_fabrication_time - Total statistics fabrication time monitor element

The total time spent on statistics fabrications by real-time statistics gathering, in milliseconds. Statistics fabrication is the statistics collection activity needed to generate statistics during query compilation.

Table 294. 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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries (reported in DETAILS XML document) 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_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 295. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity 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 uow_metrics REQUEST METRICS BASE
Package cache pkgcache ACTIVITY METRICS BASE

total_stats_fabrications - Total statistics fabrications monitor elements

The total number of statistics fabrications performed by real-time statistics gathering. Statistics fabrication is the statistics collection activity needed to generate statistics during query compilation.

Table 296. 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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries (reported in DETAILS XML document) 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_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 297. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

total_sync_runstats_time - Total synchronous RUNSTATS time monitor elements

The total time spent on synchronous RUNSTATS activities triggered by real-time statistics gathering, in milliseconds. The synchronous RUNSTATS activities occur during query compilation.

Table 298. 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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries (reported in DETAILS XML document) 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_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 299. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity Always collected
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
Package cache Reported in the activity_metrics document. Always collected

total_sync_runstats_proc_time - Total synchronous RUNSTATS processing time monitor element

The non-wait time spent on synchronous RUNSTATS activities triggered by real-time statistics gathering, in milliseconds. The synchronous RUNSTATS activities occur during query compilation.

Table 301. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_sync_runstats - Total synchronous RUNSTATS activities monitor element

The total number of synchronous RUNSTATS activities triggered by real-time statistics gathering. The synchronous RUNSTATS activities occur during query compilation.

Table 302. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries (reported in DETAILS XML document) 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_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 303. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

total_sys_cpu_time - Total system CPU time for a statement monitor element

The total system CPU time for an SQL statement.

Table 304. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Dynamic SQL dynsql Statement
For snapshot monitoring, this counter can be reset.

Usage

Use this element with Elapsed Statement Execution Time and Total User CPU for a Statement to evaluate which statements are the most expensive.

This element is composed of two subelements that report time spent as seconds and microseconds (one millionth of a second). The names of the subelements can be derived by adding "_s" and "_ms" to the name of this monitor element. To retrieve the total time spent for this monitor element, the values of the two subelements must be added together. For example, if the "_s" subelement value is 3 and the "_ms" subelement value is 20, then the total time spent for the monitor element is 3.00002 seconds.

total_usr_cpu_time - Total user CPU time for a statement monitor element

The total user CPU time for an SQL statement.

Table 306. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Dynamic SQL dynsql Statement
For snapshot monitoring, this counter can be reset.

Usage

Use this element with Elapsed Statement Execution Time and to evaluate the longest running statements.

This element is composed of two subelements that report time spent as seconds and microseconds (one millionth of a second). The names of the subelements can be derived by adding "_s" and "_ms" to the name of this monitor element. To retrieve the total time spent for this monitor element, the values of the two subelements must be added together. For example, if the "_s" subelement value is 3 and the "_ms" subelement value is 20, then the total time spent for the monitor element is 3.00002 seconds.

total_wait_time - Total wait time monitor element

The total time spent waiting within the DB2 database server. The value is given in milliseconds.

Table 308. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
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

Usage

To understand the percentage of time the database server spends actively working on requests, use the following ratio:
(total_rqst_time - total_wait_time) / total_rqst_time
The value of the client_idle_wait_time monitor element is not included in the value of the total_wait_time monitor element. The total_wait_time element represents only time spent waiting while the database server is processing requests.

tpmon_acc_str - TP monitor client accounting string monitor element

The data passed to the target database for logging and diagnostic purposes, if the sqleseti API was issued in this connection. The current value of the CLIENT_ACCTNG special register for this connection, unit of work, or activity.

This monitor element is synonymous to the client_acctng monitor element. The client_acctng monitor element is used for monitoring table functions and event monitors that write to unformatted tables, which were introduced in DB2 Version 9.7. The tpmon_acc_str monitor element is used for snapshot monitors and event monitors that write to tables, files, and pipes.
Table 309. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
DCS Application dcs_appl Basic
Table 310. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity -
Deadlock event_dlconn -
Transaction event_xact -

Usage

Use this element for problem determination and accounting purposes.

tpmon_client_app - TP monitor client application name monitor element

Identifies the server transaction program performing the transaction, if the sqleseti API was issued in this connection. The current value of the CLIENT_APPLNAME special register for this connection, unit of work, or activity.

This monitor element is synonymous to the client_applname monitor element. The client_applname monitor element is used for monitoring table functions and event monitors that write to unformatted tables, which were introduced in DB2 Version 9.7. The tpmon_client_app monitor element is used for snapshot monitors and event monitors that write to tables, files, and pipes.
Table 311. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
DCS Application dcs_appl Basic
Table 312. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity -
Deadlock event_dlconn -
Transaction event_xact -

Usage

Use this element for problem determination and accounting purposes.

tpmon_client_userid - TP monitor client user ID monitor element

The client user ID generated by a transaction manager and provided to the server, if the sqleseti API is used. The current value of the CLIENT_USERID special register for this connection, unit of work, or activity.

This monitor element is synonymous to the client_userid monitor element. The client_userid monitor element is used for monitoring table functions and event monitors that write to unformatted tables, which were introduced in DB2 Version 9.7. The tpmon_client_userid monitor element is used for snapshot monitors and event monitors that write to tables, files, and pipes.
Table 313. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
DCS Application dcs_appl Basic
Table 314. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity -
Deadlock event_dlconn -
Transaction event_xact -

Usage

Use this element in application server or Transaction Processing monitor environments to identify the end-user for whom the transaction is being executed.

tpmon_client_wkstn - TP monitor client workstation name monitor element

Identifies the client's system or workstation (for example CICS® EITERMID), if the sqleseti API was issued in this connection. The current value of the CLIENT_WRKSTNNAME special register for this connection, unit of work, or activity.

This monitor element is synonymous to the client_wrkstnname monitor element. The client_wrkstnname monitor element is used for monitoring table functions and event monitors that write to unformatted tables, which were introduced in DB2 Version 9.7. The tpmon_client_wkstn monitor element is used for snapshot monitors and event monitors that write to tables, files, and pipes.
Table 315. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
DCS Application dcs_appl Basic
Table 316. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity -
Deadlock event_dlconn -
Transaction event_xact -

Usage

Use this element to identify the user's machine by node ID, terminal ID, or similar identifiers.

tq_cur_send_spills - Current number of table queue buffers overflowed monitor element

The current number of table queue buffers residing in a temporary table.

Table 317. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Usage
An agent writing to a table queue may be sending rows to several readers. The writing agent will overflow buffers to a temporary table when the agent that it is currently sending rows to is not accepting rows and another agent requires rows in order to proceed. Overflowing to temporary table allows both the writer and the other readers to continue processing.

Rows that have been overflowed will be sent to the reading agent when it is ready to accept more rows.

If this number is high, and queries fail with sqlcode -968, and there are messages in db2diad.log indicating that you ran out of temporary space in the TEMP table space, then table queue overflows may be the cause. This could indicate a problem on another node (such as locking). You would investigate by taking snapshots on all the partitions for this query.

There are also cases, perhaps because of the way data is partitioned, where many buffers need to be overflowed for the query. In these cases you will need to add more disk to the temporary table space.

tq_max_send_spills - Maximum number of table queue buffers overflows monitor element

Maximum number of table queue buffers overflowed to a temporary table.

Element identifier
tq_max_send_spills
Element type
watermark
Table 319. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Table 320. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_subsection Always collected
Usage
Indicates the maximum number of table queue buffers that have been written to a temporary table.

tq_node_waited_for - Waited for node on a table queue monitor element

If the subsection status ss_status is waiting to receive or waiting to send and tq_wait_for_any is FALSE, then this is the number of the node that this agent is waiting for.

Element identifier
tq_node_waited_for
Element type
information
Table 321. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Usage
This can be used for troubleshooting. You may want to take an application snapshot on the node that the subsection is waiting for. For example, the application could be in a lock wait on that node.

tq_rows_read - Number of Rows Read from table queues monitor element

Total number of rows read from table queues.

Element identifier
tq_rows_read
Element type
counter
Table 322. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Table 323. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_subsection Always collected
Usage
If monitoring does not indicate that this number is increasing, then processing progress is not taking place.

If there is significant differences in this number between nodes, then some nodes may be over utilized while others are being under utilized.

If this number is large, then there is a lot of data being shipped between nodes, suggest that optimization might improve the access plan.

tq_rows_written - Number of rows written to table queues monitor element

Total number of rows written to table queues.

Element identifier
tq_rows_written
Element type
counter
Table 324. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Table 325. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_subsection Always collected
Usage
If monitoring does not indicate that this number is increasing, then processing progress is not taking place.

If there is significant differences in this number between nodes, then some nodes may be over utilized while others are being under utilized.

If this number is large, then there is a lot of data being shipped between nodes, suggest that optimization might improve the access plan.

tq_sort_heap_rejections - Table queue sort heap rejections monitor element

The number of times that table queues requested for more sort heap memory and were rejected due to sort heap threshold being exceeded.

Table 326. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 327. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) 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
Connection event_conn -
Statements event_stmt -
Transactions event_xact -
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use this element in conjunction with the tq_sort_heap_requests monitor element to determine if table queues are getting sufficient sort heap memory most of the time. If the ratio of the tq_sort_heap_rejections monitor element to the tq_sort_heap_requests monitor element is high, database performance may be sub-optimal. Consider increasing the sort heap size.

tq_sort_heap_requests - Table queue sort heap requests monitor element

The number of times that table queues requested for more sort heap memory to store data.

Table 328. 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_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 329. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) 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
Connection event_conn -
Statements event_stmt -
Transactions event_xact -
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use this element in conjunction with the tq_sort_heap_rejections monitor element to determine if table queues are getting sufficient sort heap memory most of the time. If the ratio of the tq_sort_heap_rejections monitor element to the tq_sort_heap_requests monitor element is high, database performance may be sub-optimal. Consider increasing the sort heap size.

tq_tot_send_spills - Total number of table queue buffers overflowed monitor element

Total number of table queue buffers overflowed to a temporary table.

Table 330. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS 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_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 331. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Table 332. 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
Statements event_subsection Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Indicates the total number of table queue buffers that have been written to a temporary table. See the tq_cur_send_spills monitor element for more information.

tq_wait_for_any - Waiting for any node to send on a table queue monitor element

This flag is used to indicate that the subsection is blocked because it is waiting to receive rows from any node.

Element identifier
tq_wait_for_any
Element type
information
Table 333. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Usage
If ss_status indicates waiting to receive data on a table queue and this flag is TRUE, then the subsection is waiting to receive rows from any node. This generally indicates that the SQL statement has not processed to the point it can pass data to the waiting agent. For example, the writing agent may be performing a sort and will not write rows until the sort has completed. From the db2expln output, determine the subsection number associated with the tablequeue that the agent is waiting to receive rows from. You can then examine the status of that subsection by taking a snapshot on each node where it is executing.

ts_name - Table space being rolled forward monitor element

The name of the table space currently rolled forward.

Element identifier
ts_name
Element type
information
Table 334. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space rollforward Basic
Usage
If a rollforward is in progress, this element identifies the table spaces involved.

txn_completion_status - Transaction completion status monitor element

This element indicates the status of the transaction.

Table 335. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History TXNCOMPLETION Always collected

Usage

For the change history event monitor, the status of the transaction is one of:
C
Commit
R
Rollback
S
Rollback to savepoint