L

last_active_log - Last Active Log File Number monitor element

The file number of the last active log file.

Element identifier
last_active_log
Element type
information
Table 1. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 2. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database detail_log Basic
Table 3. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
Use this element in conjunction with the first_active_log and current_active_log elements to determine the range of active log files. Knowing the range of active log files helps you determine the disk space required for log files.

You can also use this element to determine which log files have data to help you identify log files needed for split mirror support.

last_backup - Last Backup Timestamp monitor element

The date and time that the latest database backup was completed.

Element identifier
last_backup
Element type
timestamp
Table 4. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Timestamp
Usage
You may use this element to help you identify a database that has not been backed up recently, or to identify which database backup file is the most recent. If the database has never been backed up, this timestamp is initialized to zero.

last_executable_id - Last executable identifier monitor element

The executable id for the statement most recently completed by the application.

Table 5. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics Always collected
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) Always collected
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics Always collected
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) Always collected

last_metrics_update - Metrics last update timestamp monitor element

Timestamp reflecting the last time metrics were updated for this cache entry.

Table 7. Table Function Monitoring Information
Table Function Monitor Element Collection Level
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
Table 8. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Package cache - COLLECT BASE DATA

last_overflow_time - Time of Last Event Overflow monitor element

The date and time of the last overflow recorded this overflow record.

Table 9. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Overflow Record event_overflow -
Usage
Use this element with first_overflow_time to calculate the elapsed time for which the overflow record was generated.

last_request_type - Last request type monitor element

The type of the last request completed by the application.

Table 11. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics Always collected
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) Always collected
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics Always collected
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) Always collected

Usage

This monitor element is only reported on the coordinator member for the application.

The following values are possible.
  • CLOSE
  • COMMIT
  • COMPILE
  • DESCRIBE
  • EXCSQLSET
  • EXECIMMD
  • EXECUTE
  • FETCH
  • INTERNAL number, where number is the value of the internal constant
  • OPEN
  • PREPARE
  • REBIND
  • REDISTRIBUTE
  • REORG
  • ROLLBACK
  • RUNSTATS

last_reset - Last Reset Timestamp monitor element

Indicates the date and time that the monitor counters were reset for the application issuing the GET SNAPSHOT.

Element identifier
last_reset
Element type
timestamp
Table 12. Table Function Monitoring Information
Table Function Monitor Element Collection Level
WLM_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
WLM_GET_SERVICE_SUPERCLASS_STATS table function - Return statistics of service superclasses Always collected
WLM_GET_WORK_ACTION_SET_STATS table function - Return work action set statistics Always collected
WLM_GET_WORKLOAD_STATS table function - Return workload statistics Always collected
Table 13. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Timestamp
Database dbase Timestamp
Application appl Timestamp
Table Space tablespace_list Buffer Pool, Timestamp
Table table_list Timestamp
DCS Database dcs_dbase Timestamp
DCS Application dcs_appl Timestamp
Usage
You can use this element to help you determine the scope of information returned by the database system monitor.

If the counters have never been reset, this element will be zero.

The database manager counters will only be reset if you reset all active databases.

last_wlm_reset - Time of last reset monitor element

This element, in the form of a local timestamp, shows the time at which the last statistics event record of this type was created.

Table 15. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_qstats -
Statistics event_scmetrics Always collected
Statistics event_scstats -
Statistics event_wcstats -
Statistics event_wlmetrics Always collected
Statistics event_wlstats -

Usage

Use the wlm_last_reset and statistics_timestamp monitor elements to determine a period of time over which the statistics in an event monitor statistics record were collected. The collection interval begins at the wlm_last_reset time and ends at statistics_timestamp.

lib_id - Library identifier monitor element

Internal unique identifier for triggers and trigger subroutines.

This element returns NULL when it is not applicable for the monitored object.
Table 16. 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
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine Always collected
MON_GET_SECTION_ROUTINE table function - get list of routines for input section Always collected

Usage

Use this element to relate a trigger to its subroutines. Any subroutines declared in a trigger will have the same lib_id values as the trigger.

lob_object_pages - LOB Object Pages monitor element

The number of disk pages consumed by LOB data.

Table 17. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
Table 18. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Tables event_table Always collected
Usage
This element provides a mechanism for viewing the actual amount of space consumed by LOB data in a particular table. This element can be used in conjunction with a table event monitor to track the rate of LOB data growth over time.

lob_object_l_pages - LOB data logical pages monitor element

The number of logical pages used on disk by LOBs associated with this table.

Table 19. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics Always collected

Usage

  • This value might be less than the amount of space physically allocated for the object. This can happen when you use the REUSE STORAGE option of the TRUNCATE statement. This option causes storage allocated for the table to continue to be allocated, although the storage will be considered empty. In addition, the value for this monitor element might be less than the amount of space logically allocated for the object, because the total space logically allocated includes a small amount of additional meta data.

    To retrieve an accurate measure of the logical or physical size of an object, use the ADMIN_GET_TAB_INFO_V97 function. This function provides more accurate information about the size of objects than you can obtain by multiplying the number of pages reported for this monitor element by the page size.

