System Monitor Guide and Reference

Database System Monitor Capabilities

The capabilities of the database system monitor opens several possibilities:

• Activity monitoring

  For example, using the database system monitor you can obtain:

  • The list of database connections:
    • The status of each connection.
    • The SQL that each is executing.
    • The locks that each holds.
  • The tables being accessed and the number of rows read and written for each.

  You can also track the progression of a query or application using information, such as:

  • The cursors that are currently open for this application.
  • The number of rows read or CPU consumed (if available from the operating system) by this application.
  • How long each query has been running.
  • How long an application has been idle.
• Problem determination

  You can collect data to help diagnose the cause of poor system and application performance. For example:

  • By tracing deadlocks you can determine conflicts between applications that lead to poor overall system performance.
  • By looking at the amount of time applications spent waiting for locks and which application is holding these locks you can identify applications that fail to commit their transactions, a common cause of poor system performance.
• Performance analysis

  You can use the information available to analyze the performance of individual applications or SQL queries. For example, you can monitor for:

  • The CPU consumed by each individual statement or application.
  • The time it takes to run a statement.
  • The number of rows read and returned.
  • The use of database resources, such as buffer pool, prefetchers, and SQL cache.
  • The number of times a particular DB2 dynamic SQL package has been executed.

  These run-time metrics are useful in tuning queries for optimal utilization of your database resources. Modifying a query or certain system parameters can result in dramatic performance improvements. The impact of your modifications can be measured with the database system monitor.

  You can also track the usage of indexes and tables, and in a partitioned database, the progression of a query on each partition. Adding indices or repartitioning the data often results in significant performance improvements.

  Carrying out some these performance analysis tasks may also require input that is obtained from the operating system, such as system load or the amount of free storage, or from other DB2 tools such as the db2 explain facility. For example, the db2expln application lets you analyze the access plan generated by the SQL compiler, which can then be compared with the run-time information available from the database system monitor.

• System configuration

  You can assemble the information necessary to evaluate and tune the effectiveness of your database manager and database configuration.

You can use the database system monitor to help monitor, tune, and manage your databases whether they are local or remote. On a multi-node system, you can work with a specific node in the DB2 instance, or collect information from all nodes on all partitions.