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 = Case number

Step 2: Create a new save file.
CRTSAVF FILE(TSXXXXXXX/TSXXXXXXX) where XXXXXXX = Case number
Step 3: Set the auxiliary storage group.
SETASPGRP ASPGRP(asp_grp)  where asp_grp is the name of the auxiliary storage group
NOTE:
If no independent ASP (IASP) is being used, skip this step.  Alternatively, collect the snapshot from System i Navigator.
Step 4: Dump the Plan Cache using this CL command.
RUNSQL SQL('call qsys2/dump_plan_cache(''TSXXXXXXX'',''SNAPSHOT1'')')
NOTES:
Single quotes are used in this command; if the command does not work for you, try copying the RUNSQL command and pasting it onto your OS/400 command line.
'TSXXXXXXX' refers to the Library that was created in STEP 1 of this document.

Step 5: 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 6:  Instructions for Sending Data to IBM i Support
The preferred data upload method is QMGTOOLS: FTP Data to IBM

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]

Document Information

Modified date:
27 April 2021

UID

nas8N1021984

Page Feedback