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.

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.