Troubleshooting
Problem
This document explains how to properly copy records from a system-wide DBMON or plan cache snapshot to narrow down the data. See this technote for more information on database monitor impacts.
Resolving The Problem
This document explains how to subset data from a system-wide DBMON or plan cache snapshot. To prevent any problems with viewing the data, use the GUI options currently available in "Show Statements" from within IBM i Navigator and Access Client Solutions. If that cannot be used, it is important to follow these steps so that the new file matches the record format of the model file, QSYS/QAQQDBMN.
To narrow down a DBMON or plan cache snapshot file for a specific job or group of jobs, on the operating system command line, type the following:
| STRSQL or RUNSQL | |
|
To subset based on a generic job name:
CREATE TABLE library/QpadevJobs AS (SELECT * FROM library/ORIGINAL_SNAPSHOT WHERE (QQJNUM, QQUCNT) in (SELECT QQJNUM, QQUCNT FROM library/ORIGINAL_SNAPSHOT WHERE qqjob like 'QPADEV%' )) WITH DATA INSERT INTO library/QpadevJobs (SELECT * FROM library/ORIGINAL_SNAPSHOT WHERE qqrid=3018)
|
|
|
To subset based on QZDA* jobs and a specific current user ID:
CREATE TABLE library/QZDAJobs AS (SELECT * FROM library/ORIGINAL_SNAPSHOT WHERE (QQJNUM, QQUCNT) in (SELECT QQJNUM, QQUCNT FROM library/ORIGINAL_SNAPSHOT WHERE qqjob like 'QZDA%' and QVC102 = 'CurrentUserID' )) WITH DATA INSERT INTO library/QZDAJobsx (SELECT * FROM library/ORIGINAL_SNAPSHOT WHERE qqrid=3018)
|
|
|
To subset based on a specific job number:
CREATE TABLE library/Jobxxxxxx AS (SELECT * FROM library/ORIGINAL_SNAPSHOT WHERE (QQJNUM, QQUCNT) in (SELECT QQJNUM, QQUCNT FROM library/ORIGINAL_SNAPSHOT WHERE qqjnum = 'xxxxxx' )) WITH DATA Where xxxxxx is the job number you want to use to subset the data. INSERT INTO library/Jobxxxxxx (SELECT * FROM library/ORIGINAL_SNAPSHOT WHERE qqrid=3018)
|
|
|
To subset a Snapshot on a particular QRO Hash value (replace xxxxxxxx with an actual hash value):
CREATE TABLE library/Extract AS
(SELECT * from library/ORIGINAL_SNAPSHOT WHERE (qqjnum, qqucnt) in (SELECT qqjnum, qqucnt FROM library/ORIGINAL_SNAPSHOT WHERE qqrid=3014 and qqc83 = 'xxxxxxxx')) WITH DATA INSERT INTO library/Extract (SELECT * from library/ORIGINAL_SNAPSHOT WHERE qqrid = '3018' )
|
NOTE: A plan cache snapshot is NOT a trace file like a DBMON is. When sub-setting a plan cache snapshot, use caution when applying selection criteria. The snapshot includes access plans, when they were created, job information from a worst run, and job information from the creator of the plan. It is possible that a plan could be created by job X yesterday, used by job Y today, and have the longest runtime with job Z
If SQL commands are not available, an alternate approach is to use the following CL commands.
|
CPYF FROMFILE(library/SNAPSHOT1)
TOFILE(library/JOBxxxxxx) CRTFILE(*YES) INCREL((*IF QQJNUM *EQ 'xxxxxx') (*OR QQRID *EQ 3018)) Where xxxxxx is the job number you want to use to subset the data.
|
|
| Or | |
| CPYF FROMFILE(library/SNAPSHOT1) TOFILE(library/QZDAjobs) CRTFILE(*YES) INCREL((*IF QQJOB *EQ 'QZDASOINIT') (*OR QQRID *EQ 3018)) |
To analyze the snapshot, open up the SQL Performance Center in Access Client Solutions (also known as ACS). Click File from the toolbar and then Import to import the newly created file.
Related Information
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Db2 for i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
452904509
Was this topic helpful?
Document Information
Modified date:
11 May 2021
UID
nas8N1014329