DUMP_PLAN_CACHE procedure

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

Authorization: The caller must have *JOBCTL special authority or be authorized to the QIBM_DB_SQLADM function usage ID.

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.

plan-identifier

A numeric value which uniquely identifies a plan within the plan cache. When a plan-identifier is specified, sql-statement-text-filter, include-system-queries, and iasp-name are ignored. *SYSBAS and all available IASPs are searched for the plan-identifier.

sql-statement-text-filter

A character or graphic string expression that identifies a substring of text that must match for a plan to be selected. The comparison is case insensitive. Blanks, control characters, and comments embedded in the statement text are significant.

If this parameter is not provided, plans are not filtered by statement text.

include-system-queries

A character or graphic string expression that indicates whether system queries are dumped in addition to user queries.

NO: Only user queries are dumped. This is the default.
YES: System queries and user queries are dumped.

iasp-name

A character or graphic string expression that identifies the independent ASP (IASP) group to be used for finding plans to dump. An IASP must be available to access its plans.

name: The name of the IASP containing plans to dump.
*ALL: Plans from SYSBASE and all available IASPs are dumped.
*CURRENT: Only plans in the current IASP are dumped. This is the default.
*SYSBAS: Only plans in SYSBASE are dumped.

qro-hash

A character or graphic string expression containing a QRO hash value that identifies a set of plans for a specific query. *SYSBAS and all available IASPs are searched for the qro-hash. End of change

Filter interactions:

If plan-identifier is specified, only that specific plan is dumped. All other filters are ignored.
If qro-hash is specified and plan-identifier is not specified, all plans identified by the qro-hash are dumped. Other filters are ignored.
When either plan-identifier or qro-hash is specified, both *SYSBAS and all available IASP groups are searched. Both system queries and user queries may be dumped.
If neither plan-identifier nor qro-hash is specified, all plans in the plan cache matching sql-statement-text-filter and include-system-queries that are found in the IASP identified by iasp-name are dumped.

The file has the same definition as the QSYS/QAQQDBMN file. See Database monitor SQL table format for more information.

If the file already exists, the authorities are not changed. If the file does not exist, it is created, and ownership of the file is assigned to the effective user of the thread that calls DUMP_PLAN_CACHE. The public authority is set to *EXCLUDE. All other authorities are copied from the QSYS/QAQQDBMN file.

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
The PLAN_IDENTIFIER column in the QSYS2.ACTIVE_JOB_INFO, QSYS2.ACTIVE_QUERY_INFO, and QSYS2.MTI_INFO table functions
As a parameter for the QSYS2.CLEAR_PLAN_CACHE procedure
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.

The QRO hash is a 16 byte character string that uniquely identifies a query. The QRO hash is externalized in several ways:

The Visual Explain of a plan cache snapshot or a Visual Explain from the Show Statements exploration of the SQL Plan Cache
The QRO_HASH column in the QSYS2.ACTIVE_JOB_INFO, QSYS2.ACTIVE_QUERY_INFO, and QSYS2.MTI_INFO table functions
As a parameter for the QSYS2.CLEAR_PLAN_CACHE procedure
From Show Statements exploration of the SQL Plan Cache and SQL Plan Cache Snapshots
In the QQC301 column of the 3014 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);
```

Dump all plans in the current IASP that reference table TOYSTORE_SALES.

CALL QSYS2.DUMP_PLAN_CACHE(FILESCHEMA => 'SNAPSHOTS',
                           FILENAME   => 'SALES',
                           SQL_STATEMENT_TEXT_FILTER => 'TOYSTORE_SALES');

Dump all plans in *SYSBAS that reference table TOYSTORE_SALES.

CALL QSYS2.DUMP_PLAN_CACHE(FILESCHEMA => 'SNAPSHOTS',
                           FILENAME   => 'SALES',
                           SQL_STATEMENT_TEXT_FILTER => 'TOYSTORE_SALES',
                           IASP_NAME => '*SYSBAS');

Dump all plans associated with the specified QRO Hash 'B231ED32C5376A5E':

CALL QSYS2.DUMP_PLAN_CACHE(FILESCHEMA => 'SNAPSHOTS',
                          FILENAME  => 'SALES',
                          QRO_HASH => 'B231ED32C5376A5E');