local_cons - Local Connections monitor element

The number of local applications that are currently connected to a database within the database manager instance being monitored.

Table 20. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic

Usage

This number can help you determine the level of concurrent processing occurring in the database manager. This value will change frequently, so you may need to sample it at specific intervals over an extended period of time to get a realistic view of system usage.

This number only includes applications that were initiated from the same instance as the database manager. The applications are connected, but may or may not be executing a unit of work in the database.

When used in conjunction with the rem_cons_in monitor element, this element can help you adjust the setting of the max_connections configuration parameter.

local_cons_in_exec - Local Connections Executing in the Database Manager monitor element

The number of local applications that are currently connected to a database within the database manager instance being monitored and are currently processing a unit of work.

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

Usage

This number can help you determine the level of concurrent processing occurring in the database manager. This value will change frequently, so you may need to sample it at specific intervals over an extended period of time to get a realistic view of system usage. This number only includes applications that were initiated from the same instance as the database manager.

When used in conjunction with the rem_cons_in_exec monitor element, this element can help you adjust the setting of the max_coordagents configuration parameter.

The following recommendations apply to non-concentrator configurations only. When concentrator is enabled, DB2® is multiplexing a larger number of client connections onto a smaller pool of coordinator agents. In this case, it is usually acceptable to have the sum of rem_cons_in_exec and local_cons_in_exec approach the max_coordagents value.
  • If max_coordagents is set to AUTOMATIC, do not make any adjustments.
  • If max_coordagents is not set to AUTOMATIC and if the sum of rem_cons_in_exec and local_cons_in_exec is close to max_coordagents, increase the value of max_coordagents.

local_start_time - Local start time monitor element

The time that this activity began doing work on the member. It is in local time. This field can be an empty string when an activity has entered the system but is in a queue and has not started executing.

Table 22. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected
WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities Always collected

Usage

local_transaction_id - Local transaction identifier monitor element

The local transaction ID in use at the time the event occurred.

Table 23. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change history ddlstmtexec
txncompletion		 Always collected
Unit of Work uow  

Usage

For the change history event monitor, this is the local transaction ID in use at the time the event occurred. This is the SQLU_TID structure that is part of the transaction logs.

location - Location monitor element

Iidentifies the location associated with the event.

Table 24. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History UTILLOCATION Always collected

Usage

For the change history event monitor, locations depend on the UTILITY_TYPE, for example, load input files or backup target path name.

location_type - Location type monitor element

A description of what the location is used for.

Table 25. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History UTILLOCATION Always collected

Usage

If the utility_type element is LOAD, one of:
C
Copy target
D
Input data
L
LOB path
X
XML path
If the utility_type element is BACKUP, one of:
B
Backup target location
If the utility_type element is RESTORE, one of:
S
Restore source location
If the utility_type element is ROLLFORWARD, one of:
O
Alternate overflow log path captured as part of the ROLLFORWARD DATABASE command. Note that if the default overflow log path is used, no location record will be captured.
otherwise a blank character.

lock_attributes - Lock attributes monitor element

The lock attributes of the application that is currently holding the lock.

Table 26. 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
MON_GET_LOCKS table function - list all locks in the currently connected database Always collected
Table 27. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Lock lock_wait Basic
Table 28. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks1 lock Always collected
Deadlocks1 event_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

The following table lists all possible lock attribute settings. Each lock attribute setting is based upon a bit flag value defined in sqlmon.h.
Lock Attribute Value in Table Functions API Constant Description
0000000000000001 SQLM_LOCKATTR_WAIT_FOR_AVAIL Wait for availability.
0000000000000002 SQLM_LOCKATTR_ESCALATED Acquired by escalation.
0000000000000004 SQLM_LOCKATTR_RR_IN_BLOCK RR lock in block.
0000000000000008 SQLM_LOCKATTR_INSERT Insert lock.
0000000000000010 SQLM_LOCKATTR_RR Lock by RR scan.
0000000000000020 SQLM_LOCKATTR_UPDATE_DELETE Update/delete row lock.
0000000000000040 SQLM_LOCKATTR_ALLOW_NEW Allow new lock requests.
0000000000000080 SQLM_LOCKATTR_NEW_REQUEST A new lock requester.
0000000000000200 SQLM_LOCKATTR_INDOUBT Lock held by Indoubt Transaction.
0000000000000400 SQLM_LOCKATTR_LOW_PRIORITY Lock held by low priority application.

Bits returned that are not listed in the previously shown table are reserved for internal use.

lock_count - Lock count monitor element

The number of locks on the lock being held.

Table 29. 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
Table 30. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Table 31. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks1 lock Always collected
Deadlocks1 event_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

