IBM Support

MustGather: Gathering a Plan Cache Snapshot

Troubleshooting


Problem

This document has instructions for gathering a Plan Cache Snapshot for debug of SQL query performance issues.

Resolving The Problem

If you have any questions regarding this document, please contact your IBM support representative.

Step 1: Create a new library
CRTLIB LIB(TSXXXXXXX) where XXXXXXX = Last 6 digits of Case number

Step 2: Create a new save file
CRTSAVF FILE(TSXXXXXXX/TSXXXXXXX) where XXXXXXX = Last 6 digits of Case number
 
Step 3: Replace MMDDYY, in the FILENAME, with today's date and then dump the plan cache using this CL command
QSYS/RUNSQL
SQL('call qsys2/dump_plan_cache(FILESCHEMA => ''TSXXXXXX'',
FILENAME=> ''SNAPMMDDYY'',                         
INCLUDE_SYSTEM_QUERIES => ''YES'',
IASP_NAME => ''*ALL'')')
COMMIT(*NONE)
 
NOTES:
  • 'TSXXXXXXX' refers to the Library that was created in STEP 1 of this document.
  • IASP_NAME is an optional filter and only available if IBM i 7.5 SF99950 level 7 or IBM i 7.4 SF99704 level 28 Db2 Group PTF is installed.
  • Single quotes are used in the QSYS/RUNSQL command; the command only works at V7R4 or later of the OS.
  • If Step 3 fails, use these alternative options
    • If the query workload runs in an ASP, you must first use SETASPGRP
      • STRSQL or Run SQL Scripts: CALL QSYS2.DUMP_PLAN_CACHE('TSXXXXXX', 'SNAPMMDDYY')
    • With November 2025 or later versions of QMGTOOLS installed, use QMGTOOLS/DMPPLANC ASPNAME(*ALL) LIBNAME(TSXXXXXX) PROPS(N) SAVFLIB(TSXXXXXX) and the then upload the TSXXXXXX/PCSAVF to the case.
Step 4: Save the data using this command
SAVLIB LIB(TSXXXXXXX) DEV(*SAVF) SAVF(TSXXXXXXX/TSXXXXXXX) TGTRLS(*CURRENT) DTACPR(*MEDIUM)
NOTE: 'TSXXXXXXX' refers to the Library that was created in STEP 1 of this document.
 
Step 5:  Instructions for Sending Data to IBM i Support
     The preferred data upload method is QMGTOOLS
 
Step 6: (OPTIONAL) Send in a system snapshot using QMGTOOLS
 

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CI9AAM","label":"Performance-\u003EMustGather"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.4.0;7.5.0"}]

Document Information

Modified date:
23 November 2025

UID

nas8N1021984

Page Feedback