CQM_REORG_METRICS

Use the CQM_REORG_METRICS parameter in CQMCPRMS to control the reorganization recommendation behavior of Db2 Query Monitor.

Description
Specifies the metrics and thresholds analyzed during reorganization recommendation. One or more metrics (and their corresponding threshold values) can be defined. A colon separates the metric and its values. A comma separates each metric/value set.
Syntax
CQM_REORG_METRICS=metric1:low_threshold%_1:absolute_threshold_1:threshold%_1,metric2:low_threshold%_2:absolute_threshold_2:threshold%_2
metric1, metric2
The metrics that are analyzed for reorganization recommendation. Valid metrics include columns in the CQM_SUMM_METRICS table.
low_threshold%_1, low_threshold%_2
The low threshold percentage is used to verify that sufficient data exists in the comparison performance window to analyze and to make a reorganization recommendation. The low threshold percentage is checked when recommendation is requested from Db2® Automation Tool.
absolute_threshold_1, absolute_threshold_2
The absolute threshold for a metric is used to verify that sufficient data exists in the performance window to analyze and to make a reorganization recommendation.
threshold%_1, threshold%_2
The threshold percentage for a metric is the percentage by which the metric differs as measured in the baseline performance window versus the comparison performance window. The threshold % is checked when recommendation is requested from Db2 Automation Tool.
For example, the default value DB2_CPU:60:1:10,DB2_ELAPSED:60:10:20, specifies that:
  • The total DB2_CPU value (for all statements that ran against the specified objects) must be greater than 1 second or the total DB2_ELAPSED value (of all statements that ran against specified objects) must be greater than 10 seconds for the interval to be saved as a baseline or a comparison performance window.
  • One of the following conditions must be met to initiate reorganization recommendation analysis (otherwise the message "Not enough data" is issued):
    • The total DB2_CPU value of all statements in the comparison window that are common with the statements in the baseline window must be at least 60% of the total DB2_CPU value (the low threshold%) for all statements from the comparison window.
    • The total DB2_CPU value of all statements in the comparison window that are common with the statements in the baseline window must be at least 1 second (the absolute threshold).
    • The total DB2_ELAPSED value of all statements in the comparison window that are common with the statements in the baseline window must be at least 60% of the total DB2_ELAPSED value (the low threshold%) for all statements from the comparison window.
    • The total DB2_ELAPSED value of all statements in the comparison window that are common with the statements in the baseline window must be at least 10 seconds (the absolute threshold).
  • If one of the preceding conditions is met, then DB2_CPU performance must degrade by 10% or more, or DB2_ELAPSED performance must degrade by 20% or more (the threshold %) for a reorganization to be recommended by Db2 Query Monitor.
Default
DB2_CPU:60:1:10,DB2_ELAPSED:60:10:20
Example
CQM_REORG_METRICS=GETPAGES:60:100:15