Foglight for DB2 (Cartridge) 5.9.2.1


Metric	Description
INSTANCE	The name of the monitored instance.
PARTITION_NUMBER	The partition number.
PARTITION_HOST_NAME	The partition host name.
PARTITION_AVAILABILITY	The availability of the partition, which reflects the instance usability.
ERROR_MSG_OBS	Indicates the error message received while attempting to connect to the database.
OS_CONNECT_AVAILABILITY	Indicates the OS connect availability with the associated OS credential. Values: 0 — Connection to the remote host failed. 100 — Connection to the remote host succeeded.
OS_CONNECTION_TIME	The average duration (in milliseconds) for performing a single connection to the database.

pureScale Alerts Collection

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


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	The name of the monitored database.
DBPARTITIONNUM	The database partition from which the data was retrieved for this row.
HASH_VALUE	The identifier of the SQL by hash value.
NUM_EXECUTIONS	The number of times that a SQL statement has been run. You can use this element to identify the most frequently run SQL statements in your system.
NUM_COMPILATIONS	The number of different compilations for a specific SQL statement.
PREP_TIME_WORST	The longest amount of time that was required to prepare a specific SQL statement.
PREP_TIME_BEST	The shortest amount of time that was required to prepare a specific SQL statement.
INT_ROWS_DELETED	The number of rows deleted from the database as a result of internal activity.
INT_ROWS_INSERTED	The number of rows inserted into the database as a result of internal activity caused by triggers.
INT_ROWS_UPDATED	The number of rows updated from the database as a result of internal activity.
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 indices.
ROWS_WRITTEN	The number of rows changed (inserted, deleted, or updated) in the table. A high value for table-level information indicates that the table is being heavily used and you may want to use the Run Statistics (RUNSTATS) utility to maintain the efficiency of the packages used for the table.
STMT_SORTS	The total number of times that a set of data was sorted in order to process the STMT_OPERATION. You can use this element to help identify the need for an index, since indices can reduce the need for data sorting.
SORT_OVERFLOWS	The total number of sorts that ran out of sort heap and may have required disk space for temporary storage. At a database or application level, use this element with TOTAL_SORTS to calculate the percentage of sorts that had to overflow to disk. If this percentage is high, you may want to adjust the database configuration by increasing the value of sort heap.
TOTAL_SORT_TIME_S	The total elapsed time for all sorts that have been run.
TOTAL_EXEC_TIME_S	The total time (in seconds) that was spent running a particular statement in the SQL cache.
TOTAL_CPU_TIME_S	The total CPU time (user + system).
TOTAL_USR_CPU_TIME_S	The total user CPU time for a SQL statement.
TOTAL_SYS_CPU_TIME_S	The total system CPU time for a SQL statement.
TOTAL_HIT_RATIO	The total hit ratio of the SQL.
INDEX_HIT_RATIO	The total index hit ratio of the SQL.
TOTAL_POOL_L_READS	The total logical reads for the SQL — including data, index, temp, XDA, and TEMP_XDA reads.
TOTAL_POOL_P_READS	Total physical reads for the SQL — including data, index, temp, XDA, and TEMP_XDA reads.
TOTAL_CPU_TIME_PER_EXECUTION	The statement total CPU time per execution.
SORTS_PER_EXECUTION	The statement sorts per execution.
SORT_OVERFLOWS_PER_EXECUTION	The statement sort overflows per execution.
TOTAL_SORT_TIME_PER_EXECUTION	The statement total sort time per execution.
TOTAL_EXEC_TIME_PER_EXECUTION	The statement total execution time per execution.
ROWS_READ_PER_EXECUTION	The statement rows read per execution.
ROWS_WRITTEN_PER_EXECUTION	The statement rows written per execution.
ROWS_RETURNED_PER_EXECUTION	The statement rows returned per execution.
PREP_TIME_PER_EXECUTION	The statement prep time per execution.
POOL_DATA_L_READS	The number of data pages that have been requested from the buffer pool (logical) for regular and large tablespaces.
POOL_DATA_P_READS	The number of data pages read in from the tablespace containers (physical) for regular and large tablespaces.
POOL_TEMP_DATA_L_READS	The number of data pages that have been requested from the buffer pool (logical) for temporary tablespaces.
POOL_TEMP_DATA_P_READS	The number of data pages read in from the tablespace containers (physical) for temporary tablespaces.
POOL_INDEX_L_READS	The number of index pages that have been requested from the buffer pool (logical) for regular and large tablespaces.
POOL_INDEX_P_READS	The number of index pages read in from the tablespace containers (physical) for regular and large tablespaces.
POOL_TEMP_INDEX_L_READS	The number of index pages that have been requested from the buffer pool (logical) for temporary tablespaces.
POOL_TEMP_INDEX_P_READS	The number of index pages read in from the tablespace containers (physical) for temporary tablespaces.
POOL_XDA_L_READS	The number of data pages for XML storage objects (XDAs) that have been requested from the buffer pool (logical) for regular and large tablespaces.
POOL_XDA_P_READS	The number of data pages for XML storage objects (XDAs) read in from the tablespace containers (physical) for regular and large tablespaces.
POOL_TEMP_XDA_L_READS	The number of pages for XML storage object (XDA) data that have been requested from the buffer pool (logical) for temporary tablespaces.
POOL_TEMP_XDA_P_READS	The number of pages for XML storage object (XDA) data read in from the tablespace containers (physical) for temporary tablespaces.
NUM_EXEC_WITH_METRICS	The number of executions that include performance metrics. This is used to calculate the averages correctly.
SECTION_TYPE	The type of SQL. Values: S — Static SQL D — Dynamic SQL Static SQLs are only available in DB2 version 9.7.0.1 and higher.
STMT_TYPE_ID	The statement type identifier. Values: 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
EXECUTABLE_ID	An opaque binary token that is generated on the data server and uniquely identifies the SQL statement section that was run.
TOTAL_WAIT_TIME_S	The total time (in seconds) spent waiting within the DB2 database server while processing an activity.
WAIT_TIME_PERCENT	The percentage of time spent waiting within the DB2 database server out of the total time spent actively working on requests.
ROWS_RETURNED	The number of rows that have been selected and returned to the application. If this number is lower than “Rows read”, an index might be missing.
OVERALL_SORT_TIME	The summary of the sort time of all the dynamic SQLs.
OVERALL_CPU_WORKLOAD	The summary of the CPU of all the dynamic SQLs.
TOTAL_NUM_EXECUTIONS	The summary of the dynamic SQL executions of all the dynamic SQLs.
OVERALL_EXECUTION_TIME	The summary of the execution time of all the dynamic SQLs.
OVERALL_POOL_L_READS	The summary of the data logical reads of all the dynamic SQLs.
TOTAL_PREP_TIME_WORST_MS	The summary of the prep time of all the gathered SQLs.
OVERALL_POOL_P_READS	The summary of the data physical reads of all the dynamic SQLs.

Top SQLs Full Text Collection

The following table provides a list of the Top SQLs Full Text 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.
HASH_VALUE	The ID of the SQL by hash value.
SQL_FULL_TEXT	The full text for the SQL statement.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Foglight for DB2 (Cartridge) 5.9.2.1 - User Guide

Partition Usability Collection

pureScale Alerts Collection

Top SQLs Collection

Top SQLs Full Text Collection