Chat now with support
Chat with Support

Foglight for DB2 (Cartridge) 5.9.7.20 - User Guide

Introduction to Foglight for DB2 LUW Using Foglight for DB2 LUW 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

FS Storage Collection

The following table provides a list of the FS Storage 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.

FS_ID

This element shows the unique identification number provided by the operating system for a file system pointed to by a storage path.

DB_STORAGE_PATH

This element shows the full path of a location used by the database for placing automatic storage tablespaces. There can be 0 or more storage paths associated with a database.

FS_TOTAL_SIZE

This element shows the capacity (MB) of a file system pointed to by a storage path.

FS_USED_SIZE

This element shows the amount of space (MB) already used on a file system pointed to by a storage path.

STO_PATH_FREE_SIZE

This element shows the amount of free space (MB) available on a file system pointed to by a storage path. If multiple storage paths point to the same file system, the free size is not divided among them.

FS_UTILIZATION

The utilization (%) of the file system over which the DB2 instance is spread.

FS Storage Summary Collection

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

OVERALL_FS_TOTAL_SIZE

The total capacity (MB) per partition of the file system to which the storage path points.

OVERALL_FS_USED_SIZE

This element shows the amount of space (MB) per partition already used on a file system pointed to by a storage path.

OVERALL_STO_PATH_FREE_SIZE

This element shows the amount of free space (MB) per partition available on a file system pointed to by a storage path. If multiple storage paths point to the same file system, the free size is not divided among them.

OVERALL_FS_UTILIZATION

This element shows the amount of free space (MB) per database available on a file system pointed to by a storage path. If multiple storage paths point to the same file system, the free size is not divided among them.

General Activity Collection

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

ACTIVE_CONNECTIONS

The number of applications that are currently connected to the database, and for which the database manager is processing a request.

ACTIVE_CONNECTIONS_RATE

The number of active connections per second.

ACTIVE_SORTS

The number of sorts in the database that have a sort heap allocated.

APPL_ID_OLDEST_XACT

The application ID of the application with the oldest transaction.

APPL_SECTION_INSERTS

Counts the number of times a copy of a SQL executable section was not available in the application SQL work area and had to be inserted.

APPL_SECTION_LOOKUPS

Each agent has access to a unique SQL work area, where the working copy of any executable section is kept. This metric counts the total number of times the SQL work area was accessed by agents for an application. You can use this element with APPL_SECTION_INSERTS to tune the size of the SQL work area heap.

AVERAGE_LOCK_WAITER_WAIT_TIME

The average amount of time (in seconds) for a lock wait in the database.

CAT_CACHE_INSERTS

The number of times the system attempted to insert table descriptor or authorization information into the catalog cache.

CAT_CACHE_LOOKUPS

The number of times that the catalog cache was referenced to obtain table descriptor or authorization information.

This includes both successful and unsuccessful attempts to access the catalog cache.

CAT_CACHE_OVERFLOWS

The number of times that the catalog cache overflowed its allocated memory.

You can use this element with CAT_CACHE_SIZE_TOP to determine if the size of the catalog cache should be increased to avoid overflowing.

CAT_CACHE_SIZE_TOP

The largest size (in bytes) reached by the catalog cache including overflows, if they occurred.

CATALOG_CACHE_HIT_RATIO

A percentage that indicates how well the catalog cache is helping to avoid actual accesses to insert table descriptor or authorization information into the catalog cache, compared to the number of times the catalog cache was referenced to obtain table descriptor information or authorization information.

A high ratio indicates that it is successful in avoiding actual disk I/O accesses.

The indicator is calculated using the formula:

(1-(CAT_CACHE_INSERTS/CAT_CACHE_LOOKUPS))*100

CATALOG_PARTITION

The main database partition in which the database catalog tables are stored.

CLIENT_PERCENT_ACTIVE

The percentage of active client applications.

COMMIT_SQL_STMTS

The total number of SQL COMMIT statements attempted.

CONNECTIONS

The total number of connections that took place during the specified time range.

CONNECTIONS_HWM

The highest number of simultaneous connections to the database since the database was enabled.

CONNECTIONS_RATE

The number of connections that took place, per second, during the specified time range.

DB_CONN_TIME

The date and time of the connection to the database (at the database level, the first connection to the database), or when the enable database command was issued.

DB_LOCATION

The database location.

