DB2 Battle: Optimization Profile vs. Statement Concentrator (Part 3)

db2expln output - statement concentrator active

In February I had fiddled with optimization profiles and REOPT working against the statement concentrator (part 2 is here). Today I wanted to give an interesting (hopefully) update. The tool db2expln can be used to describe the access plan for static and dynamic SQL statements. It works in a different way than db2exfmt and visual explain (part of tools like Data Studio and IBM Data Server Manager). Thus, I was eager to see whether it could help to find out whether my optimization profile was applied.


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

For the next round I needed to activate my optimization profile before the query was evaluated. Hence I created a small input file with two statements:

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.