Chat now with support
Chat with Support

Foglight for DB2 (Cartridge) 6.1.0.10 - User Guide

Introduction to Foglight for DB2 LUW Using Reference
Dashboards and Views Collections and Metrics
CF Locks Collection CF Memory Pools Collection CF Resource Usage Collection Current Agents Details Collection Agents Summary Collection Current Locks Wait Collection Database Bufferpools Summary PS Collection Database Configuration Collection Database Tablespaces Collection Database Tablespaces Summary Collection Database Usability Collection Database Parameters Collection Database Instance Parameters Collection Database Instance Registry Variables Collection Database Tablespaces BP Collection FCM Activity Collection FS DB Storage Collection FS DB Storage Summary Collection FS Instance Storage Collection FS Storage Collection FS Storage Summary Collection General Activity Collection HADR Activity Collection Host Properties Collection OS General Collection Instance Activity Collection Instance General Collection Instance Nodes Collection Instance Usability Collection IO Activity Collection License Details Collection Log Activity Collection Log File Collection Log Message Collection Messages Count Collection Monitor Switches Collection OS DB2 General Process Collection Partition Tablespaces Summary Collection Partition Usability Collection pureScale Alerts Collection Top SQLs Collection Query Agents Details Collection Database Partition Activity Collection DB2 Partition Subcategories Wait Collection History Locks Wait Collection History Locks Summary Collection Instance Memory Collection Instance Memory Pool Collection Instance Memory Summary Collection Instance Memory Total Collection Database Memory Collection Database Memory Pool Collection Database Memory Pool DB Collection Database Memory Summary Collection Database Memory Total Collection Database Buffer Pools Collection Database Tables Collection Database Partition Backup Collection Database Tables Global Collection Database Applications Collection

DB2 Partition Subcategories Wait Collection

The following table provides a list of the DB2 Partition Subcategories Time-spent Monitor Element metrics that are collected, and a description for each.

Starting from DB2 v9.7.0.1, DB2 uses time-spent monitor elements to track how time is spent in the system. These elements show where time is spent waiting, or performing various types of processing. Time-spent monitor elements get rolled up into more general monitor elements. Elements with the same PARENT_METRIC_NAME are rolled up into that parent.

 
Metric
Description

DATABASE_NAME

The name of the monitored database

MEMBER

The database partition or member number.

METRIC_NAME

The event name to which the TOTAL_TIME metric value refers.

PARENT_METRIC_NAME

The identifier of the parent for the TOTAL_TIME metric, whose value contains the TOTAL_TIME value as a subset.

TOTAL_TIME

The total time value (in seconds), which corresponds to METRIC_NAME.

COUNT

The number of occurrences of this type of event.

COUNT_RATE

The rate of the number of occurrences of this type of event.

TOTAL_TIME_RATE

The rate (per second) of the total time spent.

CATEGORY

The category of the event. Values:

I/O wait - The amount of time spent waiting for IO. Subcategories provide additional information.

Other wait - The aggregate of several events that do not typically have a high impact on performance. Subcategories provide additional information.

WLM wait - The amount of time spent waiting for the WLM queue.

pureScale wait - The amount of time spent waiting for various functions within the cluster. Subcategories provide additional information.

Idle - The amount of time the client waited that was not spent within DB2 database server.

Network wait - The amount of time spent waiting for network protocols. Subcategories provide additional information.

Lock wait - The amount of time spent waiting for locks.

Log wait - The amount of time spent waiting for the log. Subcategories provide additional information.

Transaction - The amount of time spent on the commit or rollback of the transaction.

Compile - The amount of time spent doing explicit or implicit compiles.

Memory wait - The amount of time spent waiting for an internal latch.

Administration - The amount of time spent processing administration tasks. Subcategories provide additional information.

Execute - The amount of time spent executing the request. Subcategories provide additional information.

FCM wait - The amount of time spent waiting for the FCM communications layer. This is only applicable on the partition level. Subcategories provide additional information.

IPC - The amount of time spent waiting for IPC channels.

History Locks Wait Collection

The following table provides a list of the History Locks Wait metrics that are collected, and a description for each.

 
Metric
Description

DB_NAME

The name of the monitored database.

DBPARTITIONNUM

The database partition from which the data was retrieved for this row.

LOCK_NAME

The ID of the lock.

AGENT_ID

A system-wide unique ID for the locked wait application. On a single-partitioned database, this ID consists of a 16-bit counter. On a multi-partitioned database, this ID consists of the coordinating partition number concatenated with a 16-bit counter. In addition, this ID will be the same on every partition where the application may make a secondary connection.

The application handle (synonymous with agent ID) can be used to uniquely identify an active application. It can also be used as input to the FORCE APPLICATION command or API. On multi-node systems, this command can be issued from any node where the application has a connection. Its effect is global.

STMT_TEXT

The full statement text of the locked agent.

LOCK_MODE

The type of lock being held.

This mode can assist in determining the source of the contention for resources.

One of the following is indicated, depending on the type of monitoring 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).

 

Mode Types:

NON - No Lock

IS - Intention Share Lock

IX - Intention Exclusive Lock

S - Share Lock

SIX - Share with Intention Exclusive Lock

X - Exclusive Lock

IN - Intent None

