Foglight for DB2 (Cartridge) 7.2.0


Metric	Description
DB_NAME	The name of the monitored database.
DBPARTITIONNUM	The database partition from which the data was retrieved for this row.
AGENT_ID	The unique ID for the 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 is the same on every partition where the application may make a secondary connection.
APPL_CON_TIME	The date and time that an application started a connection request.
APPL_NAME	The name of the application running at the client.
APPL_ID	This ID is generated when the application connects to the database. It is unique across the network. There are different formats for the application ID, which depend on the communication protocol between the client and the server machine on which the database manager resides.
APPL_STATUS	The status of the application. This element can help you diagnose potential application problems. Values: BACKUP COMMIT_ACT COMP CONNECTED CONNECTPEND CREATE_DB DECOUPLED DISCONNECTPEND INTR IOERROR_WAIT LOAD LOCKWAIT QUIESCE_TABLESPACE RECOMP REMOTE_RQST RESTART RESTORE ROLLBACK_ACT ROLLBACK_TO_SAVEPOINT TEND THABRT THCOMT TPREP UNLOAD UOWEXEC UOWWAIT WAITFOR_REMOTE
NUM_ASSOC_AGENTS	The number of subagents associated with an application.
COORD_NODE_NUM	In a multi-node system, the node number of the node where the application is connected or attached to the instance.
ACTIVE_TIME	The elapsed time (in seconds) spent processing an SQL request on a host database server.
EXECUTION_ID	The ID that the user specified when logging in to the operating system. This ID is distinct from AUTH_ID, which the user specifies when connecting to the database.
AUTHORITY_LVL	This interface returns a text identifier based on the database authorities defined in sql.h and their source. Possible “authority” values: BINDADD CONNECT CREATE_EXT_RT CREATE_NOT_FENC CREATETAB DBADM IMPLICIT_SCHEMA LOAD LIBADM QUIESCE_CONN SECADM SYSADM SYSCTRL SYSMAINT SYSMON SYSQUIESCE Possible “source” values: USER — authority granted to the user or to a role granted to the user GROUP — authority granted to a group to which the user belongs or to a role granted to the group to which the user belongs
CLIENT_PID	The process ID of the client application that made the connection to the database.
COORD_AGENT_PID	The ID of the coordinator agent for the application.
STATUS_CHANGE_TIME	The date and time the application entered its status.
CLIENT_PLATFORM	This interface returns a text identifier based on the client platform definition in sqlmon.h.
CLIENT_PROTOCOL	This interface returns a text identifier based on the definition in sqlmon.h. Values: CPIC LOCAL NETBIOS NPIPE TCPIP (for DB2® UDB) TCPIP4 TCPIP6
TERRITORY_CODE	The territory code of the database for which the monitor data is collected.
CODEPAGE_ID	The code page at the partition where the monitored application started.
PRIMARY_AUTH_ID	The authorization ID of the user who invoked the application that is being monitored.
SESSION_AUTH_ID	The current authorization ID for the session used by this application.
CLIENT_NNAME	Deprecated by IBM.
CLIENT_DB_ALIAS	The alias of the database, provided by the application, to connect to the database.
DB_PATH	The full path of the location where the database is stored on the monitored system.
STMT_TEXT	The SQL statement text.
FOREGROUND_BACKGROUND_ TYPE	The foreground or background status of the application.
ROWS_READ	This is the number of rows read from the table.
ROWS_WRITTEN	The number of rows changed (inserted, deleted, or updated) in the table.
APPL_IDLE_TIME	The amount of time (in seconds) that has passed since an application has issued any requests to the server. Includes applications that have not terminated a transaction (for example, not issued a commit or rollback).
POOL_DATA_L_READS	The number of data pages that have been requested from the buffer pool (logical) for regular and large tablespaces.
POOL_INDEX_L_READS	The number of index pages that have been requested from the buffer pool (logical) for regular and large tablespaces.
POOL_INDEX_P_READS	The number of index pages read in from the tablespace containers (physical) for regular and large tablespaces.
POOL_TEMP_DATA_L_READS	The number of data pages that have been requested from the buffer pool (logical) for temporary tablespaces.
POOL_TEMP_DATA_P_READS	The number of data pages read in from the tablespace containers (physical) for temporary tablespaces.
POOL_TEMP_INDEX_L_READS	The number of index pages that have been requested from the buffer pool (logical) for temporary tablespaces.
POOL_TEMP_INDEX_P_READS	The number of index pages read in from the tablespace containers (physical) for temporary tablespaces.
LAST_RESET	The date and time when the monitoring counters were reset.
PACKAGE_CACHE_HIT_RATIO	A ratio that indicates how well the package cache is helping to avoid the reload of packages and sections for static SQL from the system catalogs and the recompilation of dynamic SQL statements. The indicator is calculated using the formula: (1-(db.pkg_cache_inserts/db.pkg_cache_lookups))*100

