Specifying batch Explain options
Use the following procedure to specify processing options for your batch Explain operation.
Access the Explain Options panel.
Explain Options panel-Batch Explain Options
PSSPA020 ----------------------- Explain Options ------------------------------
Command ===>
Specify options and press ENTER to continue.
Press F8 to scroll down and F7 to scroll up.
More: - +
------------------------- Batch Explain Options ------------------------------
Explain INSERT Statements Y (Y/N)
Distribution Statistics . NONE (CDIST,KTDIST,BOTH,NONE)
Include Stats Feedback . Y (Y/N)
Rule Message Level . . . . A (A=All, S=Severe, W=Warning, I=Informational)
Suppress Statement Detail When No Rules Fire N (Y/N)
Maintain History . . . . . N (Y/N)
Histories Retained . . . 3 (0-99)
Summary Report Sort Order 1 1. Object, Statement
2. Cost (desc)
3. Object, Cost (desc)
Distribution Statistics . NONE (CDIST,KTDIST,BOTH,NONE)
Include Stats Feedback . Y (Y/N)
Rule Message Level . . . . A (A=All, S=Severe, W=Warning, I=Informational)
Suppress Statement Detail When No Rules Fire N (Y/N)
Maintain History . . . . . N (Y/N)
Histories Retained . . . 3 (0-99)
Summary Report Sort Order 1 1. Object, Statement
2. Cost (desc)
3. Object, Cost (desc)
Generate Report Title . . Y (Y/N)
Title 'EXPLAIN REPORT'- In the Explain INSERT Statements field, select one of the following options:
- Y Explains the INSERT statements.
- N skips the INSERT statements.
- In the Include Stats Feedback field, select one of the following options:
- Y Includes the DSN_STAT_FEEDBACK table for batch Explain. (default)
N Skips the DSN_STAT_FEEDBACK table for batch Explain.
Specify the Rule Message Level to display in the report:
- All includes Informational, Warning, and Severe messages in the report. (default)
- Severe flags only the statements and situations that might cause severe performance problems, such as a SELECT * statement.
Warning flags statements that can be improved to enhance performance.
Arithmetic operations become Non-indexable, Stage 2 predicates. Consider comparing to a computed value instead of the expression.
Informational includes messages that provide information that might help you make changes to improve performance.
The Column Functions (MIN/MAX) on Composite index can take advantage of One-Fetch access when there exists a matching predicated on the high order column other than a LIKE predicate
In the Suppress Statement Detail When No Rules Fire field, specify whether to suppress detail in batch Explain reports. This option is used in conjunction with Rule Message Level. It does not affect the analysis object summary information or the summary report.
- Y suppresses the detail in a batch Explain report when no rules are issued, based on the Rule Message Level that you specify.
- N displays the detail in a batch Explain report, based on the Rule Message Level that you specify. (default)
The following table lists the results of various combinations of these options.
Results of Suppress Statement Detail option
Rule Message Level is
and
and Suppress Statement Detail is either
Y
N (default)
Severe
Severe rules are issued
All batch report sections and severe rules are displayed.
No severe rules are issued
No batch report sections nor rules are displayed.
All batch report sections are displayed, but no rules are shown.
Warning
Both warning and severe rules are issued
All batch report sections and warning and severe rules are displayed.
Neither warning nor severe rules are issued
No batch report sections nor rules are displayed.
All batch report sections are displayed, but no rules are shown.
Only severe rules are issued
All batch report sections and only severe rules are displayed.
Only warning rules are issued
All batch report sections and only warning rules are displayed.
Informational
Informational, warning, and severe rules are issued
All batch report sections and severe, warning, and informational rules are displayed.
Only informational and warning rules are issued
All batch report sections and only warning and informational rules are displayed.
Only informational rules are issued
All batch report sections and only informational rules are displayed.
No informational, warning, or severe rules are issued
All batch report sections are displayed, but no rules are shown.
Only warning rules are issued
All batch report sections and only warning rules are displayed.
Only severe rules are issued
All batch report sections and only severe rules are displayed.
Only warning and severe rules are issued
All batch report sections and only warning and severe rules are displayed.
In the Maintain History field, specify whether to create and save a history of the Explain operation on the object for later use in the Compare process.
In the Histories Retained field, specify the number of history records to retain for the object, for later use in the Compare process.
When the specified number of histories is reached, the oldest entries are dropped and the newest entries are retained. The range for this option is from 0 to 99.
Select a Summary Report Sort Order:
- Object, Statement shows the report sequenced by object and statement.
- Cost (desc) shows the report sequenced by cost in descending order.
- Object, Cost (desc) shows the report sequenced by object and cost in descending order.
This report appears at the top of the output, and contains one line for each object and statement, and information such as counts for scans, joins, and so on.
- In the Generate Report Title field, specify whether to include a report title in the output.
Type a title for the report, and press Enter.
This section contains the following topic:
Related topics