Foglight for DB2 (Cartridge) 7.1.0 - Getting Started Guide


Metric	Description
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.


Metric	Description
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.


Metric	Description
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. NOTE: When a transaction is not holding up logging, or the oldest transaction does not have an application ID (for example, an in-doubt transaction or inactive transaction), the application's ID is not available.
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. NOTE: This value does not include explicit SQL COMMIT statements.
INT_ROLLBACKS	The total number of rollbacks initiated internally by the database manager. NOTE: This value does not include explicit SQL COMMIT statements, but the INT_DEADLOCK_ROLLBACKS count is included.
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. NOTE: The database manager counters are reset only if you reset all active databases.
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. NOTE: This includes cases in which the section is already loaded in the cache and those in which the section has to be loaded into the cache.
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. NOTE: The “shared sort memory threshold” is determined by the value of the SHEAPTHRES database manager configuration parameter if the SHEAPTHRES_SHR database configuration parameter is 0. Otherwise, it is determined by the value of SHEAPTHRES_SHR.
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.