IBM DB2 Performance Counters

Introduction

DB2 has many advantages over other DBMSs. It is designed with high-performance features and offers superior scalability, which allows it to easily manage large datasets with minimal effort. It is also very secure, with built-in data encryption, a comprehensive security model, and advanced auditing capabilities. DB2 also has high availability and disaster recovery features, allowing it to quickly recover from outages.

In addition to its performance and scalability, DB2 is also known for its advanced data modelling and query optimization features. It supports advanced SQL capabilities, allowing users to create complex queries that are optimized for better performance. It also supports stored procedures and user-defined functions, which can be used to customize the database and increase efficiency.

Overall, DB2 is an excellent choice for organizations looking for an enterprise-grade database system. It is designed to handle large datasets and offers superior scalability, high availability, and advanced data modelling and query optimization features. It is secure, reliable, and cost-effective, making it an ideal choice for businesses of all sizes.

Key Features of DB2

1. Scalable and Adaptable: DB2 is highly scalable and can easily adapt to changing data sizes, workloads and environments.
2. Security: DB2 provides advanced security features such as authentication, authorization, encryption, auditing, and logging.
3. High Availability: DB2 provides high availability and disaster recovery options to ensure that data is always available.
4. Ease of Use: DB2 is easy to use and provides comprehensive monitoring and management tools.
5. Cost-Effective: DB2 offers a cost-effective solution when compared to other databases.

Performance Counters of IBM DB2

Connections

• Remote connections: The current number of connections initiated from remote clients to the instance of the database manager that is being monitored.
• Connections executing requests: The number of remote applications that are currently connected to a database and are currently processing a unit of work within the database manager instance being monitored.
  This monitor is not available in versions 9.x and higher.

• Idle agents: The number of Agents in the Agent pool that are currently unassigned to an application and therefore idle.
  This element cannot be used to help set the num_poolagents configuration setting. Having idle Agents available to service requests for Agents can improve performance.

Catalog Cache

• Catalog cache lookups: The number of times that the catalog cache was referenced to obtain table descriptor information or authorization information.
  This element includes both successful and unsuccessful access to the catalog cache. The catalog cache is referenced whenever:
  • a table, view, or alias name is processed during the compilation of an SQL statement.
  • database authorization information is accessed.
  • a routine is processed during the compilation of an SQL statement.
• Catalog cache inserts: The number of times that the system tried to insert table descriptor or authorization information into the catalog cache.
• Catalog cache overflows: The number of times that the catalog cache overflowed the bounds of its allocated memory.
  If cat_cache_overflows is high, the catalog cache may be too small for the workload. Enlarging the catalog cache may improve its performance.

IO Requests

• 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.
• Direct read time: The elapsed time (in milliseconds) required to perform the direct reads.
  A high average time may indicate an I/O conflict.
• Direct write time: The elapsed time (in milliseconds) required to perform the direct writes.
  A high average time may indicate an I/O conflict.

Command Rates:

• Select statements: The number of SQL SELECT statements that were executed. This element can be used to determine the level of database activity.
• Commit statements: The total number of SQL COMMIT statements that have been attempted.
  A small rate of change in this counter during the Monitor period may indicate that applications are not doing frequent commits, which may lead to problems with logging and data concurrency.
• Rollback statements: The total number of SQL ROLLBACK statements that have been attempted.
  It is advised to minimize the number of rollbacks, since higher rollback activity results in lower throughput for the database.
• Update/insert/delete statements: The number of SQL UPDATE, INSERT, and DELETE statements that were executed. This element can be used to determine the level of database activity.
• Overflow accesses: The number of accesses (reads and writes) to overflowed rows of tables.
  Overflowed rows indicate that data fragmentation has occurred. If this number is high, improving the database performance may be done by reorganizing the database.
  This Monitor is not available in versions 9.X and higher.

Tables:

• Rows read: This is the number of rows read from the tables.
• Rows inserted: This is the number of row insertions attempted. This element can be used to gain insight into the current level of activity within the database.
• Rows deleted: This is the number of row deletions attempted. This element can be used to gain insight into the current level of activity within the database.
• Rows updated: This is the number of row updates attempted. This element can be used to gain insight into the current level of activity within the database.

Memory:

