Why Plan Analyzer explain reflects that the SQL would be accelerated for static SQL

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

A Plan Analyzer for Db2 for z/OS (PPA) enhanced explain to obtain access path of Db2 package created from a COBOL program
that contains static SQL indicates statements will be accelerated when the explain is run using target subsystem that is
configured to accelerate dynamic SQL instead of indicating the actual access path that will be taken because the static SQL
will run natively. Static SQL is not eligible for acceleration unless a package containing the static SQL is explicitly bound
with the QUERYACCELERATION parameter, which is not the case with this example.

At this time in our environment only dynamic SQL qualifies for acceleration unless a package containing static SQL is bound
with the QUERYACCELERATION parameter. Since this package BIND did not include the parameter the Db2 optimizer will
not determine that the SQL can run on IDAA; therefore the access path AQRYTB is WRONG. 

Why the explain reflects that the SQL would be accelerated when this is static SQL that will not be accelerated.
Is there a parameter or something to force traditional access path analysis of packages that contain static SQL that do
not include the QUERYACCELERATION BIND parameter?

Resolution

A couple of things based on the Explain output provided: 

1) An EXPLTYPE = (CURRENT) is requested in the Explain control cards. This should read the rows in
the PLAN_TABLE, etc. except this is being overwritten to be EXPLTYPE = (FUTURE).
There is a message in the output indicating this. 

2) Since a FUTURE explain is being done, statements are passed to the Db2 optimizer as dynamic statements (instead of static) so
believe this why the access path can now show as AQRYTB. If you wish to verify this, you could change PLANTAB = (ROLLBACK)
to PLANTAB = (COMMIT) and then browse the PLAN_TABLE after the explain completes and expect you will find the rows inserted
by the PPA Explain show an 'A' in the ACCESSTYPE column. This value is determined by Db2. 

3) The reason the EXPLYTYPE is overridden is due to AUTO HVersion @FUTRDBA being used as specified in the
DATABASE = (AUTO,@FUTRDBA,ssid) control card. The parms from the HVersion @FUTRDBA override some control cards.
To see the options associated with this HVersion, use option AH from the PPA main menu and use B to browse @FUTRDBA.