Troubleshooting
Problem
This document describes the necessary information to aid in quicker resolution of general SQL query performance issues.
Symptom
If SQL statements are performing poorly on IBM i, collect the following data.
Cause
There can be many underlying causes of poor query performance ranging from back-leveled PTFs, missing indexes or reliance on temporary indexes, poorly constructed SQL statements, poor performing or undersized hardware, new defects, and so forth.
Resolving The Problem
WARNING: The data cannot be reviewed without a clear explanation of the problem.
A problem description of "everything is slow", lacks important details; use Step 1 to provide the required details.
A problem description of "everything is slow", lacks important details; use Step 1 to provide the required details.
STEP 1: Required Performance Questionnaire
- What needs to be fixed?
- Is there a date when this problem must be resolved? And what will happen if the problem is not resolved by then?
- How long is the job/query taking now and what is the expected runtime?
- What business function is impacted by slow performance on the system?
- How is the slowness observed or measured?
- What are the IBM i job names (or users or subsystems) responsible for processing the slow function? If you are not sure, what other information can you give us to identify the jobs in question?*
- What is the timeline of this issue? For example, when did you first notice the problem, does it happen every day or is it intermittent?
- What details are known about the query(s), procedures or applications in question? For example, stored procedure Proc1, is slow after an IPL on Sept 15th. If the query is known, please add it to the case or upload as a .txt document.
*If the issue appears to be system wide, pick an example of one important job or workload that is measurably slower and provide us with the details as a focus point.
STEP 2: Verify the IBM Must Gather tool is installed and current before proceeding!
(If the tool cannot be installed or updated, follow the Related Information section for alternate data collection instructions.)
QMGTOOLS is a light-weight, noninvasive data collector tool provided by IBM. The tool collects many logs and can take approximately 30 - 60 minutes to complete.
- CALL PGM(QMGTOOLS/QGETVER) to display the build version. If it is more than three months old, proceed with step 2.
- QMGTOOLS/CMPVER then F6 = download and update
- If the QMGTOOLS library does not exist, install the tool by using these instructions.
STEP 3: Collect the following, only if the SQL statement is still running. If the SQL statement finished, proceed to STEP 4.
- Use WRKACTJOB to locate the job number and user name of a job that is executing poorly.
- From WRKACTJOB, use option 5, option 11 to collect a screen capture of the call stack, if possible.
- Using the job name, user and number of the poor performing job, run the following SQL statements:
create table LIBRARY/TSxxxxxxAQ as
(SELECT * FROM TABLE(QSYS2.ACTIVE_QUERY_INFO(
JOB_NAME => 'JobName',
JOB_USER=> 'User',
JOB_NUMBER=>nnnnnn))) with data;
JOB_NUMBER=>nnnnnn))) with data;
create table LIBRARY/TSxxxxxxAJ as
(SELECT * FROM TABLE (QSYS2.ACTIVE_JOB_INFO
(DETAILED_INFO=>'ALL',
JOB_NAME_FILTER=>'JobName'))
WHERE JOB_NAME = 'nnnnnn/User/JobName') with data
JOB_NAME_FILTER=>'JobName'))
WHERE JOB_NAME = 'nnnnnn/User/JobName') with data
STEP 4: After the SQL query has completed, collect the following
NOTE: The light-weight QMGTOOLS/SYSSNAP command is non-invasive but can take several minutes to an hour or more to complete. While the SYSSNAP command is running, ensure you complete the performance questionnaire in Step 1.
CALL QSYS/QCMD
QMGTOOLS/SYSSNAPOUTPUT(*IFS)DAYSPRV(1)
COLLECTDFT(Y) COLSQL(Y) COLPCSNAP(Y)
CSDATA(Y) LICLOGS(Y)
PALS(Y) QHST(N) COLDEVD(N) SRVDOCS(N)
STRDATE(*CUR) ENDDATE(*CUR)
STEP 5: If the suspect query has not been identified, collect Job Watcher data spanning the next occurrence
Step 6: Upload the Data
The preferred data upload method is to use QMGTOOLS to FTP Data to IBM.
Save the LIBRARY/TSxxxxxxAQ and TSxxxxxxAJ files (from Step 3) to a savf.
CRTSAVF LIBRARY/IBMDATA
SAVOBJ OBJ(TSXXXXXXAQ TSXXXXXXAJ) LIB(LIBRARY) DEV(*SAVF) SAVF(LIBRARY/IBMDATA)
Then use QMGTOOLS to upload the data from Step 3 and Step 4:
QMGTOOLS/FTP2IBMCMD FTPTYPE(*HTTPS) IBMID(transferID) IBMPWD(transferPWD) INPUT(*IFS) CASENBR(TSxxxxxxxxx) SAVEFILE(LIBRARY/IBMDATA)
QMGTOOLS/FTP2IBMCMD FTPTYPE(*HTTPS) IBMID(transferID) IBMPWD(transferPWD) INPUT(*IFS) CASENBR(TSxxxxxxxxx) IFSFILE('/tmp/
SYSNAME_SYSSNAP_202412131800.zip')
NOTE: If you are a Blue Diamond client, use these instructions to send the data.
Reference: qmgtools-ftp-data-ibm
Alternate Methods:
Alternate Methods:
Related Information
Document Location
Worldwide
[{"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":"a8m0z0000000CKdAAM","label":"Performance-\u003EDatabase Performance"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
21 November 2025
UID
ibm16514165