H

hadr_connect_status - HADR Connection Status monitor element

The current high availability disaster recovery (HADR) connection status of the database.
Table 1. 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
Table 2. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the HADR connection status of a database.

For the MON_GET_HADR interface, this element is a character string, and it can be one of the following values:
  • CONNECTED
  • CONGESTED
  • DISCONNECTED
For snapshot monitor interface, the data type of this element is integer, and its value is one of the following constants:
SQLM_HADR_CONN_CONNECTED
The database is connected to its partner node.
SQLM_HADR_CONN_DISCONNECTED
The database is not connected to its partner node.
SQLM_HADR_CONN_CONGESTED
The database is connected to its partner node, but the connection is congested. A connection is congested when the TCP/IP socket connection between the primary-standby pair is still alive, but one end cannot send to the other end. For example, the receiving end is not receiving from the socket connection, resulting in a full TCP/IP send space. The reasons for network connection being congested include the following:
  • The network is being shared by too many resources or the network is not fast enough for the transaction volume of the primary HADR node.
  • The server on which the standby HADR node resides is not powerful enough to retrieve information from the communication subsystem at the necessary rate.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_connect_status_time - HADR connect status time monitor element

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

This element returns the time the HADR database became in the state indicated by the hadr_connect_status element. Shows one of the following values: connection start time, congestion start time, or disconnection time, depending on hadr_connect_status.

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

hadr_connect_time - HADR Connection Time monitor element

This monitor element can return one of the following values: high availability disaster recovery (HADR) connection time, HADR congestion time, or HADR disconnection time.

Table 4. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine when the current HADR connection status began.

If the database is in HADR primary or standby role, the meaning of this element depends on the value of the hadr_connect_status element:
  • If the value of the hadr_connect_status element is SQLM_HADR_CONN_CONNECTED, then this element shows connection time.
  • If the value of the hadr_connect_status element is SQLM_HADR_CONN_CONGESTED, then this element shows the time when congestion began.
  • If the value of the hadr_connect_status element is SQLM_HADR_CONN_DISCONNECTED, then this element shows disconnection time.
If there has been no connection since the HADR engine dispatchable unit (EDU) was started, connection status is reported as Disconnected and HADR EDU startup time is used for the disconnection time. Since HADR connect and disconnect events are relatively infrequent, the time is collected and reported even if the DFT_MON_TIMESTAMP switch is off.
Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_flags - HADR flags monitor element

Flags for HADR information.

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

Usage

hadr_flags is a space delimited string made from the following flags:
ASSISTED_REMOTE_CATCHUP
The stream is in assisted remote catchup state.
ASSISTED_MEMBER_ACTIVE
During assisted remote catchup, the member on primary that is being assisted is active. This is an abnormal condition because an active member is expected to connect to the standby database directly.
STANDBY_LOG_RETRIEVAL
The standby database is interacting with the log archive device to retrieve log files.
STANDBY_RECV_BLOCKED
The standby database temporarily cannot receive logs. The following conditions are possible causes of this flag:
  • When log spooling is disabled, the buffer that receives logs is full (standby_recv_buf_percent is 100%).
  • When log spooling is enabled, spooling has reached the spool limit (standby_spool_percent is 100%).
  • The Standby log device is full (standby_log_device_full flag is set). This condition can happen when spooling is enabled or disabled.
In all cases, progress made during replay will release space and let log receiving resume.
STANDBY_LOG_DEVICE_FULL
The standby log device is full. This condition blocks receiving of logs until space is released as replay proceeds.
STANDBY_REPLAY_NOT_ON_PREFERRED
The current replay member on the standby is not the preferred replay member.
STANDBY_KEY_ROTATION_ERROR
The standby database encountered a master key rotation error. No logs are received until the error is corrected. The system shuts down if the error is not corrected within the timeout period (30 minutes).
STANDBY_TABLESPACE_ERROR
The standby database has a table space in an invalid error state and can no longer replay transactions affecting it. Replay of transactions on other valid table spaces will continue. This can happen if there is a container or disk problem for a table space. This problem should be investigated and addressed immediately. If this state occurs at the time of database upgrade and the problem is not resolved before the upgrade then the table space(s) in error will not be recoverable once the upgrade to the new DB2® version is complete.

hadr_heartbeat - HADR Heartbeat monitor element

Number of consecutively missed heartbeats on the high availability disaster recovery (HADR) connection. This number is reset to zero when the database receives a heartbeat again. If the database is in the HADR primary or standby role, this element indicates the health of the HADR connection.
Table 6. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic
For snapshot monitoring, this counter cannot be reset.

Usage

Use this element to determine the health of the HADR connection.

A heartbeat is a message sent from the other HADR database at regular intervals. If the value for this element is zero, no heartbeats have been missed and the connection is healthy. The higher the value, the worse the condition of the connection.

