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?
- To what extent has the performance of the business function changed and 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 and does it happen every day?
- 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 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 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 fully qualified job name from step 1, collect three active dumps, spaced several minutes apart
Issue the following on the IBM i command line: CALL PGM(QQQOOOCACH) PARM('A:D:nnnnnn/User/JobName')
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/SYSSNAP OUTPUT(*IFS) DAYSPRV(1) COLLECTDFT(Y) COLSQL(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 of the problem
STEP 6: Instructions for Sending Data to IBM i Support
Active Dumps, from STEP 3, will be in the IFS.
To find the output, use DSPJOB to record the user and job number of your interactive job (this information is needed to locate the output of the active dump).
Use WRKLNK '/QIBM/UserData/OS400/SQE' from the IBM i command line.
If the active dump successfully captured a query, then you have a subdirectory that contains your interactive user name and job number as part of the name (/SQE/USERnnnnnn).
Copy the name of the subdirectory and do the following.
SYSSNAP output, from STEP 4, will be in the IFS under the /tmp/SystemName_SYSSNAP_timestamp.zip directory.
Use QMGTOOLS /FTP2IBMCMD to send the .zip data from STEP 3 & STEP 4.
Note: you need to set up a transfer id (and optionally store it in QMGTOOLS) to transfer the data via QMGTOOLS.
Example: QMGTOOLS/FTP2IBMCMD FTPTYPE(*HTTPS) IBMID(transferID) IBMPWD(transferPWD) INPUT(*IFS) CASENBR(TSxxxxxxxxx) IFSFILE('/tmp/SYSNAME_SYSSNAP_202310272037.zip')
or
QMGTOOLS/FTP2IBMCMD FTPTYPE(*HTTPS) IBMID(transferID) IBMPWD(transferPWD) INPUT(*IFS) CASENBR(TSxxxxxxxxx) IFSFILE('/QIBM/UserData/OS400/SQE/USERnnnnnn/DBOPPLANCACHEDUMP20231124105934381.DBOP'))
NOTE: If you are a Blue Diamond client, use these instructions to send the data
Related Information
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"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:
16 January 2024
UID
ibm16514165