Different Explain results from Plan Analyzer and DSNTIAUL/SPUFI.

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

Plan Analyzer for Db2 for z/OS (PPA) Explain of non-catalog SQL shows a different access path than the result of Explain ran through DSNTIAUL or SPUFI. 

Resolution

A possibility that could be causing different access paths to be generated is the Parallelism Degree option in PPA for non-catalog sources.
If this options equals 'A' or blank then we do a SET CURRENT DEGREE = 'ANY' before the dynamic explain.

Likewise, if explain option Parallelism Degree equals 1 we do a SET CURRENT DEGREE = '1' before the dynamic explain.

This option can be specified on the 'Explain Options' panel. Defining e.g.

Parallelism Degree ===> A

generates this Explain control card:

DEGREE = (ANY)

Determine which explain option degree you have set in PPA and then reattempt your test by first specifying the appropriate SET CURRENT DEGREE statement before you execute the dynamic explain statement in e.g. SPUFI. The explain results in SPUFI should then match up to the same results in PPA.

One other possibility for different explain results is ISOLATION for non-catalog explain sources. The appropriate isolation must be specified by the user via explain option Non-Catalog Isolation. The default is CS. Behind the scenes PPA explain will execute the dynamic explain under the requested bound isolation package.