Chat now with support
Chat with Support

Foglight for DB2 (Cartridge) 7.2.0 - 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

Current Agents Details Collection

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

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.

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.

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:

 

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:

AGENT_ID_HOLDING_LK

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.

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:

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

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.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating