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.

Table 4. Table function monitoring information
Table function Monitor element collection level
MON_GET_DATABASE table function - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics Always collected
Table 5. 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 for snapshots and NULL for table functions.

last_executable_id - Last executable identifier monitor element

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

Table 6. 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 8. 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 9. 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 10. 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_reference_time - Last reference time monitor element

The last time the activity was accessed by a request.

Table 11. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities Always collected
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

last_request_type - Last request type monitor element

The type of the last request completed by the application.

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

Table 13. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
MON_GET_SERVICE_SUPERCLASS_STATS table function - Return statistics of service superclasses Always collected
MON_GET_WORKLOAD_STATS table function - Return workload statistics Always collected
MON_GET_WORK_ACTION_SET_STATS table function - Return work action set statistics Always collected
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 14. 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 16. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics -
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 17. 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 subroutine.

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 18. 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.

lob_object_pages - LOB Object Pages monitor element

The number of disk pages consumed by LOB data.

Table 19. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
Table 20. 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.

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 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. 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 22. 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 23. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities Always collected
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 24. 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 25. 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 26. 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 27. 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 28. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Lock lock_wait Basic
Table 29. 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
Note: Event monitors return this element as a char(8) field.
 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 currently held.

Table 30. 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 31. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Table 32. 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 0 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 33. 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 34. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Lock lock_wait Basic
Table 35. 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 Numeric value
No Lock SQLM_LNON 0
IS Intention Share Lock SQLM_LOIS 1
IX Intention Exclusive Lock SQLM_LOIX 2
S Share Lock SQLM_LOOS 3
SIX Share with Intention Exclusive Lock SQLM_LSIX 4
X Exclusive Lock SQLM_LOOX 5
IN Intent None SQLM_LOIN 6
Z Super Exclusive Lock SQLM_LOOZ 7
U Update Lock SQLM_LOOU 8
NS Scan Share Lock SQLM_LONS 9
NX Next-Key Exclusive Lock SQLM_LONX 10
W Weak Exclusive Lock SQLM_LOOW 11
NW Next Key Weak Exclusive Lock SQLM_LONW 12

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 36. 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 37. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Lock
Lock lock_wait Lock
Table 38. 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 39. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_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
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 40. 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 41. 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 42. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_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
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 43. 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 - REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE

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 44. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 45. 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 - REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE

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 46. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 47. 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 - REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE

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 48. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Table 49. 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 50. Table function monitoring information
Table function Monitor element collection level
MON_GET_DATABASE table function - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics Always collected
Table 51. 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 52. 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 53. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock
Lock lock Lock
Lock lock_wait Lock
Table 54. 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 Numeric value
No Lock SQLM_LNON 0
IS Intention Share Lock SQLM_LOIS 1
IX Intention Exclusive Lock SQLM_LOIX 2
S Share Lock SQLM_LOOS 3
SIX Share with Intention Exclusive Lock SQLM_LSIX 4
X Exclusive Lock SQLM_LOOX 5
IN Intent None SQLM_LOIN 6
Z Super Exclusive Lock SQLM_LOOZ 7
U Update Lock SQLM_LOOU 8
NS Scan Share Lock SQLM_LONS 9
NX Next-Key Exclusive Lock SQLM_LONX 10
W Weak Exclusive Lock SQLM_LOOW 11
NW Next Key Weak Exclusive Lock SQLM_LONW 12

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 55. 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 56. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock_wait Lock
Table 57. 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 Numeric value
No Lock SQLM_LNON 0
IS Intention Share Lock SQLM_LOIS 1
IX Intention Exclusive Lock SQLM_LOIX 2
S Share Lock SQLM_LOOS 3
SIX Share with Intention Exclusive Lock SQLM_LSIX 4
X Exclusive Lock SQLM_LOOX 5
IN Intent None SQLM_LOIN 6
Z Super Exclusive Lock SQLM_LOOZ 7
U Update Lock SQLM_LOOU 8
NS Scan Share Lock SQLM_LONS 9
NX Next-Key Exclusive Lock SQLM_LONX 10
W Weak Exclusive Lock SQLM_LOOW 11
NW Next Key Weak Exclusive Lock SQLM_LONW 12

lock_name - Lock name monitor element

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

Table 58. 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 59. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Lock lock_wait lock_wait
Table 60. 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 61. 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 62. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock
Lock appl_lock_list Lock
Lock lock Basic
Table 63. 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 64. 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 65. 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 66. 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 67. 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 68. 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 69. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Lock lock_wait Basic
Table 70. 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 71. 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 72. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Lock lock Basic
Table 73. 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 74. 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 75. 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 76. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Application agent Basic
Table 77. 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 78. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 79. 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 80. 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 81. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 82. 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 - REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE

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 83. 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 84. 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 85. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock, Timestamp
Lock lock_wait Lock, Timestamp
Table 86. 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 87. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine ACTIVITY METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_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
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 88. 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 89. 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 90. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_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
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 91. 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 - REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE

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 92. 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 93. 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 94. 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 95. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine ACTIVITY METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_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
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 96. 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 97. 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 98. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_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
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 99. 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 - REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE

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 100. 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_DATABASE table function - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics 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 101. 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 102. 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 103. 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 104. 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 105. 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_DATABASE table function - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics 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 106. 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 107. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 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_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 109. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 110. 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
Note: In HADR environments, the log_disk_wait_time value is affected by the HADR synchronization mode being used.

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 111. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 112. 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 113. 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 114. 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 115. 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 116. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 117. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 118. 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 page_age_trgt_mcr database configuration parameter.

If it is required that less log are to be held by dirty pages, for example, to reduce crash recovery time, then decrease the page_age_trgt_mcr configuration parameter. If this action does not reduce the amount of log held by the dirty pages, then increase the number of page cleaners (num_iocleaners) 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 119. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 120. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 121. 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 122. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 123. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 124. 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 125. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 126. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 127. 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. The value is given in milliseconds for table function interfaces. For event monitors that write to tables, the value of this element is given in microseconds by using the BIGINT data type.

Table 128. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 129. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 130. 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 131. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 132. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
For snapshot monitoring, this counter can be reset.
Table 133. 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_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 135. 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_object_pages - Long Object Pages monitor element

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

Table 136. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
Table 137. 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_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 138. 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.