In disconnected mode, heartbeat missed is always shown as 0, because it is not applicable.

The heartbeat interval is derived from configuration parameters such as hadr_timeout and hadr_peer_window, with a maximal setting of 30 seconds.

The data type of this element is integer.

Ignore this element if the HADR role of the database is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_local_host - HADR Local Host monitor element

The local high availability disaster recovery (HADR) host name. The value is displayed as a host name string or an IP address string such as "1.2.3.4".

Table 7. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the effective HADR local host name. HADR database configuration parameters are static. Changes to a parameter are not effective until the database is stopped and restarted. This monitor element reports the value that the HADR system is actually using rather than the value in the database configuration file.

Changes to this element take effect on database activation or, if the database is already online, after HADR has been stopped and restarted on the primary.

Note: Any name used must resolve to one IP address. A name that resolves to more than one address will cause an error when trying to start HADR.
Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_local_service - HADR Local Service monitor element

The local HADR TCP service. This value is displayed as a service name string or a port number string.
Table 8. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the effective HADR local service name.

Changes to this element take effect on database activation or, if the database is already online, after HADR has been stopped and restarted on the primary.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_log_gap - HADR Log Gap monitor element

This element shows the recent average of the gap between the PRIMARY_LOG_POS value and STANDBY_LOG_POS value. The gap is measured in number of bytes.
Table 9. 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
Table 10. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the gap between the primary and standby HADR database logs.
Note: If some of the log files have been truncated, it can appear that you have an inflated log gap due to this truncation.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_peer_window - HADR peer window monitor element

The value of the HADR_PEER_WINDOW database configuration parameter.

Table 11. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the value of the HADR_PEER_WINDOW database configuration parameter.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

hadr_peer_window_end - HADR peer window end monitor element

The point in time until which a high availability disaster recovery (HADR) primary database promises to stay in peer or disconnected peer state, as long as the primary database is active.

Table 12. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the point in time until which the primary promises to stay in peer or disconnected peer state.

The value reported by the primary database might be different from the value reported by the standby database. This occurs because the primary database updates the value when it sends a heartbeat message, but the new value is shown on the standby database only after the message is received and processed on the standby database.

If a database moves out of peer or disconnected peer state, the value of this monitor element is not reset. The last known value is kept and returned. If a database never reached peer state, a value of zero will be returned.

The peer window end time is set by the primary database and then sent to the standby database. For this reason, the value of the peer window end is based on the clock of the primary database. When you compare the peer window end time with the primary database down time, you might need to add an offset to convert the timestamp to the primary database clock, if the two clocks are not well synchronized.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

hadr_primary_log_file - HADR Primary Log File monitor element

The name of the current log file on the primary HADR database.

Table 13. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the current log file on the primary HADR database.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_primary_log_lsn - HADR Primary Log LSN monitor element

The current log position of the primary HADR database. Log sequence number (LSN) is a byte offset in the database's log stream.

Table 14. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the current log position on the primary HADR database.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_primary_log_page - HADR Primary Log Page monitor element

The page number in the current log file indicating the current log position on the primary HADR database. The page number is relative to the log file. For example, page zero is the beginning of the file.

Table 15. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the current log page on the primary HADR database.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_remote_host - HADR Remote Host monitor element

The remote high availability disaster recovery (HADR) host name. The value is displayed as a host name string or an IP address string such as "1.2.3.4".

Table 16. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the effective HADR remote host name.

Changes to this element take effect on database activation or, if the database is already online, after HADR has been stopped and restarted on the primary.

Note: Any name used must resolve to one IP address. A name that resolves to more than one address will cause an error when trying to start HADR.
Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_remote_instance - HADR Remote Instance monitor element

The remote HADR instance name.

Table 17. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the effective HADR remote instance name.

Changes to this element take effect on database activation or, if the database is already online, after HADR has been stopped and restarted on the primary.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_remote_service - HADR Remote Service monitor element

The remote HADR TCP service. This value is displayed as a service name string or a port number string.
Table 18. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the effective HADR remote service name.

Changes to this element take effect on database activation or, if the database is already online, after HADR has been stopped and restarted on the primary.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_role - HADR Role monitor element

The current high availability disaster recovery (HADR) role of the database.
Table 19. 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
Table 20. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the HADR role of a database.

For the MON_GET_HADR interface, this element is a character string, and it can be one of the following values:
  • PRIMARY
  • STANDARD
  • STANDBY
For snapshot monitor interface, the data type of this element is integer, and its value is one of the following constants:
SQLM_HADR_ROLE_STANDARD
The database is not an HADR database.
SQLM_HADR_ROLE_PRIMARY
The database is the primary HADR database.
SQLM_HADR_ROLE_STANDBY
The database is the standby HADR database.

