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.
REQUIREMENTS
• The QRO Hash Value(s) must be known
• A user will need *ALLOBJ authority to access the targeted trace in the QIBM/UserData/OS400/SQE path.
Trace Setup
The QRO hash value of abcd1234 (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:abcd1234:1:2:1:0')
- For a fully instrumented trace, for intermittent performance issues, use CALL PGM(QQQOOOCACH) PARM('B:S:abcd1234: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:abcd1234:1:2:1:0:22222222:33333333')
NOTES: The trace will automatically collect when query abcd1234 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. In this example, the bold number 1 is the counter: 'B:S:abcd1234:1:2:1:0'. 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/'
The name of the sub-directory will match the User Name and Job Number that executed the query.
There will be at least two or more objects in the /QIBM/Userdata/OS400/SQE/UserName_jobNumber directory; all of the objects are needed.
Uploading the Data:
Examples:
Command to zip the subdirectory: QSH CMD('jar -cf /tmp/TTData.zip /QIBM/Userdata/OS400/SQE/UserName_jobNumber')
Then use QMGTOOLS to upload:
QMGTOOLS/FTP2IBMCMD FTPTYPE(*HTTPS) IBMID(transferID) IBMPWD(transferPWD) INPUT(*IFS) CASENBR(TSxxxxxxxxx) IFSFILE('/tmp/TTData.zip')
[{"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"}]