DUMP_PLAN_CACHE procedure

The DUMP_PLAN_CACHE procedure creates a database monitor file (snapshot) from the plan cache.

Authorization: *JOBCTL special authority or QIBM_DB_SQLADM function usage is required.

Read syntax diagramSkip visual syntax diagramDUMP_PLAN_CACHE(FILESCHEMA => library-name,FILENAME => file-name,PLAN_IDENTIFIER => plan-identifier)
The schema is QSYS2.
library-name
A character or graphic string expression that identifies the name of the library containing file-name. The special value of *CURLIB can be used.
file-name
A character or graphic string expression that identifies the name of the resulting database monitor file. If the file does not exist, it is created. If the file exists, the new plan cache snapshot will be appended to it.
Start of changeplan-identifierEnd of change
Start of changeA numeric value which uniquely identifies a plan within the plan cache. End of change

The file has the same definition and authorities as the QSYS/QAQQDBMN file. Start of changeIf the file does not exist, it is created with the public authority set to *EXCLUDEEnd of change. See Database monitor SQL table format for more information.

Start of changeIf plan-identifier is specified, only that specific plan is dumped. If plan-identifier is not specified, all user-initiated plans in the plan cache are dumped.End of change

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

Start of change

Notes

The plan identifier is a unique number that is generated when the plan is optimized. The plan identifier is externalized in several ways:

  • The statement number of a Visual Explain of a plan cache snapshot or a Visual Explain from the Show Statements exploration of the SQL Plan Cache
  • From Show Statements exploration of the SQL Plan Cache and SQL Plan Cache Snapshots
  • In the QQUCNT column of the 1000 record of a plan cache snapshot file.
  • As information passed to a Query Supervisor exit program.
End of change

Example

  • Dump the plan cache to a database performance monitor file called SNAPSHOT1 in library SNAPSHOTS.
    CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS','SNAPSHOT1');
  • Start of changeDump a specific plan to a database performance monitor file called QUERY1 in library SNAPSHOTS.
    CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS','QUERY1', 126783);
    End of change