IBM Support

MustGather: QQ/CQE Query Optimizer Functional Issues

Troubleshooting


Problem

This document describes the necessary information that needs to be collected to aid in quicker resolution of interactive QQ/CQE query optimizer functional issues.
Caution: This document will request a DBMON trace.  DBMON traces can negatively impact system performance. If possible, narrow the scope of the issue and start the DBMON shortly before the issue is seen and over specific or generic job names instead of using JOB(*ALL).  For further detail on the impact of taking a DBMON trace refer to the following document: https://www.ibm.com/node/882914

Resolving The Problem

For issues concerning CQE optimizer functional issue, this document includes the recommended data collection for debug. This is by no means all inclusive; however, it is meant as a starting point for document collection. Examples of CQE optimizer functional issues include, but are not limited to:

message MCH3203 T/QQQIMPLE
message MCH3601 T/QQQQUERY

Interactive QQ/CQE optimizer functional issues specific MustGather information

If you encounter a QQ/CQE query optimizer functional issue, collect the following information as related to the issue:
1. PTF / QAQQINI Information
Appendix A: What PTF / QAQQINI information to collect and how
2. Job Information.
Appendix B: What Job information to collect and how
3. File / Recreate information
Appendix C: What File / Re-create information to collect and how
  • - Appendix A: What PTF / QAQQINI information to collect and how



    PTF Information

    1. Version, release, and modification level of system (in other words, V5R4M5, V6R1M0, and so on).
    2. WRKPTFGRP <enter>.
    Press F6 to print.
    3. DSPPTF LICPGM(*ALL) SELECT(*ALL) OUTPUT(*PRINT)
    4. PTF levels of the From and To program of the message in question:

    - Look at the job log to locate the From and To program (example below).
    - DSPOBJD OBJ(QSYS/FromPGM) OBJTYPE(*PGM or *SRVPGM) DETAIL(*SERVICE) OUTPUT(*PRINT)
    - DSPOBJD OBJ(QSYS/ToPGM) OBJTYPE(*PGM or *SRVPGM) DETAIL(*SERVICE) OUTPUT(*PRINT)

    Note: Replace the FromPGM and ToPGM to the programs listed in the job log. Use WRKOBJ on the program to determine if OBJTYPE is *PGM or *SRVPGM. If there is no library listed or program starts with #, no data is needed.
    • - Example 1



      CPD4342 - 40 02/25/10 18:30:53,112435 QQQVALID QSYS *STMT QSQRUN1 QSYS *STMT

      The program QQQVALID is From program.
      The program QSQRUN1 is the To program.

      DSPOBJD OBJ(QSYS/QQQVALID) OBJTYPE(*PGM) DETAIL(*SERVICE) OUTPUT(*PRINT)
      DSPOBJD OBJ(QSYS/QSQRUN1) OBJTYPE(*SRVPGM) DETAIL(*SERVICE) OUTPUT(*PRINT)

    • - Example 2



      MCH3601 40 04.03.10 12:40:41,544241 #cfochkr 00019C QQQSRVI1 QSYS *STMT

      The program #cfochkr is the From program and because it starts with a # and no library listed, no data to collect
      The program QQQSRVI1 is the To program.

      DSPOBJD OBJ(QSYS/QQQSRVI1) OBJTYPE(*SRVPGM) DETAIL(*SERVICE) OUTPUT(*PRINT)



    QAQQINI file being used:

    Create a spooled file of QUSRSYS/QAQQINI or the QAQQINI that you may be using.
    CPYF FROMFILE(QUSRSYS/QAQQINI) TOFILE(*PRINT)

    Note: If you do not have a file QUSRSYS/QAQQINI or know what QAQQINI file that is being used, go to the next step.

  • - Appendix B: What Job information to collect and how



    Job Information / collection

    Create library and QAQQINI file to be used in data collection

    1. Click on the Microsoft Windows Start button.
    2. Click on Programs.
    3. Click on IBM iSeries Access for Windows.
    4. Click on iSeries Navigator.
    5. When iSeries Navigator opens, sign on the system.
    6. Expand the Database portion.
    7. Right-click on the system name, and select Run SQL Script.
    8. In the Run SQL Script window, copy and paste or type the below green portion starting with CL: CRTLIB QIBMDATA

    Notes:
    1. This example creates and uses the library QIBMDATA. You can change the library to the library you want to use.
    2. If you are currently using a QAQQINI file that uses values other than DEFAULT, you should speak to your Support Representative to determine if that should be included in the new QAQQINI file you create below.

     
    Caution: Putting the QAQQINI file in QUSRSYS with full debug on can cause significant overhead. All of the following are internal options except MESSAGES_DEBUG and REOPTIMIZE_ACCESS_PLAN. IBM supports only external options.

    Example

    CL: CRTLIB QIBMDATA;
    CL: CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(QIBMDATA) DATA(*NO);
    INSERT INTO QIBMDATA.QAQQINI VALUES('MESSAGES_FINAL_PLAN','*BASIC', default);
    INSERT INTO QIBMDATA.QAQQINI VALUES('MESSAGES_INFORMATIONAL','*BASIC', default);
    INSERT INTO QIBMDATA.QAQQINI VALUES('REOPTIMIZE_ACCESS_PLAN','*FORCE', default);
    INSERT INTO QIBMDATA.QAQQINI VALUES('MESSAGES_PLAN_IMPLEMENTATION','*BASIC', default);
    INSERT INTO QIBMDATA.QAQQINI VALUES('MESSAGES_QRO','*BASIC', default);
    INSERT INTO QIBMDATA.QAQQINI VALUES('DUMP_QDTS','*INCOMING', default);


    9. Go to Run, and select All.

    Use one of the following sections for collecting the traces needed.
    • - Section 1 - Collection steps for Interactive job



      1. CHGSYSLIBL QSYS2924 (Note: Only to be run for non-English systems.)
      2. CHGJOB JOB(*) LOG(4 00 *SECLVL) LOGCLPGM(*YES)
      3. STRDBG UPDPROD(*YES)
      4. CHGQRYA QRYOPTLIB(QIBMDATA)
      5. STRDBMON OUTFILE(QIBMDATA/DBMON1) TYPE(*DETAIL)

      See this TechNote for more information on Database Monitor impacts:  www.ibm.com/support/docview.wss?uid=ibm10882914


      6. Run the query in this interactive job
      7. ENDDBMON
      8. ENDDBG
      9. CHGQRYA QRYOPTLIB(QUSERSYS)
      10. DSPJOBLOG JOB(*) OUTPUT(*PRINT)
      11. WRKJOB JOB(*) OUTPUT(*PRINT)



    • - Section 2 - Collection steps for a batch job



      1. CRTLIB QIBMDATA
      2. Submit the job with HOLD(*YES) on the SBMJOB command.
      3. Find the job; for example, use the WRKSBMJOB command.
      4. Note the full job name (number/user/name). It will be used in the following steps.
      5. Issue STRSRVJOB JOB(NUMBER/USER/NAME) (Note: Replace the job with the job that was submitted.)
      6. Issue STRDBG UPDPROD(*YES)
      7 Issue RLSJOB JOB(NUMBER/USER/NAME) (Note: Replace the job with the job that was submitted.)
      8. Press F10 for a command line.
      9. Issue CHGJOB JOB(NUMBER/USER/NAME) LOG(4 00 *SECLVL) LOGCLPGM(*YES)
      (Note: Replace the job with the job that was submitted.)
      10. Issue CHGQRYA JOB(NUMBER/USER/NAME) QRYOPTLIB(QIBMDATA)
      11. Issue STRDBMON OUTFILE(QIBMDATA/DBMON1) JOB(NUMBER/USER/JOBNAMANE) TYPE(*DETAIL)
      (Note: Replace the job with the job that was submitted.)

      See this TechNote for more information on Database Monitor impacts:  www.ibm.com/support/docview.wss?uid=ibm10882914


      12. Press F3 to Exit.
      13. Press the Enter key to let the job run. When it fails, move to the next step.
      14. ENDDBMON OUTFILE(QIBMDATA/DBMON1) (Note: Replace the job with the job that was submitted.)

    • - Section 3 - Collection steps for iSeries Navigator and Run SQL Scripts



      1. Click on the Microsoft Windows Start button.
      2. Click on Programs.
      3. Click on IBM iSeries Access for Windows.
      4. Click on iSeries Navigator.
      5. When iSeries Navigator opens, sign on the system.
      6. Expand the Database portion.
      7. Right-click on the system name, and select Run SQL Script.
      8. In the Run SQL Script window, go to Options and ensure that Display Results in Separate Window is selected.
      9. In the Run SQL Script window, copy and paste or type the below green portion starting with CL: STRDBG UPDPROD(*YES);
      CHGJOB JOB(*) LOG(4 00 *SECLVL) LOGCLPGM(*YES) ;
      CL: STRDBG UPDPROD(*YES);
      CL: STRDBMON OUTFILE(QIBMDATA/DBMON1) TYPE(*DETAIL) ;

      See this TechNote for more information on Database Monitor impacts:  www.ibm.com/support/docview.wss?uid=ibm10882914

      CL: CHGQRYA QRYOPTLIB(QIBMDATA);
      10. Go to Run, and select All.
      11. Delete the above statements, and run your SQL statement.
      12. Close the result set window, delete the SQL statement, and type the following statements (Note: This will set the QAQQINI file back to the default of QUSRSYS. If you use a different library, change the library name on the CHGQRYA command.):

      CL: ENDDBMON;
      CL: ENDDBG;
      CL: CHGQRYA QRYOPTLIB(QUSRSYS);
      CL:DSPJOBLOG OUTPUT(*PRINT);

      13. Go to Run, and select All.
      14. Close the Run SQL Script window.



    • - Section 4 - Collection steps for Server jobs (QZDASOINIT / QRWTSRVR / QSQSRVR)

      1. For ODBC use the following Rochester Support Center knowledgebase document to use the QAQQINI file in library QIBMDATA:

      o N1016763, Pointing to a Single Job Using the Optimized Database Host Server to a Specific QAQQINI File: http://www.ibm.com/support/docview.wss?uid=nas8N1016763
      2.
      STRDBMON OUTFILE(QIBMDATA/DBMON1) JOB(*ALL/*ALL/QZDASOINIT) TYPE(*DETAIL)
      (Note: Replace the job name with the job that will run the SQL. QZDASOINIT / QRWTSRVR / QSQSRVR are some examples.)
      See this TechNote for more information on Database Monitor impacts:  www.ibm.com/support/docview.wss?uid=ibm10882914
      3. Make the connection and run the job to re-create the error.
      4. Capture the job log, if at possible:

      - While the job remains connected.
      - WRKOBJLCK OBJ(USER) OBJTYPE(*USRPRF) (Note: Replace USER with the user that made the connection.)
      - Select each job and find the job with the errors.
      - Issue: DSPJOBLOG JOB(NUMBER/USER/NAME) OUTPUT(*PRINT) (Note: Replace the job with the correct job information.)
      5. ENDDBMON JOB(*ALL/*ALL/QZDASOINIT) (Note: Replace the job name with the job that will run the SQL. QZDASOINIT / QRWTSRVR / QSQSRVR are some examples.)


    What information to collect to submit to IBM

    1. All spooled files from Interactive job
    WRKJOB <enter>
    Select Option 4
    Send in all spooled files.
    2. DBMON created in collection steps above
    - CRTSAVF QIBMDATA/SAVE1
    - SAVOBJ OBJ(DBMON1) LIB(QIBMDATA) DEV(*SAVF) SAVF(QIBMDATA/SAVE1) DTACPR(*YES)

  • - Appendix C: What File / Re-create information to collect and how



    Re-create Information if possible
    1. All Physical Files with all indexes and Logical Files built over the PF saved to a save file.
    2. SQL statement / OPNQRYF/program used to create error.

    File Information if re-creates not possible
    1. DSPFD and DSPFFD of database files (both physical and logical files) and views involved.
    2. DSPDBR of the PF involved.



  • - Directions for collecting spooled files that you own



    To retrieve the spooled file that you own, do the following:

    1. Open the System i Navigator.
    2. Expand your system where the files are located. Log in with your user ID (used to collect the spooled files above).
    3. Expand Basic Operations, and left click on Printer Output to display the output files.
    4. Locate the spooled files collected in the job(s) above.
    5. Finally, right click the files from System i Navigator, select Export..., select the destination folder, and click Save.



[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Db2 for i","Platform":[],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

561842504

Document Information

Modified date:
26 December 2019

UID

nas8N1012274