Troubleshooting
Problem
This purpose of this document is to explain how to set up Targeted Tracing (TT) by calling the QQQOOOCACH program. The result is a DBOP and QEO trace of a query executing in its natural environment.
NOTE: Calling the QQQOOOCACH program directly is only supported when a support technician, from the IBM i Global Support Center (iGSC), requests data. Access to the plan cache is supported via Plan Cache Services: https://www.ibm.com/docs/en/i/7.4?topic=services-plan-cache.
Environment
REQUIREMENTS
• The QRO Hash Value(s) must be known
• 7.5 PTF SJ03559 or 7.4 PTF SJ03557
• A user will need *ALLOBJ authority to access the targeted trace in the QIBM/UserData/OS400/SQE path.
Resolving The Problem
Trace Setup
- CALL PGM(QQQOOOCACH) PARM('F:S:TSxxxxxx') where xxxxxx is the last six digits of your case number.
Start the Trace
The QRO hash value of 11111111 (in the following examples) will be replaced by the actual QRO hash value provided by your support representative. Only one of the options below will be followed.
- For a fully instrumented trace, use CALL PGM(QQQOOOCACH) PARM('B:S:11111111:1:2:1:0')
- For a fully instrumented trace, for intermittent performance issues, use CALL PGM(QQQOOOCACH) PARM('B:S:11111111:1:4:0:0')
- For cases that require tracing on multiple hash values (up to a maximum of three different hashes), use CALL PGM(QQQOOOCACH) PARM('B:S:11111111:1:2:1:0:22222222:33333333')
NOTES: The trace will automatically collect when query 11111111 enters the system.
Ending the Trace
The trace will end automatically. Once the trace is collected, the iteration counter will decrement and tracing will end when the counter reaches zero. The trace will survive an IPL.
Viewing the status of the trace and ending the trace:
Use CALL PGM(QQQOOOCACH) <enter> B:D <enter> to see if the trace has or has not been collected and how many iterations are left, if any. There is only one iteration count value and it will be decremented when any one of the hash values are encountered. Although the trace will automatically end after the counter is decremented, you can use CALL PGM(QQQOOOCACH) PARM('B:E') to stop tracing on all QRO HASH values.
Finding the trace output:
Once the SQL query completes, check for the trace output in the IFS.
Use WRKLNK '/QIBM/UserData/OS400/SQE/TSxxxxxx'
The name of the sub-directory will match the User Name and Job Number that executed the query.
NOTE:
There will be at least two or more objects in the /QIBM/Userdata/OS400/SQE/TSxxxxxx/UserName_jobNumber directory; all of the objects are needed.
Uploading the Data:
The preferred data upload method is QMGTOOLS: FTP Data to IBM
Examples:
Command to zip the subdirectory: QSH CMD('jar -cf /tmp/TTData.zip /QIBM/Userdata/OS400/SQE/TSxxxxxx/UserName_jobNumber')
Then use QMGTOOLS to upload:
QMGTOOLS/FTP2IBMCMD FTPTYPE(*HTTPS) IBMID(transferID) IBMPWD(transferPWD) INPUT(*IFS) CASENBR(TSxxxxxxxxx) IFSFILE('/tmp/TTData.zip')
Related Information
[{"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"}]
Historical Number
610515908
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
Performance->Database Performance
Software version:
All Versions
Operating system(s):
IBM i
Document number:
685069
Modified date:
21 April 2025
UID
nas8N1011355
Manage My Notification Subscriptions