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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | detail_log | Basic |
| 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 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 |
| 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 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_EXEC_ERROR - The last error of the statement monitor element
The SQLCODE from the error SQLCA for the last error of this statement. If this statement has not had an error, then this value is NULL.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Package cache | event_pkgcache | Always collected |
LAST_EXEC_ERROR_SQLERRMC - The tokens for last statement error monitor element
The SQLERRMC from the error SQLCA containing SQLCODE tokens for the last error of this statement. If this statement has not had an error, then this value is NULL.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Package cache | event_pkgcache | Always collected |
LAST_EXEC_ERROR_TIMESTAMP - The time of last statement error monitor element
The time when the last error for this statement occurred. If this statement has not had an error, then this value is NULL.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Package cache | event_pkgcache | Always collected |
LAST_EXEC_WARNING - The last statement warning monitor element
The SQLCODE from the warning SQLCA for the last warning of this statement. If this statement has not had a warning this value is NULL.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Package cache | event_pkgcache | Always collected |
SQL0100W is not reported by this element.
Explanation
One of the following conditions is true:
- No row was found that meets the search conditions specified in an UPDATE or DELETE statement.
- The result of a SELECT statement was an empty table.
- A FETCH statement was executed when the cursor was positioned after the last row of the result table.
- The result of the SELECT used in an INSERT statement is empty.
User Response
No action is required. Processing can continue.
sqlcode: +100
sqlstate: 02000
LAST_EXEC_WARNING_SQLERRMC - The tokens for last statement warning monitor element
The SQLERRMC from the warning SQLCA containing SQLCODE tokens for the last warning of this statement. If this statement has not had a warning this value is NULL.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Package cache | event_pkgcache | Always collected |
LAST_EXEC_WARNING_TIMESTAMP - The time of last statement warning monitor element
The time when the last warning for this statement occurred. If this statement has not had a warning, then this value is NULL.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Package cache | event_pkgcache | Always collected |
last_extent - Last extent moved monitor element
The numeric identifier of the last extent moved by the table space rebalancer process.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_EXTENT_MOVEMENT_STATUS - Get extent movement progress status metrics | Always collected |
last_metrics_update - Metrics last update timestamp monitor element
Timestamp reflecting the last time metrics were updated for this cache entry.
| 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 |
| 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.
| 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 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 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.
| 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_updated - Last update time stamp monitor element
The time stamp indicating the last time this entry was updated.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list | Always collected |
| MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list | Always collected |
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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_osmetrics | - |
| Statistics | event_qstats | - |
| Statistics | event_scmetrics | Always collected |
| Statistics | event_scstats | - |
| Statistics | event_superclassmetrics | Always collected |
| Statistics | event_superclassstats | - |
| 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.
latch_name - Latch name monitor element
The unique name of a latch.
This value corresponds to latch names that are reported database diagnostic files, such as the db2diag log files, or the stack files.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_EXTENDED_LATCH_WAIT table function - Return information for latches | Always collected |
| MON_GET_LATCH table function - List all latches in the current member | Always collected |
latch_status - Latch status monitor element
The status of a latch.
The values for latch_status are:
- H
- An engine dispatchable unit is holding the latch
- W
- An engine dispatchable unit is waiting on the latch
| Table function | Monitor element collection level |
|---|---|
| MON_GET_LATCH table function - List all latches in the current member | Always collected |
latch_wait_time - Latch wait time monitor element
The time that an engine dispatchable unit spends waiting for a latch, in milliseconds.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_LATCH table function - List all latches in the current member | Always collected |
lbp_update_time - Local buffer pool update time monitor element
During extent movement, this monitor element shows the cumulative time taken to update cached page locations for moved extents in each member's local buffer pool.
The value is given in milliseconds.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_EXTENT_MOVEMENT_STATUS - get extent movement progress | Always collected |
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 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 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.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table | table | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Tables | event_table | Always collected |
- Usage
- This element provides a mechanism for viewing the actual amount of space consumed by LOB data in a particular table. This element can be used in conjunction with a table event monitor to track the rate of LOB data growth over time.
lob_prefetch_reqs - LOB prefetcher request count monitor element
The number of times an application waited for an I/O server (prefetcher) to finish a LOB read or write operation.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_superclassmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
lob_prefetch_wait_time - Wait time for prefetcher for LOB read or write monitor element
The time an application spent waiting for an I/O server (prefetcher) to read or write a LOB.
The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_superclassmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
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.
| 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.
| 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, the database system 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 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_tier_write_bytes - Bytes to write to local tier monitor element
The number of bytes written to local tier storage for remote table spaces.
Attention: This monitor element is currently only available for the Db2
Warehouse and Db2 Warehouse on Cloud products.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_BUFFERPOOL table function - Get buffer pool metrics | DATA OBJECT METRICS BASE |
| MON_GET_DATABASE table function - Get database level information | DATA OBJECT METRICS BASE |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | DATA OBJECT METRICS BASE |
| MON_GET_TABLESPACE table function - Get table space metrics | DATA OBJECT METRICS BASE |
local_tier_write_reqs - Write request for local tier monitor element
The number of local tier write requests for remote table spaces.
Attention: This monitor element is currently only available for the Db2
Warehouse and Db2 Warehouse on Cloud products.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_BUFFERPOOL table function - Get buffer pool metrics | DATA OBJECT METRICS BASE |
| MON_GET_DATABASE table function - Get database level information | DATA OBJECT METRICS BASE |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | DATA OBJECT METRICS BASE |
| MON_GET_TABLESPACE table function - Get table space metrics | DATA OBJECT METRICS BASE |
local_tier_write_time - Elapsed time to write monitor element
The elapsed time in milliseconds to write to local tier storage for remote table spaces.
Attention: This monitor element is currently only available for the Db2
Warehouse and Db2 Warehouse on Cloud products.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_BUFFERPOOL table function - Get buffer pool metrics | DATA OBJECT METRICS BASE |
| MON_GET_DATABASE table function - Get database level information | DATA OBJECT METRICS BASE |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | DATA OBJECT METRICS BASE |
| MON_GET_TABLESPACE table function - Get table space metrics | DATA OBJECT METRICS BASE |
local_transaction_id - Local transaction identifier monitor element
The local transaction ID in use at the time the event occurred.
| 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.
| 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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Change History | UTILLOCATION | Always collected |
Usage
If the
If the utility_type element
is LOAD, one of:- C
- Copy target
- D
- Input data
- L
- LOB path
- X
- XML path
utility_type element is
BACKUP, one of:- B
- Backup target location
utility_type element is RESTORE,
one of:- S
- Restore source location
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.
lock_attributes - Lock attributes monitor element
The lock attributes of the application that is currently holding the lock.
| 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock | Basic |
| Lock | lock_wait | Basic |
| 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. |
| 0000000000010000 | SQLM_LOCKATTR_INDOUBT | Lock held by Indoubt Transaction. |
| 0000000010000000 | SQLM_LOCKATTR_ALLOW_NEW | Allow new lock requests. |
| 0000000020000000 | SQLM_LOCKATTR_NEW_REQUEST | A new lock requester. |
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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_APPL_LOCKWAIT table function - Get information about locks for which an application is waiting | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock | Basic |
| 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:
- The lock_count monitor element value is incremented 255 times due to new locks being acquired.
- 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 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock | Basic |
| Lock | lock_wait | Basic |
| 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 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock | Lock |
| Lock | lock_wait | Lock |
| 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.
When DB2_AVOID_LOCK_ESCALATION is ON, lock escalation is not performed by the database. Instead, lock escalation is avoided and the lock_escals monitor element is used to count the number of times the database avoids escalation.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Database | event_db | Always collected |
| 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)
- On 64-bit systems,
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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | - | 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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | - | 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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | - | 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 utilities. Locks with holds are not released when transactions are committed.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock | Basic |
| 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 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 |
| 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 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Lock |
| Lock | lock | Lock |
| Lock | lock_wait | Lock |
| 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 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock_wait | Lock |
| 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 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock | Basic |
| Lock | lock_wait | lock_wait |
| 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.
| 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.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Lock |
| Lock | appl_lock_list | Lock |
| Lock | lock | Basic |
| 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 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Lock |
| Lock | appl_lock_list | Lock |
| Lock | lock | Basic |
| Lock | lock_wait | Lock |
| 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.
| 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 |
| INSERT_RANGE | Insert range lock |
| COL_TABLE_SERIALIZE | Column-organized table update/delete serialization lock. |
lock_object_type_id - Internal lock object type waited on monitor element
This element is reserved for internal use only.
Refer to the lock_object_type monitor element for details on the type of object for which a lock is being waited or held.
lock_release_flags - Lock release flags monitor element
Lock release flags.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_LOCKS table function - List all locks in the currently connected database | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock | Basic |
| Lock | lock_wait | Basic |
| 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 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Lock | lock | Basic |
| 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:
| 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 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Basic |
| Application | agent | Basic |
| 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.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Database | event_db | Always collected |
| 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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | - | 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_globalOutside 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.
| 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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Lock, Timestamp |
| Lock | lock_wait | Lock, Timestamp |
| 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.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Lock |
| Application | appl | Lock |
| Lock | appl_lock_list | appl_lock_list |
| 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 |
| Statisitics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Database | event_db | Always collected |
| 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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | - | 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_globalOutside 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.
| 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.
| 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.
| 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.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Database | event_db | Always collected |
| 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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | - | 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_globalOutside of the Db2 pureScale environment, this value is always zero.
locks_held - Locks held monitor element
The number of locks currently held.
| 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 |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
| Lock | db_lock_list | Basic |
| Lock | appl_lock_list | Basic |
| 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.
| 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.
| 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 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 |
| 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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_superclassstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
LOG_EXTRACTION_DISK_SPACE_USED - The number of bytes for extraction logs monitor element
The number of bytes used in the extraction log files for this transaction. DB2_ADVANCED_LOG_SPACE_MGMT=ON, Otherwise 0.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_UNIT_OF_WORK table function - Get unit of work metrics | Always collected |
| MON_GET_UNIT_OF_WORK_DETAILS table function - Get unit of work metrics as an XML document | Always collected |
log_extraction_status - Current status of extraction monitor element
Use the log_extraction_status monitor element to determine the current status of extraction.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
-
Extraction Status
- Values can be n/a (0), Error (1), Active (2) or Recovery (3).
log_extraction_throttle_reason - Reason for extraction throttling monitor element
Use the log_extraction_throttle_reason monitor element to determine the reason for extraction throttling.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
- WHEN THROTTLING OCCURS
- Throttling can occur for any of the following reasons:
- Throttling not enabled
-
- Throttling not available, for example because ALSM has been disabled.
- Throttle reason:
n/a
- Disk full
-
- During runtime and recovery undo, throttle extraction if the extraction log files were to
interfere with the upper limit of the configured number of log files for the database. This rule
ensures that the disk space that would be needed for extraction files is not taken away from the
configured log space for the database. Running out of disk space in the active log path during
regular logging activities could result in an unexpected database shutdown, therefore this condition
overrules any other throttle reasons.
This rule accounts for the logprimary and logsecond database configuration parameters, and ensures that extraction log files never breach this space. There are special cases when Db2 may end up with more primary log files than specified by logprimary (for example, with extraction running), or with less primary log files (for example, when the database is in the process of being activated and log files are being allocated asynchronously). Additionally, the logsecond parameter can change dynamically. The rule accounts for all such cases.
When log spooling is enabled on an HADR standby database, extraction is throttled to protect disk space for log spooling. If the database hadr_spool_limit configuration parameter is set to a fixed value, this rule will ensure that extraction does not take away any disk space needed for log spooling. If hadr_spool_limit is set to AUTOMATIC and disk space is limited, this rule forces extraction to share disk space between itself and log spooling. If hadr_spool_limit is set to -1, then this rule will not throttle extraction.
- Throttle reason:
DISK_FULL
- During runtime and recovery undo, throttle extraction if the extraction log files were to
interfere with the upper limit of the configured number of log files for the database. This rule
ensures that the disk space that would be needed for extraction files is not taken away from the
configured log space for the database. Running out of disk space in the active log path during
regular logging activities could result in an unexpected database shutdown, therefore this condition
overrules any other throttle reasons.
- Distance from active log file
-
- Throttle extraction if the log file being considered for extraction is the current active log file for writing. Extraction only works on closed log files.
- Throttle reason:
CURRENT_ACTIVE_FILE
- Log archiving
-
- Throttle extraction if log archiving has not been enabled for the database, or if the log file being considered for extraction has not yet been archived. Active log files waiting to be archived will always stay in the active log path, and extraction would only duplicate disk space without any added benefit.
- Throttle reason:
LOG_ARCHIVING
- Log space usage
-
- Throttle extraction when consumed active log space is below a calculated threshold. This rule is designed to save system resources and ensures that extraction will only start when the database is close to running out of the configured log space.
- Throttle reason:
DB_LOG_SPACE_USED
- Extraction ratio
-
- Throttle extraction if the sum of the extracted data exceeds the calculated configured log space percentage limit. The purpose of this rule is to prevent extraction for very large transactions, thus potentially duplicating what is found in the active log files.
- Throttle reason:
EXTRACTION_RATIO
- New extraction zone
-
- Do not extract data if a new extraction zone has been detected and any extraction log files before this zone are no longer needed. An extraction zone is the range of log records from start to end that the extraction scan will need to process.
- Throttle reason:
NEW_EXTRACTION_ZONE
- Buffer pool flush needed
-
- Throttle extraction if the currently processed log record has not yet been flushed to the disk. Log records that have not been flushed from the buffer pool will always be needed for recovery purposes, and thus would always need to be extracted. This can be controlled by the database configuration parameters PAGE_AGE_TRGT_MCR and PAGE_AGE_TRGT_GCR
- Throttle reason:
SLOW_BP_FLUSH
- Previous extraction error
-
- Throttle extraction if the currently processed log file needs to be skipped. Upon encountering certain types of errors, the extraction scanner may decide to skip the currently processed log file and restart the scan in the next log file.
- Throttle reason:
SCAN_ERROR
log_hadr_wait_cur - Current logger wait time monitor element
The current logger waiting time on an HADR log shipping request, after the log data has been written to disk locally. This can be viewed as the additional overhead of having the HADR feature on the logging performance of your system. Returns 0 if logger is not waiting for HADR. 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 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, after the log data has been written to disk locally. This can be viewed as the additional overhead of having the HADR feature on the logging performance of your system. 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 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 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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| 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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| 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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| 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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| 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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| 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 Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| 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 the database system.
log_stream_id - Log stream ID monitor element
Identifies log stream being shipped. Stream ID on source database is returned.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information | Always collected |
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 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.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table | table | Basic |
| 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 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.
low_priority_act_total - The total number of low priority activities monitor element
Total count of the number of low priority activities since the last reset.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activity | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache statement eviction | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
Usage
Use this element to determine the number of low priority activities since the last reset.