Values: LOCAL or REMOTE

At the database level, this is always LOCAL.

DB_NAME

The name of the monitored database.

DB_PATH

The full path to the database location on the monitored system. At the database level, it is the path of the coordinator partition of the database (most of the time, 0).

DB_STATUS

The status of the database. This interface returns a text identifier, based on the definition in sqlmon.h, which is one of the following values:

ACTIVE

QUIESCE_PEND

QUIESCED

ROLLFWD

DBPARTITIONNUM

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

DEADLOCKS

The total number of deadlocks that have occurred in the database for the required period. A deadlock occurs when there is a cyclical dependency between two or more threads or processes that are contending for the same set of resources within DB2. That is, each task has a lock on a resource that the other tasks are trying to lock.

DYNAMIC_SQL_STMTS

The number of dynamic SQL statements attempted.

DYNAMIC_SQL_STMTS_RATE

The number of dynamic SQL statements that were run, per second, during the specified time range.

ELAPSED_EXEC_TIME_S

The sum of the host execution times (in seconds) for all the statements that were run for a particular database. This element does not include the network time elapsed between DB2 Connect™ and the host database server.

ELAPSED_EXEC_TIME_S_RATE

The sum of the host elapsed time per second for all the statements that were run for a particular database.

HASH_JOIN_OVERFLOWS

The number of times that hash join data exceeded the available sort heap space.

INT_COMMITS

The total number of commits initiated internally by the database manager.

INT_ROLLBACKS

The total number of rollbacks initiated internally by the database manager.

LAST_BACKUP

The date and time that the latest database backup was completed.

LAST_RESET

The date and time that the monitor counters were last reset.

If the counters have never been reset, the value of the element is zero.

LOCK_LIST_IN_USE_KB

The total amount of lock list memory (in KB) that is in use.

LOCK_TIMEOUTS

The number of times that a request to lock an object timed-out instead of being granted.

LOCK_WAIT_TIME_S

The total amount of time (in seconds) that all applications were waiting for a lock within this database.

LOCK_WAIT_TIME_S_RATE

The rate of time at which all applications were waiting for a lock within this database.

LOCK_WAITS

The total number of times that applications waited for locks within this database.

LOCKS_WAITING

The number of agents waiting on a lock.

NUM_ASSOC_AGENTS

The number of subagents for all applications.

PACKAGE_CACHE_HIT_RATIO

A percentage that indicates how well the package cache is performing. It is the total number of times that a requested section was not available for use and had to be loaded into the package cache compared to what was already loaded into the cache. Includes any implicit prepares performed by the system.

 

This indicator is calculated using the formula:

((1 - (PKG_CACHE_INSERTS/ PKG_CACHE_LOOKUPS)) * 100)

PKG_CACHE_INSERTS

The total number of times that a requested section was not available for use and had to be loaded into the package cache. This includes any implicit prepares performed by the system.

PKG_CACHE_LOOKUPS

The number of times that an application looked for a section or package in the package cache. At the database level, this is the overall number of references since the database was started, or since monitor data was reset.

PKG_CACHE_NUM_OVERFLOWS

The number of times that the package cache overflowed its allocated memory.

You can use this element with PKG_CACHE_SIZE_TOP to determine if the size of the package cache should be increased to avoid overflowing.

PKG_CACHE_SIZE_TOP

The largest size reached by the package cache.

If the package cache experienced overflow, then this element contains the largest size reached by the package cache during the overflow.

POST_SHRTHRESHOLD_SORTS

The total number of sorts throttled back by the sort memory throttling algorithm. Throttled sorts are sorts that are granted less memory than requested by the sort memory manager. A sort is throttled back when the memory allocation for sorts is close to the limit set by the SHEAPTHRES_SHR database configuration parameter. Throttling significantly reduces the number SHEAPTHRES_SHR overflows in a system that is improperly configured. The value of this element only includes sorts that are using memory allocated from the shared sort heap.

ROLLBACK_SQL_STMTS

The total number of SQL ROLLBACK statements attempted. A rollback can result from an application request, a deadlock, or an error. This element only counts the rollback statements issued from applications. This metric can be used to help with determining the level of activity in the database and the amount of conflict between applications on the database.

SERVER_PLATFORM

The operating system running the database server.

SORT_HEAP_ALLOCATED

