Foglight for DB2 (Cartridge) 7.2.0


Metric	Description
INSTANCE_NAME	The name of the instance hosted in a pureScale environment.
PURESCALE_ALERTS_NO	Total number of alerts DB2 reported on the pureScale environment.
MESSAGE	Information about the alert.
ACTION	The action required to clear the alert.
IMPACT	The impact to the DB2 pureScale instance if the alert is not cleared.

Top SQLs Collection

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


Metric	Description
DB_NAME	Name of the Monitored database
MEMBER	The database partition/member from which the data was retrieved for this row.
EXECUTABLE_ID	An opaque binary token generated on the data server that uniquely identifies the SQL statement section that was executed.
SECTION_TYPE	Type of SQL. Can be 'S' for static SQL or 'D' for dynamic. Static SQLs are only available in DB2 version 9.7.0.1 and above.
STMT_TYPE_ID	Statement type identifier. Possible values are: Statement not prepared DDL, (not Set Constraints) DDL, Set Constraints DML, Select DML, Insert/Update/Delete Authorization DML, Select (blockable) DML, Lock Table DML, Commit/Rollback Set environment DDL, Savepoint DDL, (declared user temp) Passthru support CALL Free locator DML, Select with IUD DML, Select with IUD (blockable) Top-level SET, no SQL Top-level SET, reads SQL DDL, (issues internal commit) Top-level SET, modifies SQL Unknown Parent topic: Database system monitor elements
HASH_VALUE	Identifier of the SQL by hash value.
NET_ELAPSED_TIME	The total time in seconds that was spent executing the particular statement, excluding time spent executing routines used as part of the statement.
NUM_EXECUTIONS	The number of times that an SQL statement has been executed. Can be used to identify the most frequently executed SQL statements in your system.
NUM_EXECUTIONS_WITH_METRICS	Number of executions that include performance metrics. Used to calculate the averages correctly.
ROWS_READ	The number of rows read from the table. This element helps you identify tables with heavy usage for which you may want to create additional indexes.
ROWS_RETURNED	The number of rows that have been selected and returned to the application. If this number is significantly lower then "Rows read", then it might indicate an index is missing.
ROWS_MODIFIED	The number of rows changed (inserted, deleted or updated) by the statement. A high value for table-level information indicates there is heavy usage of the table and you may want to use the Run Statistics (RUNSTATS) utility to maintain efficiency of the packages used for this table.
QUERY_COST_ESTIMATE	Estimated cost for a query, as determined by the SQL compiler. This value is reported in timerons.
TOTAL_ROUTINE_INVOCATIONS	The total number of times a routine was invoked by the statement.
DEADLOCKS	The number of times that a request to lock an object resulted in a deadlock.
LOCK_WAITS	The number of times that applications or connections waited for locks.
LOCK_TIMEOUTS	The number of times that a request to lock an object timed out instead of being granted.
STMT_SORTS	The total number of times that a set of data was sorted in order to process the statement operation. Can be used to help identify the need for an index, since indexes can reduce the need for sorting of data. Using the related elements in the above table you can identify the SQL statement for which this element is providing sort information, and then analyze this statement to determine index candidates by looking at columns that are being sorted (for example, columns used in ORDER BY and GROUP BY clauses and join columns).
SORT_OVERFLOWS	The total number of sorts that ran out of sort heap and may have required disk space for temporary storage.
DIRECT_READS	The number of read operations that do not use the buffer pool.
DIRECT_WRITES	The number of write operations that do not use the buffer pool.
POOL_L_READS	Total logical Reads for the SQL, including data, index, temp, XML storage object (XDA) and temp_xda reads.
POOL_P_READS	Total Physical Reads for the SQL, including data, index, temp, XML storage object (XDA) and temp_xda reads
POOL_WRITES	The number of times a buffer pool page was physically written to disk. This includes both data pages as well as XML storage object (XDA) and index pages.
TOTAL_CPU_TIME_S	The total CPU time consumed by the statement.
LOCK_WAIT	The time spent waiting for locks.
COMPILE_TIME	The overall amount of time that was required to prepare the specific SQL statement.
CF_WAIT_TIME	The overall amount of time spent communicating with the cluster caching facility.
WLM_WAIT	The time spent waiting on a WLM queuing threshold.
DIRECT_READ_TIME	The elapsed time required to perform direct reads, i.e: reads that do not use the buffer pool.
DIRECT_WRITE_TIME	The elapsed time required to perform direct writes, i.e: reads that do not use the buffer pool.
POOL_READ_TIME	The elapsed time required to perform pool reads, i.e: reads that use the buffer pool.
POOL_WRITE_TIME	The elapsed time required to perform pool writes, i.e: reads that use the buffer pool.
PREFETCH_WAIT_TIME	The time spent waiting for an I/O server (prefetcher) to finish loading pages into the buffer pool.
RECLAIM_WAIT_TIME	In a DB2® pureScale® environment, this represents the amount of time spent waiting on page locks, where the lock request caused a page to be reclaimed.
SPACEMAPPAGE_RECLAIM_WAIT_TIME	In a DB2® pureScale® environment, this represents the amount of time spent waiting on page locks for pages related to internally maintained object space management where the lock request caused a reclaim from another member.
AUDIT_FILE_WRITE_WAIT_TIME	Time spent waiting to write an audit records.
AUDIT_SUBSYSTEM_WAIT_TIME	Time spent waiting for space in audit buffer. Waiting occurs when audit buffer is full and agent must wait for audit daemon to write buffer to disk.
DIAGLOG_WRITE_WAIT_TIME	The time spent waiting on a write to the db2diag log file.
EVMON_WAIT_TIME	The amount of time spent waiting for an event monitor record to become available.
IDA_RECV_WAIT_TIME	The time spent waiting to receive data from an in-database analytics process.
IDA_SEND_WAIT_TIME	The time spent waiting to send data to an in-database analytics process.
LOG_BUFFER_WAIT_TIME	The amount of time an agent spends waiting for space in the log buffer.
LOG_DISK_WAIT_TIME	The amount of time spent waiting for log records to be flushed to disk.
TOTAL_SECTION_PROC_TIME	The total amount of processing time spent performing section execution. Processing time does not include wait time.
TOTAL_SECTION_SORT_TIME	Total amount of time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement.
FCM_RECV_WAIT_TIME	The time spent waiting for an FCM reply message containing the results of a previously sent FCM request message. This value reflects both the time required to send the response between partitions using FCM and the time required for the subagent to process the request message.
FCM_SEND_WAIT_TIME	The time spent blocking on an FCM message send. The value reflects the time spent blocking for FCM buffers to be flushed from an FCM channel when distributing internal requests on the database system.
TOTAL_ROUTINE_TIME	The total time spent executing routines.
TOTAL_SECTION_TIME	The total time agents spent performing section execution.
TOTAL_WAIT_TIME	Total time spent waiting within the DB2® database server, while processing an activity. These includes waits for things like I/O, Locks, Memory etc. The value is given in seconds.
WAIT_FOR_CPU_S	The total time that requests, that were run in a service class, spent waiting to access the CPU.
TOTAL_EXTENDED_LATCH_WAIT_TIME	The amount of time, spent in extended latch waits.
STMT_ELAPSED_TIME	The total time in seconds that was spent executing the particular statement including all routines that were called.
TOTAL_HIT_RATIO	A ratio indicating the amount of physical reads as oppose to logical overall reads. The indicator is calculated using the formula: 100 * (1 - (POOL_P_READS /POOL_L_READS).
IO_WAIT	Total time spent waiting, while processing an I/O request. Include read and write operations, both direct and from memory pools.
EXECUTE_TIME	Total time spent executing routines and performing section execution. Processing time does not include wait time.
LOG_WAIT	Total time spent waiting for log records to be flushed to disk or waiting for space in the log buffer.
OTHER_WAIT	Total time spent waiting for miscellaneous events such as audit records writes, dialog writes, event monitor records and send or receive data from an in-database analytics process.
FCM_WAIT	The time spent waiting for an FCM message to be sent or FCM reply to be received.
PURESCALE_WAIT	In a DB2® pureScale® environment, this is the time spent waiting for communicating with the cluster caching facility or waiting for page locks, where the lock request caused a page to be reclaimed either locally or from another member.
MEMORY_WAIT	The amount of time, spent in extended latch waits.
WAIT_TIME_PERCENT	Percent of time spent waiting within the DB2® database server out of the total time spent actively executing the statement.
OVERALL_SORT_TIME	Summary of the sort time of all the gathered SQL's.
OVERALL_CPU_WORKLOAD	Summary of the CPU of all the gathered SQL's.
TOTAL_NUM_EXECUTIONS	Summary of the SQL executions of all the gathered SQL's.
OVERALL_EXECUTION_TIME	Summary of the execution time of all the gathered SQL's.
OVERALL_POOL_L_READS	Summary of the data logical reads of all the gathered SQL's.
TOTAL_COMPILE_TIME	Summary of the prep time of all the gathered SQL's.
OVERALL_POOL_P_READS	Summary of the data physical reads of all the gathered SQL's.

Query Agents Details Collection

The following table provides a list of the Query Agents Details 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.
FOREGROUND_BACKGROUND_TYPE	The foreground or background status of the application.
LAST_RESET	The date and time when the monitor 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-(PKG_CACHE_INSERTS/PGK_CACHE_LOOKUPS))*100
POOL_DATA_WRITES	The number of times a buffer pool data page was physically written to disk.
PERCENT_SORT_OVERFLOWS	The percentage of sort operations, out of the total sort operations, that ran out of sort heap and may have required disk space for temporary storage.

