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
Step 7: Send in a system snapshot by using QMGTOOLS
http://www-01.ibm.com/support/docview.wss?uid=nas8N1010353
[{"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"}]
Was this topic helpful?
Document Information
Modified date:
27 April 2021
UID
nas8N1021984