• Committed private memory: The amount of private memory that the instance of the database manager has currently committed.
  This element can be used to help set the min_priv_mem configuration parameter to ensure enough private memory is available.
• Total log space used: The total amount of active log space (in bytes) currently used in the database.
  This element must be used in conjunction Log space available to determine whether to adjust the following configuration parameters to avoid running out of log space:
  • logfilsiz
  • logprimary
  • logsecond
• Log space available: The amount of active log space in the database that is not being used by uncommitted transactions.
• Log pages read: The number of log pages read from the disk by the logger.
  This element can be used with an operating system Monitor to quantify the amount of I/O on a device that is attributable to database activity.
• Log pages write: The number of log pages written to disk by the logger.
  This element can be used with an operating system Monitor to quantify the amount of I/O on a device that is attributable to database activity.
• Secondary logs allocated: The total number of secondary log files that are currently being used for the database.
  If this value is consistently high, it may be necessary to have larger log files or more primary log files or more frequent COMMIT statements within the application.

Pool

• Pool data logical reads: Indicates the number of data pages which have been requested from the buffer pool (logical) for regular and large table spaces.
• % Pool data logical reads: Percentage of data pages which have been requested from the buffer pool (logical) for regular and large table spaces compared to data pages read in from the table space Containers (physical).
• Pool data physical reads: Indicates the number of data pages read in from the table space Containers (physical) for regular and large table spaces.
• Pool index logical reads: Indicates the number of index pages which have been requested from the buffer pool (logical) for regular and large table spaces.
• % Pool index logical reads: Percentage of index pages which have been requested from the buffer pool (logical) for regular and large table spaces compared to index pages read in from the table space Containers (physical).
• Pool index physical reads: Indicates the number of index pages read in from the table space Containers (physical) for regular and large table spaces.
• Pool index writes: Indicates the number of times a buffer pool index page was physically written to disk.
• Pool read time: Indicates the total amount of time spent reading in data and index pages from the table space Containers (physical) for all types of table spaces. This value is given in microseconds.
• Pool write time: Provides the total amount of time spent physically writing data or index pages from the buffer pool to disk. Elapsed time is given in microseconds.

Hash Joins

• Total hash joins: The total number of hash joins executed.
  Use this value in conjunction with hash_join_overflows to determine if a significant percentage of hash joins would benefit from modest increases in the sort heap size.
• Hash joins overflows: The number of times that hash joins data exceeded the available sort heap space.
• % Hash joins overflows: The percentage of hash join data exceeded the available sort heap space.

Miscellaneous

• Locks waiting: Indicates the number of Agents waiting on a lock.
  If this number is high, the applications may have concurrency problems; it is advised to identify applications that are holding locks or exclusive locks for long periods.
• Locks held: The number of locks currently held. This is the total number of locks currently held by all applications in the database.
• Deadlocks: The total number of deadlocks that have occurred.
  This element can indicate that applications are experiencing contention problems. These problems could be caused by the following situations:
  • Lock escalations are occurring for the database.
  • An application may be locking tables explicitly when system-generated row locks may be sufficient.
  • An application may be using an inappropriate isolation level when binding.
  • Catalog tables are locked for a repeatable read.
  • Applications are getting the same locks in different orders, resulting in deadlocks.
    Resolving the problem can be done by determining in which applications (or application processes) the deadlocks are occurring. Modifying the application may be available to better enable it to execute concurrently.
• Active sorts: The number of sorts in the database that currently have a sorted heap allocated.
• Total sorts: The total number of sorts that have been executed. This value includes heaps of sorts of temporary tables that were created during relational operations.
• Sorts overflows: The total number of sorts that ran out of the sort heap and may have required disk space for temporary storage.
  When a sort overflows, the additional overhead will be incurred because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk.
• % Sorts overflows: Percentage of sorts that ran out of sort heap and may have required disk space for temporary storage.
  When a sort overflows, the additional overhead will be incurred because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk.
• Version: DB2 database server version.

Top Statements

• CPU: The requests having consumed the most processor time.
• Rows read: The requests having read the most rows from the database.
• Rows written: The requests having written the most rows to the database.
• Sort time: The requests with the longest sort times.
• Sort overflows: The requests with the highest number of sort overflows.

---