Why the explain reflects that the SQL would be accelerated for static SQL?

Document ID : KB000006266
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

An 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 I 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 I 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,TDB2) 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.