This value ranges from 1 to 255. It is incremented as new locks are acquired, and decremented as locks are released.

When the lock_count monitor element has a value of 255, this indicates that a transaction duration lock is being held. At this point, the lock_count monitor element is no longer incremented or decremented when locks are acquired or released. The lock_count monitor element is set to a value of 255 in one of two possible ways:
  1. The lock_count monitor element value is incremented 255 times due to new locks being acquired.
  2. A transaction duration lock is explicitly acquired. For example, with a LOCK TABLE statement, or an INSERT.

lock_current_mode - Original lock mode before conversion monitor element

During a lock conversion operation, the lock mode held by the application waiting to acquire the lock, before the conversion is completed.

Table 32. 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
MON_GET_LOCKS table function - list all locks in the currently connected database Always collected
Table 33. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Lock lock_wait Basic
Table 34. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks1 lock Always collected
Deadlocks1 event_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

The following scenario describes an example of lock conversion. During an update or delete operation it is possible to wait for an X lock on the target row. If the transaction is holding an S or V lock on the row, this would require a conversion. At this point, the lock_current_mode element is assigned a value of S or V, while the lock waits to be converted to an X lock.

The possible lock modes are listed in the following table.

Mode Type of Lock API Constant
No Lock SQLM_LNON
IS Intention Share Lock SQLM_LOIS
IX Intention Exclusive Lock SQLM_LOIX
S Share Lock SQLM_LOOS
SIX Share with Intention Exclusive Lock SQLM_LSIX
X Exclusive Lock SQLM_LOOX
IN Intent None SQLM_LOIN
Z Super Exclusive Lock SQLM_LOOZ
U Update Lock SQLM_LOOU
NS Scan Share Lock SQLM_LONS
NW Next Key Weak Exclusive Lock SQLM_LONW

lock_escalation - Lock escalation monitor element

Indicates whether the application waiting to acquire this lock was a result of a lock escalation request. The possible values are Y (Yes) and N (No).

Table 35. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting Always collected
Table 36. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Lock
Lock lock_wait Lock
Table 37. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - 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

Use this element to better understand the cause of deadlocks. If you experience a deadlock that involves applications doing lock escalation, you may want to increase the amount of lock memory or change the percentage of locks that any one application can request.

lock_escals - Number of lock escalations monitor element

The number of times that locks have been escalated from several row locks to a table lock.

Table 38. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_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_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
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 39. 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 40. 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
Transactions event_xact Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

A lock is escalated when the total number of locks held by an application reaches the maximum amount of lock list space available to the application, or the lock list space consumed by all applications is approaching the total lock list space. The amount of lock list space available is determined by the maxlocks and locklist configuration parameters.

When an application reaches the maximum number of locks allowed and there are no more locks to escalate, it will then use space in the lock list allocated for other applications. When the entire lock list is full, an error occurs.

This data item includes a count of all lock escalations, including exclusive lock escalations and escalations in the DB2 pureScale® environment. To determine just the lock escalations in the DB2 pureScale environment, use the lock_escals_global monitor element.

There are several possible causes for excessive lock escalations:
  • The lock list size (locklist) may be too small for the number of concurrent applications
  • The percent of the lock list usable by each application (maxlocks) may be too small
  • One or more applications may be using an excessive number of locks.
  • In the DB2 pureScale environment, the global lock list size (cf_lock_sz) may be too small.
To resolve these problems, you may be able to:
  • Increase the locklist configuration parameter value.
  • Increase the maxlocks configuration parameter value.
  • Identify the applications with large numbers of locks, or those that are holding too much of the lock list, using one of the following formulae, and comparing the value to maxlocks.
    • On 64-bit systems, (((locks held * 64) / (locklist * 4096)) * 100)
    • On 32-bit systems, (((locks held * 48) / (locklist * 4096)) * 100)
    These applications can also cause lock escalations in other applications by using too large a portion of the lock list. These applications may need to resort to using table locks instead of row locks, although table locks may cause an increase in lock_waits and lock_wait_time monitor element values.

lock_escals_global - Number of global lock escalations monitor element

Number of lock escalations on a global lock due to global lock memory usage reaching the limit specified in the cf_lock_sz database configuration parameter.

Table 41. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache 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_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
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 42. 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 - Always collected
Package cache - Always collected
Locking - Always collected

Usage

Use this monitor element together with the lock_escals_maxlocks and lock_escals_locklist monitor elements to determine which lock space configuration parameter is causing escalations on the database.

lock_escals_locklist - Number of locklist lock escalations monitor element

Number of lock escalations due to local lock memory usage reaching the limit specified in the locklist database configuration parameter.

Table 43. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache 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
Table 44. 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 - Always collected
Package cache - Always collected
Locking - Always collected

Usage

Use this monitor element together with the lock_escals_maxlocks and lock_escals_global monitor elements to determine which lock space configuration parameter is causing escalations on the database.

