Troubleshooting
Problem
Temporary storage analysis tools ACTIVE_QUERY_INFO, SYSTMPSTG or RMTSTG show large amounts of temporary storage consumed in *DATABASE buckets 8, 9, 11, and 14.
This document describes what documents to collect to send to IBM to help determine the reason for the storage use.
If you would like to review and investigate without opening a Case to IBM i or you need immediate relief you should use Understanding and investigating temporary storage growth in *DATABASE buckets 8, 9, 11, and 14.
Symptom
System is consuming a large amount of temporary storage and initial analysis points to database activity.
Environment
TechNote applies to all support IBM i releases.Diagnosing The Problem
Effective with IBM i 7.2, this document should be used if the QSYS2/SYSTMPSTG (Temporary Storage) view shows excessive temporary storage (beyond 10 percent of the system ASP) allocated in these DATABASE buckets used by SQE (SQL Query Engine):- 8 Segment Cache (Segment types: 2135, 2136, 2137, 2138, 20B3 )
- 9 SQE Heap (Segment Types: 20AA, 20AB, 20AC )
- 11 DS SQE LOB (Segment Types: 214A )
- 14 DSI SQE MTI (Segment Types: 2134 )
Excessive temporary storage would be when the cumulative DATABASE components are beyond 10 percent of the system ASP.
Refer to the following document that explains how you can prevent individual jobs from allocating excessive temporary storage: Establish SQL temporary storage limits
For issues concerning database temporary storage issues, this document includes the recommended data collection for debug.
It is meant as a starting point for document collection.
Temporary Storage issues specific MustGather information
If you encounter a journal issue, collect the following information using either method A or method B as it is related to the issue:
| Method A - QMGTOOLS |
The below steps assume you have the latest version of QMGTOOLS. Build date of 04/02/22 or later.
IBM i 7.3 PTFs: MF67418/SI73319IBM i 7.4 PTFs: MF67549/SI73772
MustGather: How to obtain and Install QMGTOOLS and keep it current
Data collection:
1. CHGJOB LOG(4 00 *SECLVL) LOGCLPGM(*YES)
2. ADDLIBLE QMGTOOLS
3. GO MG
4. Option 13 - Check IBM for updated QMGTOOLS
5. Option 6 - Database menu
6. Option 6 - Temporary Storage
You should see the following:
Use the default of *NONE for the JOB keyword if you aren't sure which job(s) are causing the issue.
Specify the job information if your initial analysis points to a specific job.
iASP Name (IASPNAME) - If the query is running against data in an iASP (independent auxiliary storage pool), put the iASP name
Plan Cache Dumps (PCDUMP) - Collect all plan cache dumps, which include:D:D - DUMP DETAILED PLAN CACHE SUMMARY
I - DUMP TEMP INDEX INFORMATION
U - SUMMARY OF SQE TEMP STORAGE USE
This can run for a while after you press Enter. (several minutes)
Status messages are provided to monitor progress (which dump we are currently working on)
f. Put in full Case number and specify FTP option.
Anonymous is no longer allowed (GDPR) - Supported FTP options (search for FTP Type)
NOTE: if *NO is used for FTP then you must manually send data to IBM via MustGather: Instructions for Sending Data to IBM i Support
| Method B - Manual Collection |
If you are using Method B to send in data, ALL data needs be sent in as .txt and not .PDF. (except for savefiles)
Step 1: Collect the following data.
NOTE: If independent ASPs are being used, you will need to use the SETASPGRP command before performing the following steps.
1. Create a new library to be used in subsequent steps
On an operating system command line, type the following:
CRTLIB LIB(TSxxxxxx) where xxxxxx = last 6 digits of your Case number
2. Collect output from WRKSYSSTS
WRKSYSSTS output(*PRINT)
3. Collect a Detailed Dump of the Plan Cache
CALL PGM(QQQOOOCACH) PARM('F:TSxxxxxx/DDFILE:D:D')
4. Collect an Active Dump of the Plan Cache
CALL PGM(QQQOOOCACH) PARM('F:TSxxxxxx/ADFILE:A:D')
5. Collect a Heap Summary:
CALL PGM(QQQOOOCACH) PARM('F:TSxxxxxx/SFILE:S')
6. Collect a MTI Summary:
CALL PGM(QQQOOOCACH) PARM('F:TSxxxxxx/IFILE:I')
7. Collect a Plan Cache Snapshot (NOTE: Alternatively, collect the snapshot from System i Navigator.)
a. STRSQL
b. call qsys2/dump_plan_cache('TSxxxxxx', 'SNAPSHOT1')
8. Collect a dump with a summary of SQE temp storage use
a. SBMJOB CMD(CALL PGM(QQQOOOCACH) PARM('F:TSxxxxxx/UFILE:U')
- IBM i 7.3 PTFs: MF67418/SI73319 IBM i 7.4 PTFs: MF67549/SI73772
9. The SQL Plan Cache may now be cleared in an attempt to free storage. This step is optional, but may avoid the need to IPL to release temporary storage.
From a command line, type the following:
CALL PGM(QQQOOOCACH) PARM('C:Y')
Step 2: Send the data to IBM i Global Support Center for review
1. Locate the QPDSPSTS print file that contains output from WRKSYSSTS
2. CRTSAVF TSxxxxxx/IBMdata
3. CRTSAVF TSxxxxxx/PCdump
4. Locate the QPRINT file from Step 1, number 8. The location of the data, in the IFS, is included in the QPRINT output and will be used on the OBJ SAV parameter of this command:
SAV DEV('/qsys.lib/tsxxxxxx.lib/pcdump.file') OBJ(('/QIBM/UserData/OS400/SQE/userNAMEjobNumber/*.*')) DTACPR(*YES)
or if your PTF level is before May 2020:
SAV DEV('/qsys.lib/tsxxxxxx.lib/pcdump.file') OBJ(('/tmp/sqe/userNAMEjobNumber/*.*')) DTACPR(*YES)
5. SAVLIB LIB(TSXXXXXX) DEV(*SAVF) SAVF(TSXXXXXX/IBMDATA)
Note: If you do not find the specified subdirectory under QIBM/UserData/OS400/SQE, run the WRKLNK '/QIBM/UserData/OS400/SQE' command and select Option 2 (Edit) in front of the SQE directory. (If your PTF level is before May 2020, also look in WRKLNK '/tmp/sqe')
In the resulting list-display, look for similarly named (USERNAME_JOBNUMBER...) subdirectories with a current date (or sort by date by putting the cursor on the Changed date column and pressing F16 (Sort)).
Related Information
Historical Number
N1022269
Was this topic helpful?
Document Information
Modified date:
19 March 2024
UID
nas8N1022269