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

Products

• SQL-Ease for DB2 for z/OS
• Plan Analyzer for DB2 for z/OS

Issue/Introduction

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?

 

Resolution

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'.