CLEAR_PLAN_CACHE

CLEAR_PLAN_CACHE procedure

  >>-CLEAR_PLAN_CACHE --()----------------------------------><

The schema is QSYS2.

This procedure is used primarily in performance test and QA environments. It provides database performance analysts with a way to create a consistent environment from which to evaluate potential database performance changes. The procedure will clear all plans in the system SQL Plan Cache that exist at the time the procedure is run. Besides clearing the plan information, any Maintained Temporary Indexes (MTIs) not currently in use by a query will be deleted as part of the clear. SQL queries run while the CLEAR_PLAN_CACHE procedure is running may have their plans removed, but the queries themselves will not incur a failure related to plan removal. Any SQL queries run after the clear is complete will begin to repopulate the plan cache.

The time the CLEAR_PLAN_CACHE procedure takes to run will vary depending on the plan cache size. To avoid tying up an interactive job, it is recommended to submit the procedure in a background job using a combination of the Submit Job (SBMJOB) and Run SQL (RUNSQL) CL commands.

Authorization: The CLEAR_PLAN_CACHE procedure requires that the authorization ID associated with the statement has *JOBCTL special authority or QIBM_DB_SQLADM function usage.

Errors: The procedure will fail with SQL0443 and SQL0552 if the caller does not have the required authority.

Example:

SBMJOB CMD(RUNSQL SQL('CALL QSYS2.CLEAR_PLAN_CACHE()') COMMIT(*NONE) NAMING(*SQL))