Creating SQL plan cache snapshots

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

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');