hadr_standby_log_file - HADR Standby Log File monitor element

The name of the current log file on the standby HADR database.

Table 21. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the current log file on the standby HADR database.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_standby_log_lsn - HADR Standby Log LSN monitor element

The current log position of the standby HADR database. Log sequence number (LSN) is a byte offset in the database's log stream.

Table 22. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the current log position on the standby HADR database.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_standby_log_page - HADR Standby Log Page monitor element

The page number in the current log file indicating the current log position on the standby HADR database. The page number is relative to the log file. For example, page zero is the beginning of the file.

Table 23. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the current log page on the standby HADR database.

Important: This monitor element has been deprecated in Version 10.1 and might be removed in a future release. For more information, see New fields improve HADR monitoring and Some monitoring interfaces for HADR have been deprecated.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_state - HADR State monitor element

The current high availability disaster recovery (HADR) state of the database.
Table 24. 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
Table 25. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

For the MON_GET_HADR interface, this element is a character string, and it can be one of the following values:
  • DISCONNECTED
  • LOCAL_CATCHUP
  • REMOTE_CATCHUP_PENDING
  • REMOTE_CATCHUP
  • PEER
  • DISCONNECTED_PEER
For snapshot monitor interface, the data type of this element is integer, and its value is one of the following constants:
SQLM_HADR_STATE_DISCONNECTED
The database is not connected to its partner database.
SQLM_HADR_STATE_LOC_CATCHUP
The database is doing local catchup.
SQLM_HADR_STATE_REM_CATCH_PEND
The database is waiting to connect to its partner to do remote catchup.
SQLM_HADR_STATE_REM_CATCHUP
The database is doing remote catchup.
SQLM_HADR_STATE_PEER
The primary and standby databases are connected and are in peer state.
SQLM_HADR_STATE_DISCONN_PEER
The primary and standby databases are in disconnected peer state.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_syncmode - HADR Synchronization Mode monitor element

The current high availability disaster recovery (HADR) synchronization mode of the database.
Table 26. 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
Table 27. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the HADR synchronization mode of a database.

For the MON_GET_HADR interface, this element is a character string, and it can be one of the following values:
  • ASYNC
  • NEARSYNC
  • SYNC
  • SUPERASYNC
For snapshot monitor interface, the data type of this element is integer, and its value is one of the following constants:
SQLM_HADR_SYNCMODE_SYNC
SYNC mode.
SQLM_HADR_SYNCMODE_NEARSYNC
NEARSYNC mode.
SQLM_HADR_SYNCMODE_ASYNC
ASYNC mode.
SQLM_HADR_SYNCMODE_SUPERASYNC
SUPERASYNC mode.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hadr_timeout - HADR Timeout monitor element

The number of seconds without any communication from its partner after which an HADR database server will consider that the connection between them has failed.
Table 28. 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
Table 29. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database hadr Basic

Usage

Use this element to determine the effective HADR timeout value.

Changes to this element take effect on database activation or, if the database is already online, after HADR has been stopped and restarted on the primary.

This element should be ignored if the database's HADR role is standard. Use the hadr_role monitor element to determine the HADR role of the database.

hash_grpby_overflows - Hash GROUP BY overflows monitor element

The number of times that GROUP BY operations using hashing as their grouping method exceeded the available sort heap memory.

Table 30. 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 31. 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 total_hash_grpbys 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.

hash_join_overflows - Hash Join Overflows monitor element

The number of times that hash join data exceeded the available sort heap space.

Table 32. 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 33. 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 34. 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 level, if the value of hash_join_small_overflows is greater than 10% of this hash_join_overflows, then you should consider increasing the sort heap size. Values at the application level can be used to evaluate hash join performance for individual applications.

hash_join_small_overflows - Hash Join Small Overflows monitor element

The number of times that hash join data exceeded the available sort heap space by less than 10%.

Table 35. 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 36. 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 37. 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
If this value and hash_join_overflows are high, then you should consider increasing the sort heap threshold. If this value is greater than 10% of hash_join_overflows, then you should consider increasing the sort heap size.

heartbeat_expected - Heartbeats expected monitor element

The number of heartbeat messages expected on this log stream.

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

Usage

These messages accumulate when a database is started on the local member. With the heartbeat_missed value, you can determine the health of a network for a given time duration.

heartbeat_missed - Heartbeats missed monitor element

The number of heartbeat messages not received on time on this log stream.

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

Usage

These messages start accumulating when a database is started on the local member. This number should be viewed relative to the heartbeat_expected value. For example, 100 missed heartbeats when heartbeat_expected is 1000 is a 10% miss rate. This miss rate indicates a network problem with. However, 100 missed heartbeats when heartbeat_expected is 10000 is a 1% miss rate is unlikely to be a network issue. Take the heartbeat_interval value into account when assessing the heartbeat_expected value. A short heartbeat_interval value can cause the heartbeat_missed value to appear high even though it is safe.