lock_escals_maxlocks - Number of maxlocks lock escalations monitor element

Number of lock escalations due to local lock memory usage reaching the limit specified in the maxlocks database configuration parameter.

Table 45. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache 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
Table 46. 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 - Always collected
Package cache - Always collected
Locking - Always collected

Usage

Use this monitor element together with the lock_escals_locklist and lock_escals_global monitor elements to determine which lock space configuration parameter is causing escalations on the database.

lock_hold_count - Lock hold count monitor element

The number of holds placed on the lock. Holds are placed on locks by cursors registered with the WITH HOLD clause and some DB2 utilities. Locks with holds are not released when transactions are committed.

Table 47. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Table 48. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks1 lock Always collected
Deadlocks1 event_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.

lock_list_in_use - Total lock list memory in use monitor element

The total amount of lock list memory (in bytes) that is in use.

Table 49. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic

Usage

This element may be used in conjunction with the locklist configuration parameter to calculate the lock list utilization. If the lock list utilization is high, you may want to consider increasing the size of that parameter.

Note: When calculating utilization, it is important to note that the locklist configuration parameter is allocated in pages of 4 KB each, while this monitor element provides results in bytes.

lock_mode - Lock mode monitor element

The type of lock being held. If the mode is unknown, the value of this monitor element is NULL.

Table 50. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting Always collected
MON_GET_LOCKS table function - list all locks in the currently connected database Always collected
Table 51. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock
Lock lock Lock
Lock lock_wait Lock
Table 52. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks1 lock -
Deadlocks1 event_dlconn -
Deadlocks with Details1 event_detailed_dlconn -
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 mode can help you determine the source of contention for resources.

This element indicates one of the following, depending on the type of monitor information being examined:
  • The type of lock another application holds on the object that this application is waiting to lock (for application-monitoring and deadlock-monitoring levels).
  • The type of lock held on the object by this application (for object-lock levels).

The possible values for this field are:

Mode Type of Lock API Constant
No Lock SQLM_LNON
IS Intention Share Lock SQLM_LOIS
IX Intention Exclusive Lock SQLM_LOIX
S Share Lock SQLM_LOOS
SIX Share with Intention Exclusive Lock SQLM_LSIX
X Exclusive Lock SQLM_LOOX
IN Intent None SQLM_LOIN
Z Super Exclusive Lock SQLM_LOOZ
U Update Lock SQLM_LOOU
NS Scan Share Lock SQLM_LONS
NW Next Key Weak Exclusive Lock SQLM_LONW

lock_mode_requested - Lock mode requested monitor element

The mode in which the lock was requested by the application waiting to acquire the lock.

Table 53. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting Always collected
Table 54. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock_wait Lock
Table 55. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - 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

The mode in which the lock was requested by the application. This value can help you determine the source of contention for resources.

The possible lock modes are listed in the following table.

Mode Type of Lock API Constant
No Lock SQLM_LNON
IS Intention Share Lock SQLM_LOIS
IX Intention Exclusive Lock SQLM_LOIX
S Share Lock SQLM_LOOS
SIX Share with Intention Exclusive Lock SQLM_LSIX
X Exclusive Lock SQLM_LOOX
IN Intent None SQLM_LOIN
Z Super Exclusive Lock SQLM_LOOZ
U Update Lock SQLM_LOOU
NS Scan Share Lock SQLM_LONS
NW Next Key Weak Exclusive Lock SQLM_LONW

lock_name - Lock name monitor element

Internal binary lock name. This element serves as a unique identifier for locks.

Table 56. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting Always collected
MON_GET_LOCKS table function - list all locks in the currently connected database Always collected
Table 57. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Lock lock_wait lock_wait
Table 58. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks1 lock -
Deadlocks1 event_dlconn -
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

The internal name can be formatted using the routine MON_FORMAT_LOCK_NAME to obtain more details about the lock. For example, if this is a table lock, then you can obtain the table and tablespace that the lock references.

lock_node - Lock Node monitor element

The node involved in a lock.

Table 59. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Deadlocks event_dlconn Statement
Deadlocks with Details event_detailed_dlconn Statement
Usage
This can be used for troubleshooting.

lock_object_name - Lock Object Name monitor element

This element is provided for informational purposes only. It is the name of the object for which the application holds a lock (for object-lock-level information), or the name of the object for which the application is waiting to obtain a lock (for application-level and deadlock-level information).

Note: This monitor element has been deprecated. Using this monitor element will not generate an error. However, it does not return a valid value. This monitor element is no longer recommended and might be removed in a future release.
Table 60. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock
Lock appl_lock_list Lock
Lock lock Basic
Table 61. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks lock Always collected
Deadlocks event_dlconn Always collected
Deadlocks with Details event_detailed_dlconn Always collected
Usage
For table-level locks, it is the file ID (FID) for SMS and DMS table spaces. For row-level locks, the object name is the row ID (RID). For table space locks, the object name is blank. For buffer pool locks, the object name is the name of the buffer pool.

