IBM Support

SQE full dbop trace

Question & Answer


Question

How do I collect a full DBOP trace?

Answer

This example is set up to do a good run and failing run within these steps.

Step 5 is the run of the failing example and step 11 is the working example.


First step ... create/setup a new QAQQINI file.

Do you have one in QUSRSYS?
If so, use that as the FROMLIB on the following CRTDUPOBJ instead of QSYS.

1) CRTDUPOBJ OBJ(QAQQINI)    
            FROMLIB(QSYS)    
            OBJTYPE(*FILE)  
            TOLIB(-yourlib-)  
            DATA(*YES)
            TRG(*YES)   

Note: You do NOT want to use QUSRSYS as the -yourlib- as the QAQQINI file in QUSRSYS affects every query on the system. Use a different library for -yourlib- in the instructions.

2) STRSQL

insert into -yourlib-.qaqqini                            
       values('MESSAGES_INFORMATIONAL','*FULL',null),  
             ('MESSAGES_FINAL_PLAN'   ,'*FULL',null),  
             ('QUERY_TREE_DUMP'       ,'*DBPM',null),  
             ('QUERY_TREE_DUMP_AT'    ,'*CLOSE',null),
             ('QUERY_INSTRUMENTATION' ,'*FULL',null)

update -yourlib-.qaqqini                                
   set qqval  = '*FORCE'                              
 where qqparm = 'REOPTIMIZE_ACCESS_PLAN'

Second step ... the failing case.

The following commands need to be executed in the recreate job before running the query

3) CHGJOB LOG(4 00 *SECLVL) LOGCLPGM(*YES)

4) CHGQRYA QRYOPTLIB(-yourlib-)

5) Run the failing example

6) DSPJOBLOG * *PRINT

7) The IFS location of the tracing output can be found with a CPCA083 message.
Example 1:

Message ID . . . . . . :   CPCA083       Severity . . . . . . . :   00      
Message type . . . . . :   Completion                                      
Date sent  . . . . . . :   05/29/02      Time sent  . . . . . . :   11:07:14
                                                                           
Message . . . . :   Directory created.                                      
Cause . . . . . :   Directory/QIBM/UserData/OS400/SQE/<USER_JOBNUMBER>/ was successfully    
  created.
Example 2:
Message ID . . . . . . :   CPCA083       Severity . . . . . . . :   00      
Message type . . . . . :   Completion                                      
Date sent  . . . . . . :   07/26/12      Time sent  . . . . . . :   10:51:59
                                                                           
Message . . . . :   Directory created.                                      
Cause . . . . . :   Directory /TMP/SQE/ERRORS/<USER_JOBNUMBER>/ was successfully    
  created.
The "/QIBM/UserData/OS400/SQE/<USER_JOBNUMBER>/" is used later in step 16.
- This IFS path is just an example. Use the actual path in your CPCA083 message.

8) Sign off

Third step ... the working case. (if applicable)

9) Sign on to System i

10) CHGQRYA QRYOPTLIB(yourlib)

11) Run the working example

12) DSPJOBLOG * *PRINT

13) The IFS location of the tracing output can be found with a CPCA083 message.
Example 1:

Message ID . . . . . . :   CPCA083       Severity . . . . . . . :   00      
Message type . . . . . :   Completion                                      
Date sent  . . . . . . :   05/29/02      Time sent  . . . . . . :   11:07:14
                                                                           
Message . . . . :   Directory created.                                      
Cause . . . . . :   Directory/QIBM/UserData/OS400/SQE/<USER_JOBNUMBER>/ was successfully    
  created.
Example 2:
Message ID . . . . . . :   CPCA083       Severity . . . . . . . :   00      
Message type . . . . . :   Completion                                      
Date sent  . . . . . . :   07/26/12      Time sent  . . . . . . :   10:51:59
                                                                           
Message . . . . :   Directory created.                                      
Cause . . . . . :   Directory /TMP/SQE/ERRORS/<USER_JOBNUMBER>/ was successfully    
  created.
The "/QIBM/UserData/OS400/SQE/<USER_JOBNUMBER>/" is used later in step 17.
- This IFS path is just an example. Use the actual path in your CPCA083 message.

Fourth step ... gather up the IFS data on your IBM i

14) CRTSAVF qgpl/sf000000b (replace 000000 with the last 6 digits of the Case number)
15) CRTSAVF qgpl/sf000000g (replace 000000 with the last 6 digits of the Case number)
Example: use '456789' for 000000 if your Case number is TS123456789

16) SAV DEV('/qsys.lib/qgpl.lib/sf000000b.file')
    OBJ(('-from step 7 above-'))
    SUBTREE(*ALL)

17) SAV DEV('/qsys.lib/qgpl.lib/sf000000g.file')
    OBJ(('/-from step 13 above-'))
    SUBTREE(*ALL)



Fifth step ... get savefile(s) on your PC

- From a command (DOS) window:

18) FTP yourIBMi, and press the Enter key (where "yourIBMi" is the name of your system).
19) Log in with your operating system user profile and password.
20) Type bin, and press the Enter key.
21) Type cd QGPL and press the Enter key.
22) Type get SF000000b.savf and press the Enter key. (from step 16)
23) Type get SF000000g.savf and press the Enter key. (from step 17)
24) quit and press the Enter key

Sixth step ... FTP savf to our IBM site

Enhanced Customer Data Repository (ECuRep)

(note: Upload is for: OS/400)


Seventh step ... JOBLOGs.

Send in the DSPJOBLOG from step 6 and 12 via Access Client Solutions (ACS) as .txt file.
TechNote: How to download spool files - Small, Medium, Large - and in the correct ASCII format.

[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Db2 for i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]

Historical Number

N1019777

Document Information

Modified date:
30 October 2024

UID

nas8N1019777

Page Feedback