Setting up, starting, and using smarter reorganization recommendations

Follow these steps to set up, start, and use reorganization recommendation monitoring.

About this task

A general description of the steps that are required to implement smarter reorganization recommendations follows. These steps assume that you already know how to create Db2 Automation Tool object, utility, exception and job profiles. Additional information is provided in topic links.

Procedure

  1. Start the Db2 Query Monitor subsystem. For more information, see Starting the Query Monitor Subsystem.
  2. In Db2 Query Monitor, specify the appropriate reorganization recommendation settings, either in a monitoring profile or in the Db2 Query Monitor subsystem parameter file, CQMPARMS. Configure the monitoring profile or CQMPARMS file to collect data about the objects and SQLText associated with the SQL workloads of interest for reorganization recommendation monitoring.
    For more information, see:
  3. (Optional) Customize the metrics that Db2 Query Monitor will use for reorganization analysis. Perform this step if you want to change the default metrics that are used for reorganization analysis. For more information, see Customizing metrics and thresholds.
  4. (Optional) If you need to ensure that your performance data does not consume too much space, customize the maximum collection window length as needed. For more information, see Customizing the maximum collection window length.
  5. Activate the monitoring agent for the Db2 subsystem you want to monitor. You can activate the monitoring agent using the ISPF client or the CAE Browser Client. For more information, see Activating a monitoring agent - ISPF client or Activating a monitoring agent - CAE Browser Client.
  6. Start the CAE Agent on the LPAR where the Db2 Query Monitor subsystem is running. For more information, see Starting the CAE Agent.
  7. Start the CAE Server. For more information, see Starting the CAE Server.
  8. Verify that the CAE Agent is connected to the CAE Server. For more information, see Verifying that the CAE Agent and the CAE Server are connected.
  9. In Db2 Automation Tool, create the required object, utility, and exception profiles. At a minimum, a REORG TABLESPACE must be included in the utility profile and the QUERY MONITOR REORG_OVERRIDE exception must be specified in the exception profile. In addition to the REORG_OVERRIDE exception, you must select at least one other exception to trigger smarter reorganization recommendations. It is recommended that you choose an exception that evaluates whether the objects in the job profile require reorganization (such as the real-time statistic exception DAYS_SINCE_LAST). For more information about utility profiles, exceptions, and job profiles, see the Db2 Automation Tool V4.3 documentation.
  10. In Db2 Automation Tool, create a performance window.
    For more information, see "Creating and associating performance windows" in the Db2 Automation Tool V4.3 documentation.
  11. In Db2 Automation Tool, combine the object, utility and exception profiles into a job profile.
    When defining the job profile, associate the performance window that you created with this job profile. You can also optionally associate the job profile with a maintenance window.
  12. Run the Db2 Automation Tool build. This sets up the initial monitoring.
  13. (Optional) Re-run the job profile build on a regular basis (such as weekly) to re-evaluate all reorganization recommendations.
  14. (Optional) After you perform a Db2 Automation Tool build for reorganization recommendation, you can use the CAE Browser Client to view, analyze, and review performance windows that have been captured as potential candidates for reorganization recommendation or to stop reorganization recommendation monitoring. For more information, see Using the Reorganization Recommendation Monitor.
    The objects in the object profile are now monitored for smarter reorganization recommendations.

    On subsequent builds of the same Db2 Automation Tool job profile that requests a reorganization recommendation, review the messages that are returned from the build. The recommendation from Db2 Query Monitor is included in message CQMB662I. Additional CQMB messages may accompany this message that provide information about why a recommendation was or was not made.

    If you want to stop reorganization recommendation monitoring for one or more job profiles (before the job profile is configured to end, as specified in the Db2 Automation Tool build), see "Stopping reorganization recommendation monitoring" in the Db2 Automation Tool V4.3 documentation.

    Note: You can also stop reorganization recommendation monitoring using the CAE Browser Client. For more information, see Using the Reorganization Recommendation Monitor.