histogram_type - Histogram type monitor element

The type of the histogram, in string format.

There are seven histogram types.
CoordActQueueTime
A histogram of the time (in milliseconds) non-nested activities spend queued (for example, in a threshold queue), measured on the coordinator member.
CoordActExecTime
A histogram of the time (in milliseconds) non-nested activities spend executing at the coordinator member. Execution time does not include time spent initializing or queued. For cursors, execution time includes only the time spent on open, fetch and close requests. When an activity is remapped between service subclasses, the execution time histogram is updated only for the service subclass in which the activity completes execution.
CoordActLifetime
A histogram of the elapsed time (in milliseconds) from when a non-nested activity is identified by the database manager until the activity completes execution, as measured on the coordinator member. When you remap activities between service subclasses, the lifetime histogram is updated only for the service subclass in which the activity completes execution.
CoordActInterArrivalTime
A histogram of the time interval (in milliseconds) between the arrival of non-nested coordinator activities. The inter-arrival time mean is calculated for service subclasses through which activities enter the system. When you remap activities between service subclasses, the inter-arrival time histogram of the service subclass you remap an activity to is unaffected.
CoordActEstCost
A histogram of the estimated cost (in timerons) of non-nested DML activities. The estimated cost of an activity is counted only toward the service subclass in which the activity enters the system.
ReqExecTime
A histogram of request execution times (in milliseconds), which includes requests on the coordinator member, and any subrequests on both coordinator and non-coordinator members (like RPC requests or SMP subagent requests). Requests included may or may not be associated with an activity: Both PREPARE and OPEN requests are included in this histogram, for example, but while OPEN requests are always associated with a cursor activity, PREPARE requests are not part of any activity.The execution time histogram of a service subclass involved in remapping counts the portion of the execution time spent by the partial request in the service subclass.
UowLifetime
A histogram of the elapsed time (in milliseconds) from the time that a unit of work is identified by the database manager until the time that the unit of work completes execution (committed or rolled back).
Table 41. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_histogrambin Always collected

Usage

Use this element to identify the type of histogram. Several histograms can belong to the same statistics record, but only one of each type.

hld_member - Database member for application holding lock

Database member where the lock is being held by the holding application.

Table 43. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting Always collected

Usage

If the lock is being held on a remote member, the value of hld_member is -2. To determine which member the lock is being held at, use the MON_GET_LOCKS table function and specify the lock_name as a search argument.

host_ccsid - Host Coded Character Set ID monitor element

This is the coded character set identifier (CCSID) of the host database.

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

host_db_name - Host Database Name monitor element

The real name of the host database for which information is being collected or to which the application is connected. This is the name that was given to the database when it was created.

Table 45. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Database dcs_dbase Basic
DCS Application dcs_appl_info Basic
Usage
Use this element for problem determination on DCS applications.

host_name - Host name monitor element

Name of the host on which the cluster caching facility process resides.

host_prdid - Host Product/Version ID monitor element

The product and version that is running on the server.

Table 47. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Application dcs_appl_info Basic
Usage
Used to identify the product and code version of the DRDA host database product. It is in the form PPPVVRRM, where:
  • PPP identifies the host DRDA product
    • ARI for DB2 Server for VSE & VM
    • DSN for DB2 for z/OS®
    • QSQ for DB2 for i
    • SQL for other DB2 products.
  • VV identifies a 2-digit version number (with high-order 0 in the case of a 1-digit version)
  • RR identifies a 2-digit release number (with high-order 0 in the case of a 1-digit release)
  • M identifies a 1-character modification level (0-9 or A-Z)

host_response_time - Host Response Time monitor element

At the DCS statement level, this is the elapsed time between the time that the statement was sent from the DB2 Connect gateway to the host for processing and the time when the result was received from the host. At DCS database and DCS application levels, it is the sum of the elapsed times for all the statements that were executed for a particular application or database. At the data transmission level, this is the sum of host response times for all the statements that used this many data transmissions.
Table 48. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Database
  • dcs_dbase
  • stmt_transmissions
Statement
DCS Application
  • dcs_appl
  • stmt_transmissions
Statement, Timestamp
DCS Statement dcs_stmt Statement, Timestamp
For snapshot monitoring at the statement level, this counter cannot be reset. This counter can be reset at other levels.

Usage

Use this element with Outbound Number of Bytes Sent and Outbound Number of Bytes Received to calculate the outbound response time (transfer rate):
   (outbound bytes sent + outbound bytes received) / host response time

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.

hostname - Host name monitor element