Troubleshooting
Problem
This is a general document which contains instructions on how to collect information that will assist in further debugging an SQL problem.
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/support/pages/node/882914
Resolving The Problem
For SQL functional issues, 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 SQL functional issues include, but are not limited to:
Message MCH3203 T/QSQRUN3
Message MCH3601 T/QSQROUTE
SQL functional issues specific MustGather information
If you encounter a SQE optimizer functional issue, collect the following information as related to the issue:
Appendix A: What PTF information to collect and how
PTF Information
Appendix B: What Job information to collect and how
Collection steps for interactive job
From a new sign on / job, do the following:
Collection steps for a batch job
Collection steps for server jobs (QZDASOINIT / QRWTSRVR / QSQSRVR)
What information to collect to submit to IBM:
1. All spooled files from Interactive / batch or server job.
2. QRECOVERY/QSQ901S and DBMON
o CRTDUPOBJ OBJ(QSQ901S) FROMLIB(QRECOVERY) OBJTYPE(*FILE) TOLIB(QIBMDATA) DATA(*YES)
o CRTSAVF QIBMDATA/SAVEFILE
o SAVOBJ OBJ(DBMON1 QSQ901S) LIB(QIBMDATA) DEV(*SAVF) SAVF(QIBMDATA/SAVEFILE) DTACPR(*YES)
Appendix C: What File / Re-create information to collect and how
1. All physical files with all indexes and logical files built over physical file saved to a save file.
2. SQL statement / OPNQRYF / program used to create error.
Refer to TechNote doc - MustGather: Instructions for Sending Data to IBM i Support - http://www.ibm.com/support/docview.wss?uid=nas8N1019224
Message MCH3203 T/QSQRUN3
Message MCH3601 T/QSQROUTE
SQL functional issues specific MustGather information
If you encounter a SQE optimizer functional issue, collect the following information as related to the issue:
| 1. | PTF Information Appendix A: What PTF information to collect and how |
| 2. | Job Information Appendix B: What Job information to collect and how |
| 3. | Re-create information Appendix C: What File / Re-create information to collect and how |
Appendix A: What PTF information to collect and how
Appendix A: What PTF information to collect and how
PTF Information
| 1. WRKPTFGRP F6 to print |
| 2. DSPPTF OUTPUT(*PRINT) 3. PTF levels of the From and To program if error message is involved: Look at the job log to locate the From and To program (example below): o DSPOBJD OBJ(QSYS/FromPGM) OBJTYPE(*PGM or *SRVPGM) DETAIL(*SERVICE)OUTPUT(*PRINT) o DSPOBJD OBJ(QSYS/ToPGM) OBJTYPE(*PGM or *SRVPGM) DETAIL(*SERVICE) OUTPUT(*PRINT) Note: Change 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 #, you should continue on to the next step. Example 1 The program QQQVALID is From program. The program QSQRUN1 is the To program. CPD4342 - 40 02/25/10 18:30:53,112435 QQQVALID QSYS *STMT QSQRUN1 QSYS *STMT DSPOBJD OBJ(QSYS/QQQVALID) OBJTYPE(*PGM) DETAIL(*SERVICE) OUTPUT(*PRINT) DSPOBJD OBJ(QSYS/QSQRUN1) OBJTYPE(*SRVPGM) DETAIL(*SERVICE) OUTPUT(*PRINT) Example 2 The program #cfochkr is the From program and because it starts with a # and no library listed, there is no data to collect. The program QQQSRVI1 is the To program. MCH3601 40 04.03.10 12:40:41,544241 #cfochkr 00019C QQQSRVI1 QSYS *STMT DSPOBJD OBJ(QSYS/QQQSRVI1) OBJTYPE(*SRVPGM) DETAIL(*SERVICE) OUTPUT(*PRINT) |
Appendix B: What Job information to collect and how
Appendix B: What Job information to collect and how
Collection steps for interactive job
Collection steps for interactive job
From a new sign on / job, do the following:
| 1. | Sign off and back on to get a clean job log |
| 2. | CHGSYSLIBL QSYS2924 (Note: Only to be run for non-English systems where QSYS2924 exists.) |
| 3. | CHGJOB JOB(*) LOG(4 00 *SECLVL) LOGCLPGM(*YES) |
| 4. | STRDBG UPDPROD(*YES) |
| 5. | CRTLIB QIBMDATA |
| 6. |
STRDBMON OUTFILE(QIBMDATA/DBMON1) TYPE(*DETAIL) INCSYSSQL(*YES)
See this TechNote for more information on Database Monitor impacts: www.ibm.com/support/docview.wss?uid=ibm10882914
|
| 7. | STRTRC SSNID(TRACE1) MAXSTG(4000000) (Note: Set the MAXSTG as large as you can.) |
| 8. | Run the program / SQL that fails. |
| 9. | ENDTRC SSNID(TRACE1) DTALIB(QIBMDATA) PRTTRC(*YES) |
| 10. | ENDDBMON |
| 11. | ENDDBG |
| 12. | DSPJOBLOG JOB(*) OUTPUT(*PRINT) |
| 13. | WRKJOB JOB(*) OUTPUT(*PRINT) |
| 14. | WRKJOB <enter> and send all spooled files. |
Collection steps for a batch job
Collection steps for a batch job
| 1. | Issue 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 STRDBMON OUTFILE(QIBMDATA/DBMON1) JOB(NUMBER/USER/JOBNAMANE) TYPE(*DETAIL) INCSYSSQL(*YES) (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
|
| 11. | Issue STRTRC SSNID(TRACE1) JOB(NUMBER/USER/NAME) MAXSTG(4000000) (Note: Set the MAXSTG as large as you can and replace the job with the job that was submitted.) |
| 12. | Press F3 to Exit. |
| 13. | Press the Enter key to let the job run. |
| 14. | ENDDBMON JOB(NUMBER/USER/JOBNAMANE) (Note: Replace the job with the job that was submitted.) |
| 15. | ENDTRC SSNID(TRACE1) DTALIB(QIBMDATA) PRTTRC(*YES) |
Collection steps for server jobs (QZDASOINIT / QRWTSRVR / QSQSRVR)
Collection steps for server jobs (QZDASOINIT / QRWTSRVR / QSQSRVR)
| 1. | Issue CRTLIB QIBMDATA |
| 2. |
Issue STRDBMON OUTFILE(QIBMDATA/DBMON1) JOB(*ALL/*ALL/QZDASOINIT) TYPE(*DETAIL) INCSYSSQL(*YES)
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. | Issue STRTRC SSNID(TRACE1) JOB(*ALL/*ALL/QZDASOINIT) MAXSTG(4000000) Note: Set the MAXSTG as large as you can and replace the job name with the job that will run the SQL (QZDASOINIT / QRWTSRVR / QSQSRVR are some examples). |
| 4. | Make the connection and run the job to re-create the error. |
| 5. | Capture the job log, if at possible: o While the job remains connected o Issue WRKOBJLCK OBJ(USER) OBJTYPE(*USRPRF) Note: Replace USER with the user that made the connection o Select each job and find the job with the errors o Issue the following command: DSPJOBLOG JOB(NUMBER/USER/NAME) OUTPUT(*PRINT) Note: Replace the job with the correct job information |
| 6. | Issue ENDTRC SSNID(TRACE1) DTALIB(QIBMDATA) PRTTRC(*YES) |
| 7. | Issue 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 / batch or server job.
2. QRECOVERY/QSQ901S and DBMON
o CRTDUPOBJ OBJ(QSQ901S) FROMLIB(QRECOVERY) OBJTYPE(*FILE) TOLIB(QIBMDATA) DATA(*YES)
o CRTSAVF QIBMDATA/SAVEFILE
o SAVOBJ OBJ(DBMON1 QSQ901S) LIB(QIBMDATA) DEV(*SAVF) SAVF(QIBMDATA/SAVEFILE) DTACPR(*YES)
Appendix C: What File / Re-create information to collect and how
Appendix C: What File / Re-create information to collect and how
1. All physical files with all indexes and logical files built over physical file saved to a save file.
2. SQL statement / OPNQRYF / program used to create error.
| 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
[{"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
564913787
Was this topic helpful?
Document Information
Modified date:
12 June 2023
UID
nas8N1012188