Z - Super Exclusive Lock

U - Update Lock

NS - Next Key Share Lock

NX - Next Key Exclusive Lock

W - Weak Exclusive Lock

NW - Next Key Weak Exclusive Lock

LOCK_OBJECT_TYPE

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

This element can assist in determining the source of the contention for resources.

The object type IDs are defined in sqlmon.h. The object may be one of the following types:

Tablespace

Table

Buffer pool

Block

Record (or row)

Data partition

Internal (another type of lock held internally by the database manager)

Automatic resize

Automatic storage

Values:

AUTORESIZE_LOCK

AUTOSTORAGE_LOCK

BLOCK_LOCK

EOT_LOCK

INPLACE_REORG_LOCK INTERNAL_LOCK

INTERNALB_LOCK

INTERNALC_LOCK

INTERNALJ_LOCK

INTERNALL_LOCK

INTERNALO_LOCK

INTERNALQ_LOCK

INTERNALP_LOCK

INTERNALS_LOCK

INTERNALT_LOCK

INTERNALV_LOCK

KEYVALUE_LOCK

ROW_LOCK

SYSBOOT_LOCK

TABLE_LOCK

TABLE_PART_LOCK

TABLESPACE_LOCK

XML_PATH_LOCK

AGENT_ID_HOLDING_LK

The agent ID of the blocker that holds the object or the application handle of the agent that holds a lock for which this application is waiting.

The lock monitor group must be turned on to obtain this information.

This element can assist in determining which applications are in contention for resources.

If the value is 0 (zero) and the application is waiting for a lock, the lock is being held by an in-doubt transaction.

APPL_ID_HOLDING_LK

The application ID of the application that is holding a lock on the object this application is waiting to obtain.

LOCK_WAIT_START_TIME

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

This element can assist in determining the severity of resource contention.

LOCK_MODE_REQUESTED

The lock mode being requested by the application.

Mode Types:

•
NON - No Lock
•
IS - Intention Share Lock
•
IX - Intention Exclusive Lock
•
S - Share Lock
•
SIX - Share with Intention Exclusive Lock
•
X - Exclusive Lock
•
IN - Intent None
•
Z - Super Exclusive Lock
•
U - Update Lock
•
NS - Next Key Share Lock
•
NX - Next Key Exclusive Lock
•
W - Weak Exclusive Lock
•
NW - Next Key Weak Exclusive Lock

LOCK_ESCALATION

Indicates whether a lock request was made as part of a lock escalation.

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

DATA_PARTITION_ID

The ID of the data partition for which a lock occurred.

This element is only applicable to partitioned tables.

A value of -1 represents a lock of the whole table and not a specific partition.

TABNAME

The name of the table.

Along with TABSCHEMA, this element can assist in determining the source of the contention for resources.

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

TABSCHEMA

The schema of the table.

Along with TABNAME, this element can assist in determining the source of the contention for resources.

TBSP_NAME

The name of a tablespace.

This element can assist in determining the source of the contention for resources.

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

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

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

This is not returned for a table lock held on a partitioned table.

LOCK_ATTRIBUTES

The lock attributes of the lock wait. If there are no locks, the text identifier is NONE. Otherwise, it is any combination of the following, separated by a '+':

•
ALLOW_NEW
•
DELETE_IN_BLOCK
•
ESCALATED
•
INSERT
•
NEW_REQUEST
•
RR
•
RR_IN_BLOCK
•
UPDATE_DELETE
•
WAIT_FOR_AVAIL

LOCK_CURRENT_MODE

During a lock conversion operation, the type of lock held before the conversion is completed. For example, 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, that would require a conversion. LOCK_CURRENT_MODE is assigned a value of S or V while the lock waits to be converted to an X lock.

LOCK_RELEASE_FLAGS

Each release flag is based on a bit flag value defined in sqlmon.h. The following are possible release flag settings (API constant description):

SQLM_LOCKRELFLAGS_SQLCOMPILER - Locks by SQL compiler

SQLM_LOCKRELFLAGS_UNTRACKED - Non-unique, untracked locks

SUBSECTION_NUMBER

Identifies the subsection associated with the returned information.

This number relates to the subsection number in the access plan that can be obtained with db2expln.

LOCK_DURATION

The duration of the lock wait.

History Locks Summary Collection

The following table provides a list of the History Locks Summary metrics that are collected, and a description for each.

 
Metric
Description

DB_NAME

The name of the monitored database.

DBPARTITIONNUM

The database partition from which the data was retrieved.

LONGEST_DURATION

A metric for internal use.

NUM_OF_EXCEEDING_LOCKS

A metric for internal use.

Instance Memory Collection

The following table provides a list of the Instance Memory metrics that are collected, and a description for each.

 
Metric
Description

DBPARTITIONNUM

The database partition from which the data was retrieved for this row.

INSTANCE_NAME

The name of the monitored instance.

POOL_ID

The pool ID:

Values:

•
FCMBP
•
MONITOR
•
OTHER

POOL_CUR_SIZE

The average allocated size (in KB) of a memory pool.

POOL_CONFIG_SIZE

The internally configured size (in KB) of a memory pool in the DB2 database system.

POOL_UTILIZATION

The utilization of the allocated memory from the configured memory.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating