Recovering a Corrupt Access Plan That Is Giving MCH3601 Message

Technote (troubleshooting)


Problem(Abstract)

This document discusses how to determine where the access plan is kept and how to recover a corrupt access plan.

Resolving the problem

An access plan contains information about the data needed for a query and how to extract it. When the access plan is corrupt, you can experience error messages such as the MCH3601 message shown below, which is given when using the SQL Query Engine (SQE) at release R520 and R530 when an access plan is corrupt.

The SQE Access Plan Corruption error message:

MCH3601
 08/08/05 06:39:18.934240 QQQOOOCHKF   QSYS        *STMT    QQQOOOCHKF
From module . . . . . . . . :   QQQOOOFLTR
From procedure . . . . . . :   DBOPPASSESENABLEFILTER
Statement . . . . . . . . . :   4337
To module . . . . . . . . . :   QQQOOOFLTR
To procedure . . . . . . . :   DBOPPASSESENABLEFILTER
Statement . . . . . . . . . :   4337
Message . . . . :   Pointer not set for location referenced.
Cause . . . . . :   A pointer was used, either directly or as a basing pointer, that has not been set to an address.


You can also try to determine a corrupt access plan by using the Print SQL Information (PRTSQLINF) command on the SQL package or program to see if the package has strange looking data.

Determining Where the Access Plan Is Kept

To determine where the access plan is kept, consider the following:

o For dynamic SQL, an access plan is created, but the plan is not saved. A new access plan is created each time the PREPARE statement runs.
o For an IBM System i products program that contains static embedded SQL, the access plan is saved in the associated space of the program or package that contains embedded SQL statements.
o For OPNQRYF, an access plan is created but is not saved. A new access plan is created each time the OPNQRYF command is processed.
o For Query/400, an access plan is saved as part of the query definition object.
An access plan can also be stored in the System Wide Statement Cache (SWSC) or Job statement cache. The best way to try to determine specifically where the access plan is stored is to get a database monitor file. Rochester Support Center Registered knowledgebase document 27930317, Debug Information for V5R2M0 and Above for CQE and SQE - DBOP and DBPM, has instructions on how to start the database monitor.

Once the database monitor data is captured, you can run the following query over the database monitor file to locate where the access plan is stored:

SELECT DISTINCT QQC103 AS Object_Name, QQC104 AS Object_Library,
QWC18 AS SWSC, QQ1000 AS SQL_Statement
FROM library/dbmonfile
WHERE QQRID = 1000 AND QQC21 <> 'MT' and QQJNUM = '111111'
GROUP BY QQ1000, QQC103, QQC104, QWC18
ORDER BY QQ1000

Note : Change QQJNUM to the job number or remove and QQJNUM = '111111' if the job number is unknown. Change library/dbmonfile to the library name and database monitor file name that you collected.

In the output, the Object_Name (QQC103) could be an SQL Package or SQL Programming. If the QQC103 field is blank, the access plan could be stored in the System Wide Statement Cache (SWSC) or in the Job Statement Cache (if there is an access plan saved). If the SWSC (QWC18) field shows a Y, then the access plan should be stored in the SWSC.

Another option to try to capture the program is to change the system supplied message, MCH3601, to dump additional information. One of the important items is the dump data parameter. Type the command DSPMSGD MCH3601 and select Option 5 to display the message attributes. Notice that Data to be dumped is set to *NONE. This can be changed to *JOB and other values.

*JOB says that when the MCH3601 message is issued, do a DSPJOB OUTPUT(*PRINT), which gives the call stack and all information for the job that DSPJOB gives. Note in the help text for the DMPLST parameter that the dump data only occurs when the message is sent as an escape message. In these cases, it is being sent as an escape message. If you change to specify *JOB, you will get the call stack that shows the program that is calling SQL and is failing. Use the following command to change the MCH3601 message to dump:

CHGMSGD MSGID(MCH3601) MSGF(QSYS/QCPFMSG) DMPLST(*JOB)

Press the Enter key.

To find the program that is invoking SQL, look for the first QSQ module in the call stack; start from the top and go down. The program is the one listed above it in the call stack. You can then use PRTSQLINF on this program to get more information on the SQL in the program.

Note: To capture the job data via the CHGMSGD command, the QSRVDMP system value must be set to *DMPUSRJOB or *DMPALLJOB and the program receiving the MCH3601 message must not monitor for the message, whether it be an IBM internal program or a user program.

Recovering the Access Plan

Recovery depends on the type of object that the access plan is saved in. Below is a listing of how to recover specific objects:
Object Type: Recovery:
SQLPKG Delete the SQL Package and re-create it.
PGM or SRVPGM Recompile.
SWSC IPL.
There is also an option to rebuild the access plan using the QAQQIN file. If you place the QAQQINI file in library QUSRSYS, it will affect all jobs. You can place the QAQQINI file in a user library, but then you must use the CHGQRYA QRYOPTLIB(library) command to invoke the QAQQINI file before the query is run. This can cause a slight performance degradation as the access plans are being reoptimized. After all the access plan corruption is resolved, you should remove this option from the QAQQINI file:
1. To create the QAQQINI file in QUSRSYS, type the following statement in STRSQL or Run SQL Scripts:

CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(QUSRSYS) DATA(*NO)

Press the Enter key.
2. To insert the value to rebuild the access plans, run the following statement:

INSERT INTO QUSRSYS/QAQQINI VALUES('REBUILD_ACCESS_PLAN','*YES',NULL)
3. To remove the option, type DLTF QUSRSYS/QAQQINI or run the following SQL statement:

DELETE FROM QUSRSYS/QAQQINI WHERE QQPARM = 'REBUILD_ACCESS_PLAN'

Preventative Action

To prevent an access plan from becoming corrupted, do the following:
1. Read Information APAR II13737, which points to APARs where preventative PTFs have been created to help fix certain types of access plan corruption.
2. Look for hardware problems by checking the Product Activity Log (PAL) and Service Action Log (SAL) in the System Service Tools (SST) by using the DSPLOG command. For additional information, refer to the following Rochester Support Center knowledgebase documents:

New, When to Call Hardware Support and Other Types of Hardware Failures or Issues:

To look at the SAL entries, do the following:
1. On the IBM operating system command line, type the following:

STRSST

Press the Enter key and sign on.
2. Select Option 1. Start a service tool.
3. Select Option 7. Hardware service manager.
4. Select Option 6. Work with service action log.
5. Type the date and time from before your error in the From: section and type the date and time from after your error in the To: section. Press the Enter key. The log entries will appear.
To look at the PAL entries, do the following:
1. On the operating system command line, type STRSST and press Enter.
2. Select Option 1. Start a service tool.
3. Select Option 1. Product activity log.
4. Select Option 1. Analyze log.
5. Select Option 1. All logs. Set a date range (usually entries for the last year works well).
6. Select Report type 3=Print options, and set Informational and Statistic to Y.
7. Select Report type 4=Print full report, and set Include hexadecimal data to Y.

Historical Number

387451354

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

IBM i
Performance

Software version:

5.3.5, 5.4.0

Operating system(s):

IBM i

Reference #:

N1019079

Modified date:

2013-01-04

Translate my page

Machine Translation

Content navigation