Checking the indexes

After you locate the DBD in the dump, the indexes can be analyzed for inconsistencies.

Procedure

To check each index fan set located by ODB:

  1. If the index OBD is marked, make a note that it is a duplicate OBD (indicates an error) and return to the procedure that referred you here. If it is not marked, continue with the next step.
  2. Determine whether the OBDFSCHI field of the OBDFS matches the record OBID. If not, make a note of the error and continue.
  3. Cross-check the OBDFS in the DBD with the Db2 catalog. Issue the following SQL statement, replacing dbid with the DBID of the DBD being checked, and obid with the OBID of the index fan set.

    Begin program-specific programming interface information.

    SELECT NAME, CREATOR FROM SYSIBM.SYSINDEXES
      WHERE DBID=dbid
      AND OBID=obid;

    End program-specific programming interface information.

    The result is one row is returned. If more or less than one row is returned, make a note of the error.

  4. Issue the following SQL statement, using the NAME you received from the previous SELECT and the CREATOR you received.

    Begin general-use programming interface information.

    SELECT COUNT(*) FROM SYSIBM.SYSKEYS
      WHERE IXNAME=name
      AND IXCREATOR=creator;

    End general-use programming interface information.

    The result is equal to the value of OBDKNFDO in OBDFS; this field contains the number of columns in the key. If the result is not as described, make a note of the error.

  5. Use the OBID contained in the OBDIPSET field to locate, check, and mark the index page set OBD. The value for OBDTYPE is binary B'11'. To check the index page set OBD:
    • Determine whether the OBDPSOB field matches the index OBID.
    • Compare the value of the OBDPSET field in the DBD to values in the SYSIBM.SYSINDEXPART catalog table. Issue the following SQL statement, replacing the NAME returned in step 3 and the CREATOR returned in step 3.

      Begin general-use programming interface information.

      SELECT COUNT(*) FROM SYSIBM.SYSINDEXPART
        WHERE IXNAME=name
        AND IXCREATOR=creator;

      End general-use programming interface information.

      The result is equal the value of OBDPSANM in OBDPS if the index is a clustering index for a partitioned table space. If the index is not a clustering index for a partitioned table space, the result is 1.

      If the results are not as described, make a note of the error.

    • Return to the procedure that referred you here.