Different Explain results from Plan Analyzer and DSNTIAUL/SPUFI.

Products

• RC Compare for DB2 for z/OS
• Bind Analyzer for DB2 for z/OS
• SQL-Ease for DB2 for z/OS
• SYSVIEW Performance Management Option for DB2 for z/OS
• Plan Analyzer for DB2 for z/OS
• Subsystem Analyzer for DB2 for z/OS
• Database Analyzer for DB2 for z/OS
• Fast Unload for DB2 for z/OS
• Fast Check for DB2 for z/OS
• Fast Index for DB2 for z/OS
• Rapid Reorg for DB2 for z/OS

Issue/Introduction

Description:

Plan Analyzer Explain of non-catalog SQL shows a different access path than the result of Explain ran through DSNTIAUL or SPUFI. Why is that?

Solution:

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.

Environment

Release:
Component: PPADB2