Agents Summary Collection

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


Metric	Description
ACTIVE	The number of active agents.
ACTIVE_Rate	The number of active agents per second (average active sessions).
BACKGROUND_PROCESSES	The sum of background application agents.
BACKUP_AGENTS	Indicates that the application is performing a backup of the database.
BALANCE_AVERAGE_ACTIVE_ SESSIONS	The balance of average active agents across the database partitions. If most of the activity is on one partition, for example, the activity is not balanced. If most of the activity is spread equally across the partitions, it is balanced.
COMMIT_ACT_AGENTS	Indicates that the unit of work is committing its database changes.
COMP_AGENTS	Indicates that the database manager is compiling an SQL statement or precompiling a plan on behalf of the application.
CONNECTPEND_AGENTS	Indicates that the application has initiated a database connection, but the request has not yet completed.
CREATE_DB_AGENTS	Indicates that the agent has initiated a request to create a database, but that request has not yet completed.
DB_NAME	The name of the monitored database.
DBPARTITIONNUM	The database partition from which the data was retrieved for this row.
DECOUPLED_AGENTS	Indicates that there are no agents currently associated with the application. This is a normal state. When the Connection Concentrator is enabled, there is no dedicated coordinator agent, so an application can be decoupled on the coordinator partition. In non-concentrator environments, an application cannot be decoupled on the coordinator partition, because there is always a dedicated coordinator agent.
DISCONNECTPEND_AGENTS	Indicates that the application has initiated disconnection from the database, but the command has not yet completed running. The application may not have explicitly run the database disconnect command. The database manager disconnects from a database if the application ends without disconnecting.
FOREGROUND_PROCESSES	The sum of foreground application agents.
INACTIVE	Indicates that the application is in one of three states: connected, uowwait, or decoupled.
INTR_AGENTS	Indicates that an interrupt of a request is in progress.
IOERROR_WAIT_AGENTS	Indicates that the application has detected an I/O error and is attempting to disable a particular tablespace. The application has to wait for all other active transactions on the tablespace to complete before it can disable the tablespace.
LOAD_AGENTS	Indicates that the application is performing a “fast load” of data into the database.
LOCKWAIT_AGENTS	Indicates that the unit of work is waiting for a lock. After the lock is granted, the status is restored to its previous value.
QUIESCE_TABLESPACE_AGENTS	Indicates that the application is performing a quiesce tablespace request.
RECOMP_AGENTS	Indicates that the database manager is recompiling (that is, rebinding) a plan on behalf of the application.
REMOTE_RQST_AGENTS	Indicates that the application is waiting for results from a federated data source.
RESTART_AGENTS	Indicates that the application is restarting a database in order to perform crash recovery.
RESTORE_AGENTS	Indicates that the application is restoring a backup image to the database.
ROLLBACK_ACT_AGENTS	Indicates that the unit of work is rolling back its database changes.
ROLLBACK_TO_SAVEPOINT_AGENTS	Indicates that the application is rolling back to a savepoint.
TEND_AGENTS	Indicates that the unit of work is part of a global transaction that has ended, but has not yet entered the prepared phase of the two-phase commit protocol.
THABRT_AGENTS	Indicates that the unit of work is part of a global transaction that has been heuristically rolled back.
THCOMT_AGENTS	Indicates that the unit of work is part of a global transaction that has been heuristically committed.
TOTAL_AGENTS	The total number of agents of all types.
TOTAL_AGENTS_Rate	The rate of total agents per second.
UNLOAD_AGENTS	Indicates that the application is performing a “fast unload” of data from the database.
UOWEXEC_AGENTS	Indicates that the database manager is running requests on behalf of the unit of work.
UOWWAIT_AGENTS	Indicates that the database manager is waiting on behalf of the unit of work in the application. This status typically means that the system is running in the application's code.
WAITFOR_REMOTE_AGENTS	Indicates that the application is waiting for a response from a remote partition in a partitioned database instance.
OTHER	The agents not identified by one of the agent types we show at the workload agents.

