How to Recover from a Damaged SYSIXADV File

Resolving The Problem

The Index Advisor file used when the optimizer determines that a permanent index might be beneficial - and it returns the key columns necessary to create the suggested index into QSYS2/SYSIXADV. More information can be found at this V7R2 reference:

http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/idxadvisor.htm

This file is not critical for IBM i operation.

You can have a damaged SYSIXADV and run normally.
But the damaged file messages are noise and most customer want to clean them up.

Damage messages most frequently seen are MCH1604, MCH1668 and CPF8199, but could be other MCH16xx or CPF81xx class messages.

To recover from having a damaged SYSIXADV file, you should follow the below steps:

Note: Replace QSYS2 with QSYS2nnnnn if this is for an IASP.

Note: Replace zz with the CCSID used by character columns using the DSPFFD command on QSYS/QADBXREF file. Here's what to look for:


             Data        Field  Buffer    Buffer        Field    Column
  Field      Type       Length  Length  Position        Usage    Heading
  DBXFIL     CHAR           10      10         1        Both     FILE  
                                                                 NAME  
    Field text  . . . . . . . . . . . . . . . :  File name              
    Coded Character Set Identifier  . . . . . :     37

- any CHAR column will do, and in this example the CCSID (Coded Character Set Identifier) is 37


These steps are best executed when application activity is quiesced.

1. ALCOBJ OBJ((QSYS2/SYSIXADV *FILE *EXCL)) CONFLICT(*RQSRLS)
2. DLTF QSYS2/SYSIXADVIX
3. DLTF QSYS2/CONDIDXA
4. DLTF QSYS2/SYSIXADV
5. CHGJOB CCSID(zz)
6. CALL QSYS/QSQSYSIBM
7. CALL QSYS/QSQIBMCHK


If the previous steps do not work due to locks, you can also try the following steps:

1. ADDENVVAR ENVVAR(QIBM_NO_INDEX_ADVICE) VALUE('') LEVEL(*SYS)
2. IPL
3. Repeat the DLTF steps if needed
4. RMVENVVAR ENVVAR(QIBM_NO_INDEX_ADVICE) LEVEL(*SYS)
5. IPL