What would cause performance degradation using the SQL-Ease Explain option?

Products

• 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

Issue/Introduction

Question:

Using SQL-Ease for Explain processing, performance is impacted due to the creation of 2 tables (PLAN_TABLE and DSN_STATEMENT_TABLE) and their correlating indexes in the background. This occurs upon each Explain process. How can this be minimized?

Answer:

To alleviate SQL-Ease from creating these objects each time, change the Explain options for the PLAN_TABLE option from 'ROLLBACK' to 'COMMIT', thus making the Explain tables and indexes that are auto-created for the Explain process permanent. Once the objects are permanent, then the Explain options for the PLAN_TABLE option can revert back to 'ROLLBACK'.

Environment

Release:
Component: SQE