Chat now with support
Chat with Support

Foglight for DB2 (Cartridge) 5.9.3.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 Top SQLs Full Text Collection Top SQLs Text 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 Instance Storage Collection

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

OVERALL_FS_TOTAL_SIZE

The overall total space size (MB) of file systems over which the DB2 instance is spread.

OVERALL_FS_USED_SIZE

The amount of space (MB) used on the file systems over which the DB2 instance is spread (not just what DB2 is using; this metric represents the overall usage).

OVERALL_INSTANCE_FS_UTILIZATION

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

OVERALL_FREE_SIZE

This element shows the amount of free space (MB) per instance on a file system.

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.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating