IBM Support

MustGather: Invalid Access Path

Troubleshooting


Problem

This document describes what information should be collected for problems where the access paths (SQL Indexes, logical files) are rebuilt for no apparent reason.

Cause

Access paths can become invalidated when under certain conditions which can include
ending a RGZPFM,
restoring files without saving access paths,
restoring physical files that have logical files in a different library,
invalidation of an index due to damage found upon it,
and having the system crash while the files are in use.

Resolving The Problem

If access paths are continually rebuilt or there is no explanation for why they were invalidated, collect the following information using either Method A or Method B as it is related to the issue.

For this TechNote we use logical file and index interchangeably which officially is not correct but acceptable for this document.
 
Method A - QMGTOOLS
Data collection:

2. ADDLIBLE QMGTOOLS

3. GO MG
4. Option 13 - Check IBM for updated QMGTOOLS  You want Aug 2024 or later.

5. Option 6 - Database menu

6. Option 7 - Invalid Access Path
InvalidAccessPath_screen
7. Enter in the SQL Index or logical file that you are investigating.

8. Specify the Case number and specify the FTP option.
    Anonymous is no longer allowed (GDPR) - Supported FTP options (search for FTP Type)


NOTE: If *NO is used for FTP then you must manually send to IBM via:
MustGather: Instructions for Sending Data to IBM i Support

9. Press Enter to start data collection
QMGTOOLS_IFS
Method B - Manual Collection
We will need the complete output of this query.
You may want to run the query to an output file to get all the data sent in.
select system_index_name, system_table_name, index_type, accpth_type, create_timestamp create_ts, last_build_timestamp last_bld_ts, last_invalidation_timestamp l_i_ts, last_invalidation_reason_code l_i_rsn_code, last_invalidation_reason_type l_i_rsn_type, hex(last_invalidation_reason_type) hx_l_i_rsn_type, index_held, maintenance, recovery, index_valid, unique, partitioned, last_build_degree, last_build_type
  from QSYS2.SYSPARTITIONINDEXES x
  where system_table_schema = --library-- and system_table_name = --filename--
  order by system_table_schema, system_table_name, system_index_name
;
The '--library--' and '--filename--' are important to get correct.  
Examples: 
system_table_schema = 'PRODLIB' and system_table_name = 'ORDERS'
system_table_schema = 'BANK0001' and system_table_name = 'CHECK00001'
If no data is returned verify that you have the correct library / schema and the correct SQL table / physical file.
You can use DSPFD on the index / logical file to determine the underlying SQL table / physical file.

PTF information:

1. Version, release, and modification level of system (for example, V5R4M5, V6R1M0, and so on).

2. Issue WRKPTFGRP and press Enter; then press F6 to print a spooled file.

3. Issue DSPPTF OUTPUT(*PRINT)

General information:

1. Issue DSPLOG PERIOD((*AVAIL *BEGIN)) OUTPUT(*PRTWRAP) MSGID(CPF3145)

2. Product Activity Logs /Service Action Log Report / License Internal Code Log:

A. Issue the STRSST command.
B. Select Option 1. Start a service tool.
C. Select Option 1. Product activity log.
D. Select Option 1. Analyze log.
E. Under LOG, type 3 for Magnetic Media, and include the date/time from the last month until now.
Report type . . . . . . . . . 3
Optional entries to include:
Informational . . . . . . . y
Statistic . . . . . . . . . y

Use the options:
Report type . . . . . . . 4
For report type 4:
Include hexadecimal
data . . . . . . . . Y

F. Press F3 two times.

3. VLOGs:

A. Issue the STRSST command.
B. Select Option 1. Start a service tool.
C. Select Option 5. Licensed Internal Code log.
D. Select Option 2. Dump entries to printer from the Licensed Internal Code log.
E. Select Dump Option 1 1=Header
F. Press Enter.
G. Select Dump option 3 3=Header and entire entry
H. Specify the date and time of 2 hours prior to the file being invalidated
I. Press Enter.
J. Press F12 to exit.
K. Select Option 7. Display the status of the Licensed Internal Code log.
L. Refresh until Dump requests not complete is zero.
M. Press F3 three times,and exit out of service tool.Check QSYSOPR for messages.
 

File information:

If the Access Path that was marked invalid was a Physical File, you should collect only the FILE information in the steps below. If the Access Path was an Index or LF, collect the Index information for the index that was marked invalid and the File information will be the physical file the index or LF is built over.

1. Issue DSPJRN JRN(LIB/JRN) RCVRNG(LIB/STARTJRNRCV LIB/ENDJRNRCV)
OUTPUT(*OUTFILE) OUTFILE(QGPL/DSPJRNOUT) OUTFILEFMT(*TYPE5)
ENTDTALEN(*VARLEN *CALC)

Note: Replace the LIB/JRN with the correct library and journal end LIB / STARTJRNRCV LIB/ENDJRNRCV with the starting and ending journal receiver to span the time frame of when the file was invalidated. This step only applies if the file is journaled. The output of DSPFD can be used to determine if the file is journaled and what the journal name and journal library is.

Note: You only need to do this for the Logical Files that were rebuilt and the based on Physical File. That will limit the entries in the Physical File.

2. Issue CRTLIB QIBMDATA

3. Issue CRTSAVF QIBMDATA/PMRSAVE

4. Issue SAVOBJ OBJ(DSPJRNOUT) LIB(QGPL) DEV(*SAVF) SAVF(QIBMDATA/PMRSAVE)

5. Issue DSPFD FILE(-your library-/-your underlying physical file-) OUTPUT(*PRINT)

6. Issue DSPFD FILE(-your library-/-your index-) OUTPUT(*PRINT)

7. Issue DMPSYSOBJ OBJ(-your underlying physical file-) CONTEXT(-your library-)

8. DMPSYSOBJ OBJ(-your index-) CONTEXT(-your library-)

9. From the previous DMPSYSOBJ, search for the following and copy the ADDRESS field for each:


A) For Indexes -> DATA SPACE INDEX

OBJECT TYPE-           DATA SPACE INDEX                                *QDDSI                                                     NAME-        -your index-                    TYPE-          0C   SUBTYPE-          90                                             CREATION-    02/12/11  06:30:20              SIZE-          000AA07000                                                             OWNER-       -owner-                         TYPE-          08   SUBTYPE-          01                                             ATTRIBUTES-          0800                    ADDRESS-       076FC5CE2C 000000                                                     

B) For the Physical File (Data Space) - DATA SPACE

OBJECT TYPE-           DATA SPACE                                      *QDDS                                                       NAME-        -your index-                    TYPE-          0B   SUBTYPE-          90                                             CREATION-    02/12/11  06:30:20              SIZE-          0110017000                                                             OWNER-       -owner-                         TYPE-          08   SUBTYPE-          01                                             ATTRIBUTES-          0800                    ADDRESS-       3853FC2996 000000                                            

C) For the Physical File (Cursor) - CURSOR

OBJECT TYPE-           CURSOR                                          *MEM                                                       NAME-        -your index-                    TYPE-          0D   SUBTYPE-          50                                             LIBRARY-     -your library-                  TYPE-          04   SUBTYPE-          01                                             CREATION-    10/04/11  22:21:53              SIZE-          0000003000                                                             OWNER-       -owner-                         TYPE-          08   SUBTYPE-          01                                             ATTRIBUTES-          0000                    ADDRESS-       168B8F72C2 000000                                                   


10. Dump using service tools:

o Issue the STRSST command.
o Select Option 1. Start a service tool.
o Select Option 4. Display/Alter/Dump.
o Select Option 2. Dump to printer.
o Select Option 1. Machine Interface (MI) object .
o Select Option 11. Data space (0B)
o Select Option 2. Find by object address.
o Put in the address from Part B of Step 6 above, and press Enter.
o Press Enter to confirm.
o Select Option 3. Disassembled code.
o Press Enter.
o Press F3.
o Select Option 2. Dump to printer.
o Select Option 1. Machine Interface (MI) object.
o Select Option 13. Cursor (0D).
o Select Option 2. Find by object address.
o Put in the address from Part C of Step 6 above and press Enter.
o Press Enter to confirm.
o Select Option 3. Disassembled code.
o Press Enter to print.
o Press F3.
o Select Option 7. Display the status of the Licensed Internal Code log.
o Refresh until Dump requests not complete is zero.
o Press Enter.
o If the Access Path that was invalid is a Physical File, you can F3 and Exit Service Tools.
o If the Access Path that was invalid is an Index, continue to collect the last dump.
o Select Option 2. Dump to printer.
o Select Option 1. Machine Interface (MI) object.
o Select Option 12. Data space index (0C).
o Select Option 2. Find by object address.
o Put in the address from part A of step 6 above and press Enter.
o Press Enter to confirm.
o Select Option 3. Disassembled code.
o Press Enter to print.
o Press F3.
o Select Option 7. Display the status of the Licensed Internal Code log
o Refresh until Dump requests not complete is zero.
o Press F3 three times, and exit out of SST.
 

** All spool files and save file generated from above steps needs to be sent to IBM. **
Direction for transferring files to IBM if QMGTOOLS was NOT used

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i6QAAQ","label":"IBM i Db2-\u003EIndex \/ Access Path \/ Logical file"},{"code":"a8m0z0000000CHbAAM","label":"IBM i Db2-\u003EMustGather Database"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Historical Number

N1012160

Document Information

Modified date:
12 August 2024

UID

nas8N1012160

Page Feedback