Chat now with support
Chat with Support

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

pureScale Alerts Collection

Describes the metrics collected about instances hosted in a DB2 pureScale environment.

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.

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.

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.

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.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating