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.
Once the QRO hash value of the problematic query has been identified (11111111 in this example), your support representative will request one of the following from an IBM i command line:
- 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. Once the trace is collected, the iteration counter will decrement and all tracing will automatically 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' or WRKLNK '/tmp/sqe' to find the sub-directory that contains the trace data.
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/UserName_jobNumber or /tmp/SQE/UserName_jobNumber subdirectory; all of the objects are needed. Save and send IBM both the DBOPTRACE and QEO stream files to your support representative for analysis.
NOTE: If the SI726** PTFs, in the Environment section of this document, are applied, the trace output will be in /QIBM/UserData/OS400/SQE. A user will need *ALLOBJ authority to access the targeted trace in the QIBM/UserData/OS400/SQE path.
Uploading the Data:
Examples: QMGTOOLS/FTP2IBMCMD FTPTYPE(*HTTPS) IBMID(transferID) IBMPWD(transferPWD) INPUT(*IFS) CASENBR(TSxxxxxxxxx) IFSFILE('/QIBM/UserData/OS400/SQE/QSECOFRIBM_282190/DBOPTTRACE_20220803130159656.DBOP')
[{"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"}]