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 Start of changebased on the specified category optionEnd of change.

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

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
Start of changecategoryEnd of change
Start of changeA 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.
End of change
The file has the same definition as the QSYS/QAQQDBMN file. Start of changeIf the file does not exist, it is created with the public authority set to *EXCLUDE. End of changeSee 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);