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.
- 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.
- plan-identifier
- A numeric value which uniquely identifies a plan within the plan cache.
The file has the same definition and authorities as the QSYS/QAQQDBMN file. If the file does not exist, it is created with the public authority set to *EXCLUDE. See Database monitor SQL table format for more information.
If 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.
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.
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.
Example
- Dump the plan cache to a database performance monitor file called SNAPSHOT1 in library
SNAPSHOTS.
CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS','SNAPSHOT1');
- Dump a specific plan to a database performance monitor file called QUERY1 in
library
SNAPSHOTS.
CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS','QUERY1', 126783);