The total number of allocated pages of sort heap space for all sorts at the level chosen and at the time the snapshot was taken.

SORT_OVERFLOW_PERCENT

The percentage, within the total number of sorts, of sorts that ran out of sort heap and may have required disk space for temporary storage.

If the percentage is high, consider adjusting the database configuration by increasing the sort heap.

SORT_OVERFLOWS

The total number of sorts that ran out of sort heap and may have required disk space for temporary storage. When a sort overflows, additional overhead is incurred because the sort requires a merge phase and might require more I/O if data needs to be written to disk.

SORT_SHRHEAP_ALLOCATED

The total amount of shared sort memory allocated in the database.

If this value is often much higher or lower than the current shared sort memory threshold, the threshold should probably be adjusted.

SORT_SHRHEAP_TOP

The database-wide shared sort memory high-water mark (in 4k pages).

STATIC_SQL_STMTS

The number of static SQL statements attempted.

STATIC_SQL_STMTS_RATE

The number, per second, of static SQL statements that were run during the specified time range.

TOTAL_SORT_TIME_S

The total elapsed time (in seconds) for all sorts that were run. This metric can be used with TOTAL_SORTS to calculate the average sort time, which can indicate whether sorting is a performance issue.

TOTAL_SORTS

The total number of sorts that have been run. This metric can be used with SORT_OVERFLOWS to calculate the percentage of sorts that need more heap space. You can also use it with TOTAL_SORT_TIME to calculate the average sort time. If the amount of sort overflows is small relative to the total sorts, then increasing the sort heap size may have little impact on performance, unless it is increased substantially.

TOTAL_SUBAGENT_CONS

The number of connections made by a subagent to the database at the node.

XACT

The total number of units of work, calculated by taking the sum of INT_COMMIT, INT_ROLLBACK, COMMIT_SQL_STMTS and ROLLBACK_SQL_STMTS.

XACT_RATE

The number, per second, of transactions that were carried out during the specified time range.

HADR Activity Collection

The following table provides a list of the HADR Activity 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.

HADR_ROLE

The current HADR role of the database. The data type of this element is integer. The value for this element is one of the following constants:

SQLM_HADR_ROLE_STANDARD (value = 0): The database is not a HADR database.

SQLM_HADR_ROLE_PRIMARY (value = 1): The database is the primary HADR database.

SQLM_HADR_ROLE_STANDBY (value = 2): The database is the standby HADR database.

HADR_STATE

The current HADR state of the database. The data type of this element is integer. This element should be ignored if the HADR role of the database is standard. If the database has a HADR role of primary or standby, the value for this element is one of the following constants:

SQLM_HADR_STATE_DISCONNECTED (value = 0): The database is not connected to its partner database.

SQLM_HADR_STATE_LOC_CATCHUP (value = 1): The database is doing local catch-up.

SQLM_HADR_STATE_REM_CATCH_PEND (value = 2): The database is waiting to connect to its partner to do remote catch-up.

SQLM_HADR_STATE_REM_CATCHUP (value = 3): The database is doing remote catch-up.

SQLM_HADR_STATE_PEER (value = 4): The primary and standby databases are connected and are in peer state.

HADR_SYNCMODE

The current HADR synchronization mode of the database. The data type of this element is integer. This element should be ignored if the HADR role of the database is standard. If the database has a HADR role of primary or standby, the value for this element is one of the following constants:

SQLM_HADR_SYNCMODE_SYNC: Sync mode

SQLM_HADR_SYNCMODE_NEARSYNC: Nearsync mode

SQLM_HADR_SYNCMODE_ASYNC: Async mode

HADR_CONNECT_STATUS

This element should be ignored if the HADR role of the database is standard. If the database has a HADR role of primary or standby, the value for this element is one of the following constants:

SQLM_HADR_CONN_CONNECTED (value = 0): The database is connected to its partner node.

SQLM_HADR_CONN_DISCONNECTED (value = 1): The database is not connected to its partner node.

SQLM_HADR_CONN_CONGESTED (value = 2 ): The database is connected to its partner node, but the connection is congested. A connection is congested when the TCP/IP socket connection between the primary and standby pair is still alive, but one end cannot send to the other end. For example, the receiving end is not receiving from the socket connection, resulting in a full TCP/IP send space. The reasons for network connection congestion include the following:

HADR_CONNECT_TIME

Shows one of the following:

