|db2expln output - statement concentrator active|
In a first test I invoked db2expln with some parameters to specify the database name, the SQL statement of interest, and a file name for the output. The interesting part of the result can be see in the screenshot above. The original statement is transformed with the range values being replaced by variables ("effective statement"). That is exactly what the statement concentrator is supposed to do.
db2expln -database hltest -statement "select id,s from betw where id between 12 and 35" -output db2explnNOopt.out
|db2expln output - REOPT processing|
set current optimization profile="HLOESER"."PROFILE_BETW"
select id,s from betw where id between 11 and 36
The file can then be passed to db2expln using the stmtfile option. The result file produced by db2expln has information about each of the two statements, an indicator that both have been processed. The part of the SELECT statement is surprising as it does not include an access plan (see screenshot). But it includes good news in the form of the following message: "Section will be recompiled for REOPT processing". Thus, the optimization profile has been applied and has caused the value-specific reoptimization of the range query.
To obtain the access plan that was used, the strategy of analyzing section actuals as shown in part 2 of this series still seems to be the way to go.