To determine the table holding the lock, use table_name and table_schema instead of the file ID, since the file ID may not be unique.

To determine the table space holding the lock, use tablespace_name.

lock_object_type - Lock object type waited on monitor element

The type of object against which the application holds a lock (for object-lock-level information), or the type of object for which the application is waiting to obtain a lock (for application-level and deadlock-level information).

Table 62. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_FORMAT_LOCK_NAME table function - format the internal lock name and return details Always collected
MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting Always collected
MON_GET_LOCKS table function - list all locks in the currently connected database Always collected
Table 63. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock
Lock appl_lock_list Lock
Lock lock Basic
Lock lock_wait Lock
Table 64. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks1 lock -
Deadlocks1 event_dlconn -
Deadlocks with Details1 event_detailed_dlconn -
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.

For snapshot monitoring and the deadlock1 event monitor, the object type identifiers are defined in sqlmon.h. The objects may be one of the following types:
  • Table space (SQLM_TABLESPACE_LOCK in sqlmon.h)
  • Table
  • Buffer pool
  • Block
  • Record (or row)
  • Data partition (SQLM_TABLE_PART_LOCK in sqlmon.h)
  • Internal (another type of lock held internally by the database manager)
  • Automatic resize
  • Automatic storage.

For the locking event monitor and the monitoring table functions in Table 1, the possible values for the lock_object_type monitor element are defined in Table 4.

Table 65. Possible values for lock_object_type monitor element
Possible values Description
TABLE Table lock
ROW Row lock
TABLESPACE Table space lock
EOT End of table lock
KEYVALUE Key value lock
SYSBOOT Sysboot lock
PLAN Plan lock
VARIATION Variation lock
SEQUENCE Sequence lock
BUFFERPOOL Buffer pool lock
LOB LOB/Long region lock
CATALOG Catalog cache lock
ONLINE_BACKUP Online backup lock
OBJECT_TABLE Object table lock
ALTER_TABLE Table alter lock
DMS_SEQUENCE DMS sequence lock
REORG Inplace reorganization lock
MDC_BLOCK MDC block lock
TABLE_PARTITION Table partition lock
AUTORESIZE Autoresize lock
AUTOSTORAGE Autostorage lock
XMLPATH XML path lock
EXTENT_MOVEMENT Extent movement lock
WORKLOAD Workload authorization lock
FED_SERVER Federation server lock
FED_USER Federation user mapping lock
CHUNK Chunk lock
LOAD_PRE_PART Load table pre-partitioning lock
LOAD_PART Load table partitioning lock
LOAD_TS Loading table space lock
LONG_FIELD_ESC Long field escalation lock
LONG_FIELD_SPACE Long field buddy space lock

lock_release_flags - Lock release flags monitor element

Lock release flags.

Table 66. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_LOCKS table function - List all locks in the currently connected database Always collected
Table 67. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Lock lock_wait Basic
Table 68. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks1 lock Always collected
Deadlocks1 event_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

The following table lists all possible release flag settings. Each release flag is based upon a bit flag value defined in sqlmon.h.
API Constant Description
SQLM_LOCKRELFLAGS_SQLCOMPILER Locks by SQL compiler.
SQLM_LOCKRELFLAGS_UNTRACKED Non-unique, untracked locks.
Note: All non-assigned bits are used for application cursors.

lock_status - Lock status monitor element

Indicates the internal status of the lock.

Table 69. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting Always collected
MON_GET_LOCKS table function - list all locks in the currently connected database Always collected
Table 70. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Table 71. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks1 lock 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

This element can help explain what is happening when an application is waiting to obtain a lock on an object. While it may appear that the application already has a lock on the object it needs, it may have to wait to obtain a different type of lock on the same object.

The lock can be in one of the following statuses:
G
Granted state: The application has the lock in the state specified by the lock_mode monitor element.
C
Converting state: The application is trying to change the lock held to a different type; for example, changing from a share lock to an exclusive lock.
W
Waiting state.
Note: The lock event monitor, deadlock event monitor, and snapshot APIs report numeric values rather than the character values described above. The following table shows the numeric values used for each of the above statuses:
Table 72. Numeric lock_status values
Lock event monitor Snapshot APIs and deadlock event monitor
1 - Granted 1 - Granted
4 - Converting 2 - Converting
2 - Waiting not applicable
Note: API users should refer to the sqlmon.h header file containing definitions of database system monitor constants.

lock_timeout_val - Lock timeout value monitor element

Indicates the timeout value (in seconds) when an application has issued a SET CURRENT LOCK TIMEOUT statement. In cases where the statement has not been executed, the database level lock timeout will be shown.

Table 73. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Application agent Basic
Table 74. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -

Usage

The SET CURRENT LOCK TIMEOUT statement can be used to specify the maximum duration for which application agents will wait for a table or index lock.

If an application is waiting too long on a lock, you can check the lock_timeout_val monitor element value to see whether it is set too high inside the application. You can modify the application to lower the lock timeout value to let the application time out, if that is appropriate for the application logic. You can accomplish this modification with the SET CURRENT LOCK TIMEOUT statement.

If the application is timing out frequently, you can check whether the lock timeout value is set too low and increase it as appropriate.

lock_timeouts - Number of lock timeouts monitor element

The number of times that a request to lock an object timed out instead of being granted.

Table 75. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_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
Table 76. 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 77. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

This element can help you adjust the setting for the locktimeout database configuration parameter. If the number of lock timeouts becomes excessive when compared to normal operating levels, you may have an application that is holding locks for long durations. In this case, this element may indicate that you should analyze some of the other lock and deadlock monitor elements to determine if you have an application problem.

You could also have too few lock timeouts if your locktimeout database configuration parameter is set too high. In this case, your applications may wait excessively to obtain a lock.

lock_timeouts_global - Lock timeouts global monitor element

Number of lock timeouts where the application holding the lock was on a remote member.

Table 78. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache 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
Table 79. 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 - Always collected
Package cache - Always collected
Locking - Always collected

Usage

Use this element in conjunction with the lock_timeouts monitor element. The lock_timeouts_global monitor element represents the number of times a lock timeout has occurred while waiting to acquire a lock held on another member. To determine the number of times a lock timeout has occurred while waiting to acquire a lock held on the same member, use the following formula:
lock_timeouts - lock_timeouts_global

Outside of the DB2 pureScale environment, this value is always zero.

lock_wait_end_time - Lock wait end timestamp monitor element

The date and time the application stopped waiting to obtain a lock on the object that is currently lock.

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

lock_wait_start_time - Lock wait start timestamp monitor element

The date and time that this application started waiting to obtain a lock on the object that is currently locked by another application.

Table 81. 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
Table 82. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock, Timestamp
Lock lock_wait Lock, Timestamp
Table 83. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks 1 event_dlconn Timestamp
Deadlocks with Details 1 event_detailed_dlconn Timestamp
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 severity of resource contention.

lock_wait_time - Time waited on locks monitor element

The total elapsed time spent waiting for locks. The value is given in milliseconds.

Table 84. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_TIMES_BY_ROW - Get formatted row-based combined hierarchy wait and processing times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_WAIT_TIMES_BY_ROW - Get formatted row-based output for wait times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_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_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
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_ROUTINE_EXEC_LIST table function - get list of statements executed by routine Always collected
Table 85. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Lock
Application appl Lock
Lock appl_lock_list appl_lock_list
For snapshot monitoring, this counter can be reset.
Table 86. 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
Transactions event_xact Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

At the database level, this is the total amount of elapsed time that all applications were waiting for a lock within this database. This measure of elapsed time can include time spent on locks taken during activities, as well as locks taken during other processing, such compilation.

At the application-connection and transaction levels, this is the total amount of elapsed time that this connection or transaction has waited for a lock to be granted to it.

The value for this element does not include lock wait times for agents that are currently still in a lock wait state. It only includes lock wait times for agents that have already completed their lock waits.

This element may be used in conjunction with the lock_waits monitor element to calculate the average wait time for a lock. This calculation can be performed at either the database or the application-connection level.

When using monitor elements providing elapsed times, you should consider:
  • Elapsed times are affected by system load, so the more processes you have running, the higher this elapsed time value.
  • To calculate this element at the 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, you can calculate the average wait time for a lock, as previously shown.

lock_wait_time_global - Lock wait time global monitor element

Time spent on global lock waits. The unit of measurement for time is in milliseconds.

Table 87. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_TIMES_BY_ROW - Get formatted row-based combined hierarchy wait and processing times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_WAIT_TIMES_BY_ROW - Get formatted row-based output for wait times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache 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_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
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 88. 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 - Always collected
Package cache - Always collected
Locking - Always collected

Usage

Use this monitor element in conjunction with the lock_wait_time monitor element, which represents all the time spent waiting for locks. The lock_wait_time_global monitor element represents the time spent waiting for locks held by conflicting applications on different members. To determine the total time spent waiting for locks held by conflicting applications on the same member, use the following formula:
lock_wait_time - lock_wait_time_global

Outside of the DB2 pureScale environment, this value is always zero.

lock_wait_time_global_top - Top global lock wait time monitor element

The longest lock wait that has occurred for a lock that is held on another member. This value is reported in milliseconds.

Table 89. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_wlstats Always collected

lock_wait_time_top - Lock wait time top monitor element

The high watermark for lock wait times of any request in a workload. Units are milliseconds. The lock_wait_time_top high watermark is always collected for workloads. A request contributes toward this high watermark only when request metrics are enabled.