HADR connection time

HADR congestion time

HADR disconnection time

This element should be ignored if the HADR role of the database is standard. If the database HADR role is primary or standby, the meaning of this element depends on the value of the HADR_CONNECT_STATUS element:

If the value of the HADR_CONNECT_STATUS element is SQLM_HADR_CONN_CONNECTED, then this element shows connection time.

If the value of the HADR_CONNECT_STATUS element is SQLM_HADR_CONN_CONGESTED, then this element shows the time when congestion began.

If the value of the HADR_CONNECT_STATUS element is SQLM_HADR_CONN_DISCONNECTED, then this element shows disconnection time.

If there has been no connection since the HADR engine dispatchable unit (EDU) started, connection status is reported as Disconnected and HADR EDU startup time is used for the disconnection time. Since HADR connect and disconnect events are relatively infrequent, the time is collected and reported even if the DFT_MON_TIMESTAMP switch is off.

HADR_HEARTBEAT

The number of missed heartbeats on the HADR connection. If the HADR role of the database is primary or standby, this element indicates the health of the HADR connection. A heartbeat is a message sent from the other HADR database at regular intervals. If the value for this element is zero, no heartbeats have been missed and the connection is healthy. The higher the value, the worse the condition of the connection.

A HADR database expects at least one heartbeat message from the other database in each quarter of the time interval defined in the HADR_TIMEOUT database configuration parameter or in 30 seconds, whichever is shorter. For example, if the HADR_TIMEOUT value is 80 (seconds), then the HADR database expects at least one heartbeat message from the other database every 20 seconds.

 

Use this element to determine the health of the HADR connection. Use the HADR_ROLE monitor element to determine the HADR role of the database.

HADR_LOCAL_HOST

The local HADR host name. The value is displayed as a host name string or an IP address string such as 1.2.3.4. This element should be ignored if the HADR role of the database is standard.

HADR_LOCAL_SERVICE

The local HADR TCP service. This value is displayed as a service name string or a port number string. This element should be ignored if the HADR role of the database is standard.

HADR_REMOTE_HOST

The remote HADR host name. The value is displayed as a host name string or an IP address string such as 1.2.3.4. This element should be ignored if the HADR role of the database is standard.

HADR_REMOTE_SERVICE

The remote HADR TCP service. This value is displayed as a service name string or a port number string. This element should be ignored if the HADR role of the database is standard.

HADR_REMOTE_INSTANCE

The remote HADR instance name. This element should be ignored if the HADR role of the database is standard.

HADR_TIMEOUT

The number of seconds it takes for a HADR database server to consider a communication attempt to have failed. For an attempt to fail, a HADR database server must not receive a reply message from its partner within the number of seconds specified in this element. This element should be ignored if the HADR role of the database is standard.

HADR_PRIMARY_LOG_FILE

The name of the current log file on the primary HADR database. This element should be ignored if the HADR role of the database is standard.

HADR_PRIMARY_LOG_PAGE

The page number in the current log file indicating the current log position on the primary HADR database. The page number is relative to the log file. For example, page zero is the start of the file. This element should be ignored if the HADR role of the database is standard.

HADR_PRIMARY_LOG_LSN

The current log position of the primary HADR database. Log sequence number (LSN) is a byte offset in the log stream of the database. This element should be ignored if the HADR role of the database is standard.

HADR_STANDBY_LOG_FILE

The name of the current log file on the standby HADR database. This element should be ignored if the HADR role of the database is standard.

HADR_STANDBY_LOG_PAGE

The page number in the current log file indicating the current log position on the standby HADR database. The page number is relative to the log file. For example, page zero is the start of the file. This element should be ignored if the HADR role of the database is standard.

HADR_STANDBY_LOG_LSN

The current log position of the standby HADR database. Log sequence number (LSN) is a byte offset in the log stream of the database. This element should be ignored if the HADR role of the database is standard.

HADR_LOG_GAP_KB

The size (in KB) of the gap between the primary LSN and the standby LSN.

When a log file is truncated, the LSN in the next log file starts as if log file truncation took place, as a result of that creating an LSN hole that contains no log data. Such holes can lead to a situation where the log gap does not reflect the actual log difference between the primary and standby LSN.

HADR_LOG_GAP_KB_RATE

The running log gap rate (in KB/s) between the primary and the standby database.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating