Chat now with support
Chat with Support

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

Partition Usability Collection

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

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.

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

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.

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.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating