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