Database Partition Activity Collection

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


Metric	Description
DATABASE_NAME	The name of the monitored database
MEMBER	The member or partition ID.
TOTAL_RQSTS	The total amount of requests completed.
AVG_RESPONSE_TIME	The average duration (in seconds) for a single request.
TOTAL_RQST_TIME	The total amount of time (in seconds) spent working on requests.
BALANCE_TOTAL_RQST_TIME	The balance of the total amount of time spent working on requests across the database partitions. The balance is poor if one partition (or member) is doing most of the activity.
TOTAL_WAIT_TIME	The total amount of time (in seconds) spent waiting within the DB2 database server.
TOTAL_CPU_TIME	The total amount of CPU time used (in seconds) while within the DB2 database server. This includes both user and system CPU time.
BALANCE_TOTAL_CPU_TIME	The balance of CPU usage across all monitored partitions.
%WAIT	The amount of time the database spent waiting, as a percentage of the total request time.
TOTAL_CPU_WAIT	The total amount of time (in microseconds) that requests run in this service class spent waiting to access the CPU. This is only available for DB2 version 10 and higher.
FCM_REVC_VOLUME	The total amount of data (in bytes) received through the FCM communications layer.
FCM_SEND_VOLUME	The total amount of data (in bytes) distributed by the FCM communications layer.
FCM_REVC_VOLUME_RATE	The total amount of data (in bytes) received through the FCM communications layer.
FCM_SEND_VOLUME_RATE	The total amount of data (in bytes) distributed by the FCM communications layer.
TCPIP_SEND_VOLUME_KB	The amount of data (in KB) sent by the data server to the clients.
TCPIP_SEND_VOLUME_RATE	The number of KBs sent per second by the data server to the clients.
TCPIP_REVC_VOLUME_KB	The amount of data (in KB) received by the data server from the clients over TCP/IP.
TCPIP_REVC_VOLUME_RATE	The number of KBs received per second by the server from the clients.

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

pureScale Alerts Collection

Top SQLs Collection

Query Agents Details Collection

Database Partition Activity Collection