Table 90. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_wlstats Always collected

Usage

Use this element to determine the highest lock wait time of any request on a partition for a workload during the time interval collected.

lock_wait_val - Lock wait value monitor element

The amount of time spent in lock wait (in milliseconds) before an event for mon_lockwait is generated.

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

lock_waits - Lock waits monitor element

The total number of times that applications or connections waited for locks.

Table 92. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_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_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
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 93. 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 94. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

At the database level, this is the total number of times that applications have had to wait for locks within this database.

At the application-connection level, this is the total number of times that this connection requested a lock but had to wait because another connection was already holding a lock on the data.

This element may be used with lock_wait_time to calculate, at the database level, the average wait time for a lock. This calculation can be done at either the database or the application-connection level.

If the average lock wait time is high, you should look for applications that hold many locks, or have lock escalations, with a focus on tuning your applications to improve concurrency, if appropriate. If escalations are the reason for a high average lock wait time, then the values of one or both of the locklist and maxlocks configuration parameters may be too low.

lock_waits_global - Lock waits global monitor element

Number of lock waits due to the application holding the lock being on a remote member.

Table 95. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache 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_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
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 96. 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 - Always collected
Package cache - Always collected
Locking - Always collected

Usage

Use this monitor element in conjunction with the lock_waits monitor element, which reports the total number of lock waits due to locks held by conflicting applications on all members. The lock_waits_global monitor element indicates the number of times that a lock wait was held by conflicting applications on different members. To determine the number of lock waits held by a conflicting application on the same member as the waiting application, use the following formula:
lock_waits - lock_waits_global

Outside of the DB2 pureScale environment, this value is always zero.

locks_held - Locks held monitor element

The number of locks currently held.

Table 97. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics Always collected
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) Always collected
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics Always collected
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) Always collected
Table 98. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
Lock db_lock_list Basic
Lock appl_lock_list Basic
Table 99. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks with Details event_detailed_dlconn Always collected

Usage

If the monitor information is at the database level, this is the total number of locks currently held by all applications in the database.

If the monitor information is at the application level, this is the total number of locks currently held by all agents for the application.

locks_held_top - Maximum number of locks held monitor element

The maximum number of locks held during this transaction.

Table 100. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Transactions event_xact Always collected

Usage

You can use this element to determine if your application is approaching the maximum number of locks available to it, as defined by the maxlocks configuration parameter. This parameter indicates the percentage of the lock list that each application can use before lock escalations occur. Lock escalations can result in a decrease in concurrency between applications connected to a database.

Since the maxlocks parameter is specified as a percentage and this element is a counter, you can compare the count provided by this element against the total number of locks that can be held by an application, as calculated using one of the following formulae:
  • On 64-bit systems, (locklist * 4096 / 64 ) * (maxlocks / 100)
  • On 32-bit systems, (locklist * 4096 / 48 ) * (maxlocks / 100)

If you have a large number of locks, you may need to perform more commits within your application so that some of the locks can be released.

locks_in_list - Number of Locks Reported monitor element

The number of locks held by a particular application to be reported on by the event monitor.

Table 101. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks with Details event_detailed_dlconn Always collected

locks_waiting - Current agents waiting on locks monitor element

Indicates the number of agents waiting on a lock.

Table 102. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
Lock db_lock_list Basic

Usage

When used in conjunction with appls_cur_cons, this element indicates the percentage of applications waiting on locks. If this number is high, the applications may have concurrency problems, and you should identify applications that are holding locks or exclusive locks for long periods of time.

log_buffer_wait_time - Log buffer wait time monitor element

The amount of time an agent spends waiting for space in the log buffer. The value is given in milliseconds.

Table 103. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_TIMES_BY_ROW - Get formatted row-based combined hierarchy wait and processing times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_WAIT_TIMES_BY_ROW - Get formatted row-based output for wait times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_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
Table 104. 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

log_disk_wait_time - Log disk wait time monitor element

The amount of time an agent spends waiting for log records to be flushed to disk. The value is given in milliseconds.

Table 105. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_TIMES_BY_ROW - Get formatted row-based combined hierarchy wait and processing times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_WAIT_TIMES_BY_ROW - Get formatted row-based output for wait times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_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
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 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

log_disk_waits_total - Total log disk waits monitor element

The number of times agents have to wait for log data to write to disk.

Table 107. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_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
Table 108. 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

log_hadr_wait_cur - Current logger wait time monitor element

The current logger waiting time on an HADR log shipping request. Returns 0 if logger is not waiting. When wait time reaches peer wait limit, HADR will break out of peer state to unblock the primary database. If logger is completely blocked, log_hadr_wait_cur and log_hadr_wait_time will grow in real time, while log_hadr_waits_total stays the same. Units are milliseconds.

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

log_hadr_wait_time - Total logger wait time monitor element

