DUMP_PLAN_CACHE_TOPN procedure

The DUMP_PLAN_CACHE_TOPN procedure creates a database monitor file (snapshot) from the plan cache containing the user-initiated queries based on the specified category option.

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

Read syntax diagramSkip visual syntax diagramDUMP_PLAN_CACHE_TOPN(FILESCHEMA => library-name,FILENAME => file-name,TOPN => topn_number ,CATEGORY => category)
The schema is QSYS2.
library-name
A character or graphic string expression that identifies the name of the library containing file-name.
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 properties will be appended to it.
topn-number
An integer expression representing the number of queries to dump
category
A character or graphic string expression that identifies the type of top N queries to be dumped.
CPU
Dump the TOPN queries with the most accumulated CPU time.
DATABASE READS
Dump the TOPN queries with the most accumulated database read I/Os. This includes both synchronous and asynchronous reads.
RUNTIME
Dump the TOPN queries with the longest accumulated runtime. This is the default.
STORAGE
Dump the TOPN queries with the largest temporary storage usage.
The file has the same definition 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.

Example

  • Capture the 20 queries with the largest elapsed time and dump the details into a snapshot file named SNAPSHOTS/TOPN121413
    CALL QSYS2.DUMP_PLAN_CACHE_TOPN('SNAPSHOTS', 'TOPN121413', 20);