Current Locks Wait Collection

The following table provides a list of the Current 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 is the same on every partition where the application may make a secondary connection.

The application handle can be used to uniquely identify an active application (application handle is synonymous with agent ID). 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 waited (locked) agent.

LOCK_MODE

The type of lock that is being held.

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

This element indicates one of the following, 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 help in determining the source of the contention for resources.

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

•

•

•

•

•

•

•

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

•

•

•

•

•

•

•

•

INPLACE_REORG_LOCK INTERNAL_LOCK

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

Agent ID of the blocker that holds the object or the application handle of the agent holding a lock for which this application is waiting. The lock monitor group must be turned on to obtain this information.

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

If this element is 0 (zero) and the application is waiting for a lock, indicates that the lock is held by an in-doubt transaction.

APPL_ID_HOLDING_LK

The application ID of the application that is holding a lock on the object that 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 object that is locked by another application.

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

LOCK_MODE_REQUESTED

The lock mode 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 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.

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 help 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 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 help in determining the source of the contention for resources.

TBSP_NAME

The name of a tablespace.

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

It is equivalent to the TBSPACE column in the database catalog table SYSCAT.TABLESPACES.

•

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 element 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 '+' sign:

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

LOCK_RELEASE_FLAGS

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

Database Bufferpools Summary PS Collection

Tracks how members use group buffer pools (GBP) and local buffer pools (LBP).


Metric	Description
INSTANCE_NAME	The name of the monitored instance.
DB_NAME	The name of the monitored database.
MEMBER	The name of the database member from which the data was retrieved.
GBP_EFFICENCY	Percentage of pages read from the GBP after the page found in the LBP was found to be invalid. Indicates the amount of time a read from disk was avoided thanks to the GBP.
GBP_HIT_RATIO	pureScale only — Percentage of times a requested page was found in the GBP out of all times it was requested. If a page is found in the GBP, it saves the database the time to look for it in the LBP.
GBP_INVALID_PAGES	Number of GBP data page read attempts due to an LBP page being present but marked invalid.
GBP_LOGICAL_READS	pureScale only — Number of times a GBP-dependent page was attempted to be read from the GBP because the page was either invalid or not present in the LBP.
GBP_PHYSICAL_READS	Number of times a GBP-dependent page was read into the LBP from disk because it was not found in the GBP.
LBP_HIT_RATIO	Percentage of times a requested page was found in the LBP (either valid or invalid) out of all times it was requested.
LBP_LOGICAL_READS	Number of pages (Data, Index, XDA) that were found in the LBP. Value includes both valid and invalid pages.
LBP_PHYSICAL_READS	Number of pages prefetched from the GBP to the LBP.
TOTAL_HIT_RATIO	Percentage of times a requested page was found in the buffer pool out of all times it was requested. If a page is found in the buffer pool, it saves the database the time to look for it in the physical storage.
TOTAL_LOGICAL_READS	Total logical reads (index, XDA and data) requested from the buffer pool.
TOTAL_PHYSICAL_READS	Total pages (index, XDA and data) read from physical storage into the buffer pool.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Foglight for DB2 (Cartridge) 7.2.0 - User Guide

Current Agents Details Collection

Agents Summary Collection

Current Locks Wait Collection

Database Bufferpools Summary PS Collection