CQM_REORG_METRICS

Use the CQM_REORG_METRICS parameter in in CQMCAESV 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