Troubleshooting
Problem
Resolving The Problem
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 Information1. 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. - Example 1
- 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=ibm10882914CL: 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=nas8N10167632. 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=ibm108829143. 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) - Section 1 - Collection steps for Interactive job
- 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.
-
Direction for transferring files to IBM
Refer to TechNote doc - MustGather: Instructions for Sending Data to IBM i Support - http://www.ibm.com/support/docview.wss?uid=nas8N1019224
Historical Number
561842504
Was this topic helpful?
Document Information
Modified date:
26 December 2019
UID
nas8N1012274