Creating SQL plan cache snapshots

From within the SQL Performance Center, you may create a snapshot of the current plan cache contents by selecting New->Plan Cache Snapshot from the File menu.

Unlike the snapshot option under Show Statements, it allows you to create a snapshot without having to first view the queries.

SQL Plan Cache Snapshot

The same filtering options are provided here as on the Show Statements screen.

The stored procedure, QSYS2.DUMP_PLAN_CACHE, provides the simplest, programmatic way to create a database monitor file output (snapshot) from the plan cache. The DUMP_PLAN_CACHE procedure takes two parameters, library name and file name, for identifying the resulting database monitor file. If the file does not exist, it is created. For example, to dump the plan cache to a database performance monitor file in library QGPL:

CALL QSYS2.DUMP_PLAN_CACHE('QGPL','SNAPSHOT1');