The total time the logger spent waiting for HADR to ship logs. With log_hadr_wait_time and log_hadr_waits_total, you can compute average HADR wait time per log flush in arbitrary interval. The two fields are also reported by table function mon_get_transaction_log. Units are milliseconds.

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

log_hadr_waits_total - Total logger wait events monitor element

The total number of HADR wait events in the logger. The count is incremented every time logger initiates a wait on HADR log shipping, even if the wait returns immediately. Thus this count is effectively the number of log flushes in peer state. With log_hadr_wait_time and log_hadr_waits_total, you can compute average HADR wait time per log flush in arbitrary interval. The two fields are also reported by table function mon_get_transaction_log.

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

log_held_by_dirty_pages - Amount of Log Space Accounted for by Dirty Pages monitor element

The amount of log (in bytes) corresponding to the difference between the oldest dirty page in the database and the top of the active log.

Element identifier
log_held_by_dirty_pages
Element type
watermark
Table 112. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 113. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 114. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
When the snapshot is taken, this value is calculated based on conditions at the time of that snapshot.

Use this element to evaluate the effectiveness of page cleaning for older pages in the buffer pool.

The cleaning of old pages in the buffer pool is governed by the softmax database configuration parameter. If the page cleaning is effective then log_held_by_dirty_pages should be less than or approximately equal to: 

  (softmax / 100)  *  logfilsiz * 4096

If this statement is not true, increase the number of page cleaners (num_iocleaners) configuration parameter.

If the condition is true and it is required that less log be held by dirty pages, then decrease the softmax configuration parameter.

log_read_time - Log Read Time monitor element

The total elapsed time spent by the logger reading log data from the disk. For event monitors that write to tables, the value of this element is given in microseconds by using the BIGINT data type.

Table 115. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 116. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 117. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
Use this element in conjunction with the log_reads, num_log_read_io, and num_log_data_found_in_buffer elements to determine if:
  • The current disk is adequate for logging.
  • The log buffer size is adequate.

log_reads - Number of Log Pages Read monitor element

The number of log pages read from disk by the logger.

Element identifier
log_reads
Element type
counter
Table 118. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 119. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 120. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
You can use this element with an operating system monitor to quantify the amount of I/O on a device that is attributable to database activity.

log_to_redo_for_recovery - Amount of Log to be Redone for Recovery monitor element

The amount of log (in bytes) that will have to be redone for crash recovery.

Element identifier
log_to_redo_for_recovery
Element type
watermark
Table 121. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 122. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 123. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
When the snapshot is taken, this value is calculated based on conditions at the time of that snapshot. Larger values indicate longer recovery times after a system crash. If the value seems excessive, check the log_held_by_dirty_pages monitor element to see if page cleaning needs to be tuned. Also check if there are any long running transactions that need to be terminated.

log_write_time - Log Write Time monitor element

The total elapsed time spent by the logger writing log data to the disk. For event monitors that write to tables, the value of this element is given in microseconds by using the BIGINT data type.

Table 124. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 125. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 126. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
Use this element in conjunction with the log_writes and num_log_write_io elements to determine if the current disk is adequate for logging.

log_writes - Number of Log Pages Written monitor element

The number of log pages written to disk by the logger.

Element identifier
log_writes
Element type
counter
Table 127. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 128. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 129. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
You may use this element with an operating system monitor to quantify the amount of I/O on a device that is attributable to database activity.
Note: When log pages are written to disk, the last page might not be full. In such cases, the partial log page remains in the log buffer, and additional log records are written to the page. Therefore log pages might be written to disk by the logger more than once. You should not use this element to measure the number of pages produced by DB2.

long_object_pages - Long Object Pages monitor element

The number of disk pages consumed by long data in a table.

Table 131. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
Table 132. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Tables event_table Always collected
Usage
This element provides a mechanism for viewing the actual amount of space consumed by long data in a particular table. This element can be used in conjunction with a table event monitor to track the rate of long data growth over time.

long_object_l_pages - Long object data logical pages monitor element

The number of logical pages used on disk by long data contained in this table.

Table 133. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TABLE table function - Get table metrics Always collected

Usage

  • This value might be less than the amount of space physically allocated for the object. This can happen when you use the REUSE STORAGE option of the TRUNCATE statement. This option causes storage allocated for the table to continue to be allocated, although the storage will be considered empty. In addition, the value for this monitor element might be less than the amount of space logically allocated for the object, because the total space logically allocated includes a small amount of additional meta data.

    To retrieve an accurate measure of the logical or physical size of an object, use the ADMIN_GET_TAB_INFO_V97 function. This function provides more accurate information about the size of objects than you can obtain by multiplying the number of pages reported for this monitor element by the page size.

long_tbsp_id - Long table space ID monitor element

An identifier of the table space that holds long data (LONG or LOB type columns) for this table.

Table 134. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_TABLE table function - Get table metrics Always collected

Usage

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