Smarter reorganization recommendations with Db2 Automation Expert and Db2 Query Monitor

Edit online

Db2 index and table space reorganizations are frequently used to improve application performance, and real-time statistics can be useful in indicating whether a REORG is needed. However, there are times when real-time statistics might indicate that a REORG is needed, but the application is accessing data in a way that is not impacted by disorganized data. Db2 Automation Expert can work with IBM® Db2 Query Monitor for z/OS® to leverage the SQL performance data that Db2 Query Monitor collects on your application objects to determine if a reorganization is necessary. Using these products together helps your organization avoid unnecessary reorganizations, thereby reducing CPU cycles, memory use, disk space usage, and the impact on your applications.

Db2 Query Monitor is an SQL statement monitor that can identify problem SQL activity and determine the tables and indexes that are used by the problem SQL statement.

In summary, implementing smarter reorganization recommendations involves the following steps:

  1. Configure Db2 Automation Expert and Db2 Query Monitor for reorganization recommendations.
  2. In Db2 Query Monitor, customize the settings for performance monitoring.
  3. In Db2 Automation Expert:
    1. Define a performance window (one or more periods of time in which Db2 Query Monitor monitors object performance).
    2. Create a job profile that specifies a REORG TABLESPACE utility and an exception profile that contains the QUERY MONITOR REORG_OVERRIDE (in addition to other relevant exceptions).
    3. Associate the job profile with a performance window.
    4. Build the job profile. After you build the job, Db2 Query Monitor begins monitoring the objects during the performance window time periods.
  4. On subsequent builds of the job profile, you can review build messages that report whether Db2 Query Monitor recommends or does not recommend the reorganization, based on analysis of the performance data collected during a performance window. You can explore the data that was analyzed by using the Reorganization Recommendation Monitor in the Db2 Query Monitor CAE browser client.