Foglight for DB2 (Cartridge) 5.9.2.1 - User Guide

Instance Homepage

The DB2 Database Instance Homepage displays a summary view of the instance health. You drill down further to carry out root cause analysis of various performance issues.

To access the Instance Homepage, drill down to Databases Dashboard > Instance Homepage .

For more information, see Instance home page.

Database Homepage

The Database Homepage provides an overall view of the status of the selected entity for the selected time range, as well as drill-downs, which provide a visual representation of the status and performance of the monitored DB2 environment. These drill-downs allow you to determine any potential bottlenecks in database performance.

To access the Database Homepage, navigate through Databases Dashboard > Instance Homepage > Select Database, and then select a database name.

You can also access the Database Homepage for a database from the Databases Dashboard by clicking the icon at the far right of the database name cell and then selecting Database Homepage from the menu that appears.

NOTE: The Infrastructure agent configuration indicator at the bottom right of the Homepage notifies if the Infrastructure agent is installed and configured for the selected instance. This agent enables OS monitoring of the hosts where the DB2 LUW instance resides.

For information about the various sections appearing on this Homepage, see Database home page .

Using the Database Homepage Toolbar

The Database Homepage toolbar allows you to drill down and view a variety of DB2 database metrics.

The following toolbar options are provided:

An SQL snapshot contains information about SQL statements for the applications connected to the database. SQL snapshots are useful in determining the state of a database instance and also for observing trends and predicting potential problems.

The Top SQL panel shows the SQL snapshots captured during the selected time range. Drilling down on a snapshot shows the a list of 20 SQL statements that consumed the highest amount of system resources.

NOTE: The default number of top SQL statements is 20, but it can be customized.

To access the Top SQL panel, navigate through Databases Dashboard > Instance Homepage > Database Homepage, and then choose Activity > Top SQL.

You can also access the Tops SQL panel for a database from the Databases Dashboard by clicking the icon at the far right of the database name cell and then selecting Top SQL from the menu that appears.

For more information about this view, see Top SQL .

The Top SQL dashboard shows the SQL snapshots and the related SQL statement. Using this dashboard you can also access individual SQL statement by drilling down on an SQL statement.

The drill-down shows the SQL text in a larger view and the related metrics. For more information about this view, see SQL Statement Details View .

Use the Top Tables panel to help you monitor the performance of database tables.

To access the Top Tables panel, navigate through Databases Dashboard > Instance Homepage > Database Homepage, and then choose Activity > Top Tables.

For more information about this panel, see Top Tables .

The Locks panel displays information about current locks and historical locks held in the selected database.

To access the Locks panel, navigate through Databases Dashboard > Instance Homepage > Database Homepage, and then choose Activity > Locks.

You can also access the Locks panel for a database from the Databases Dashboard by clicking the icon at the far right of the database name cell and then selecting Locks from the menu that appears.

For more information, see Locks.

This panel provides metrics on agent workload, activity, and background operations.

To access the Current Agents panel, navigate through Databases Dashboard > Instance Homepage > Database Homepage, and then choose Activity > Current Agents.

You can also access the Current Agents panel for a database from the Databases Dashboard by clicking the icon at the far right of the database name cell and then selecting Current Agents from the menu that appears.

For more information, see Current Agents.

The User-defined Collections panel allows viewing the customized collections, which were created via the User-defined Collections page in the Administration dashboard, either during the last sample (Last Snapshot) or plotted over the specified time range (Selected Period).

To access the User-defined Collections panel, navigate through Databases > Instance Homepage > Database Homepage, and then choose Activity > User-defined Collections.

For more information, see User-defined Collections.

The Memory Summary panel provides memory summary metrics on the members and memory pools for the selected database.

To access the Memory Summary panel, navigate through Databases > Instance Homepage > Database Homepage, and then choose Memory > Memory summary.

For more information, see Memory Summary Pane.

The Buffer Pools panel provides performance metrics on the buffer pools for the selected database.

To access the Buffer Pools panel, navigate through Databases > Instance Homepage > Database Homepage, and then choose Memory > Buffer Pools.

For more information, see Buffer Pools Pane.

Storage Panel

All database data is stored in a number of Tablespaces. There are different types of tablespaces, including catalog, regular, large, system-temporary, and user-temporary tablespaces. Tablespaces can be managed by the OS (System-Managed Space — SMS) or the database (Database-Managed Space — DMS). Each tablespace can have one or more containers. Each tablespace has a page size associated with it, which limits the row length and column count of tables that can be placed in the tablespace, meaning that a larger page increases the capacity of the tablespace. The utilization of the page size has an effect on the usage of overall tablespace resources. For example, having a small table with a large page size results in a significant amount of wasted space and inefficient I/O and buffering usage.

The Tablespaces panel shows the top ten tablespaces with the highest percentage of the space usage, along with individual table properties for each individual tablespace. Use this panel to quickly investigate the existing tablespace usage and look for parameters that can indicate potential performance issues.

To access the Tablespaces panel, navigate through Databases > Instance Homepage > Database Homepage, and then choose Storage > Tablespaces.

You can also access the Tablespaces panel for a database from the Databases Dashboard by clicking the icon at the far right of the database name cell and then selecting Tablespaces from the menu that appears.

For more information about this panel, see Tablespaces .

The Tablespace Details view shows individual tablespace properties and metrics. This dashboard also provides space utilization and free space fragmentation charts that can give you insight in how well the system can perform based on the existing storage resources. For example, a high amount of fragmented disk space can lead to increased performance overhead. You drill down to this view by clicking a tablespace entry on the Tablespaces dashboard or clicking on the tablespace name in the Tablespace Summary section.

For more information about this view, see Tablespace Details .

A buffer pool is memory that temporarily stores data and index pages from the database. If an application needs to read a data or index page and that page is already in the buffer pool, the application reads the page from the buffer pool, a process that is faster than retrieving the page from disk. Avoiding disk I/O is a critical issue when you are trying to improve database logical and physical read performance.

The Tablespaces Bufferpools Activities panel shows the top tablespaces with the highest percentage of the space usage, and for each tablespace, it displays its buffer pool usage, such as data reads and writes. Use this dashboard to quickly investigate the buffer pool usage for the top tablespace consumers, and to look at the overall buffer pool performance.

To access the Tablespaces Bufferpool Activities panel, navigate through Databases > Instance Homepage > Database Homepage, and then choose Storage > Tablespaces Bufferpool Activities.

For more information about this panel, see Tablespaces .