Using the Reorganization Recommendation Monitor

The Reorganization Recommendation Monitor enables you to view reorganization recommendations and analyze performance windows and that have been captured as potential candidates for reorganization analysis. You can also view information about the thresholds that are currently used to evaluate your data and stop reorganization recommendation monitoring.

Procedure

  1. In the CAE Browser Client, click Tools > Reorganization Recommendation Monitor.

    The left side of the window displays in a hierarchy of folders for your monitored Db2 Automation Tool job profiles and the group profiles they contain. The job profiles are sorted into folders for each Db2® subsystem or data sharing group.

    The right side of the window displays activity that has been collected for all objects, such as tablespaces and indexes, from the group profile that is selected in the left column.

    Above the table, the following information is shown:

    Status
    The monitoring status of the Db2 subsystem(s) for the selected group.
    CQM subsystem
    The Db2 Query Monitor subsystem or data sharing group member.
    Expires at
    The date and time that reorganization recommendation monitoring for the selected group (of job profiles) expires. This value can be set or updated in Db2 Automation Tool.
    Last Group Analyzed Time
    The number of seconds that Reorganization Recommendation Monitor spent analyzing the selected group of job profiles.

    Highlight a group profile to display the following in the table:

    • The objects (tablespace or index) that are monitored
    • Details about the last analysis that was triggered by Db2 Automation Tool.
    • Details about the baseline performance window (if collected)
    • Details about the comparison performance window (if collected)
    • Details about the next performance window
  2. In the left column, expand the folder structure and highlight the group profile of interest to display the following information in a table in the right side of the window:
    Database
    The Db2 database.
    Creator
    The object creator.
    Type
    The object type. Valid values are X (index) and S (tablespace).
    Name
    The table space or index.
    Active
    Indicates whether reorganization recommendation monitoring is active or not.
    Last reorganization recommendation result
    The columns in this section are updated by Db2 Automation Tool, they are not updated when you click the Analyze button. The information shown in these columns can help you to better understand the reorganization recommendations you receive from Db2 Automation Tool. For example, such information might help you to understand why Db2 Automation Tool did not recommend a reorganization for a particular table space or index.
    Recommendation
    The recommendation regarding whether a reorganization is needed. Valid values are:
    Recommended
    A reorganization is recommended.
    Not recommended
    A reorganization is not recommended.
    Not enough info
    There is insufficient information to make a recommendation.
    Not analyzed
    The selection was not analyzed.
    No baseline window
    No baseline window is available.
    No comparison window
    No comparison window is available.
    No CAE Agent
    The CAE Agent was not started when Db2 Automation Tool requested a reorganization recommendation.
    Not Monitored Db2
    The Query Monitor Subsystem was not started or monitoring of the Db2 subsystem was not activated when Db2 Automation Tool requested a reorganization recommendation.
    Additional columns
    Additional columns show the percent difference between the expected value and the total value for a metric. You can also view the threshold %, expected value, and total value by hovering your cursor over the table cell. The additional columns that display by default are CPU Time and Elapsed Time. If you override these default settings to use other metrics, the metrics you specify are shown instead.
    Analyzed Time, ms
    The number of milliseconds that Reorganization Recommendation Monitor spent analyzing one pageset in the group (of job profiles). After PH13428, this time (for one pageset) is equal to the time in the Last Group Analyzed Time field for all pagesets in the group because the CAE Server makes only one request for all pagesets in the selected group (of job profiles).
    Baseline performance window
    Start
    The starting date and time associated with the baseline performance window.
    End
    The ending date and time associated with the baseline performance window.
    Intervals
    The interval number.
    Comparison performance window
    Start
    The starting date and time associated with the comparison performance window.
    End
    The ending date and time associated with the comparison performance window.
    Intervals
    The interval number.
    Next performance window
    Start
    The starting date and time associated with the next performance window.
    End
    The ending date and time associated with the next performance window.
    Paused
    Db2 monitoring is paused and the following message is displayed at the top of the page: Db2 Subsystem ssid is not monitored by any CQM Subsystem. When Db2 monitoring is started, the Paused status is cleared and performance window analysis continues.
  3. Buttons at the top of the window enable you to perform the following additional actions:
    Thresholds
    View the threshold %, absolute threshold, and low threshold % for each metric that is analyzed for reorganization recommendation. The metrics and thresholds used for reorganization recommendation analysis can be customized using the following parameters:
    • com.rocketsoft.nm.qm.reorg.metrics - (CAE Server on Windows) Define this parameter in cqm.server.properties. If a cqm.server.properties file does not exist, create one.
    • CQM_REORG_METRICS parameter - (CAE Server on USS) Define this parameter in the STDENV DD of the CAE server started task CQMCAESV.
    Refresh
    Refresh the list of Db2 Automation Tool job profiles (left side of window) and the information shown in the data table (right side of window) with the most recent information that is available in the CAE Server.
    Stop
    Stop monitoring for the selected job profile that was submitted by Db2 Automation Tool to Db2 Query Monitor.
    Analyze
    Analyze whether a reorganization is needed for a selected object (tablespace or index). When you click Analyze, Db2 Query Monitor immediately evaluates whether a reorganization is recommended or not. This does not affect the status displayed in the Last reorganization recommendation result column. Last Reorg recommendation result shows what was sent to Db2 Automation Tool during the last Db2 Automation Tool build.
    Review
    Review the selected activity in the activity browser. This allows you to view the baseline and comparison windows and compare metrics.

Results for the Analyze action

When you click Analyze for a selected group, the analysis result, the selected object name, and the reason for the result. Clicking the Analyze button does not affect the status in the Last Reorg Recommendation column.

Analysis Dialog
Not enough information to make REORG recommendation for profile.object. These options are available for reason:
Reason: No baseline performance window.
Reason: No comparison performance window.
Reason: Retained values for all metrics are less than the threshold.

The following analysis results are possible (you can mouse-over the status for more details):

Recommended
A reorganization is recommended.
Not recommended
A reorganization is not recommended.
Not enough info
There is insufficient information to make a recommendation.
No baseline window
No baseline window is available.
No comparison window
No comparison window is available.