The available index utilities offer the ability to check, fix, and rebuild indexes.

Index check

Index check (PROUTIL IDXCHECK) identifies possible errors, but does not ever alter index entries. IDXCHECK can be run online or offline. If an IDXCHECK operation is interrupted or unresponsive, it does not cause data corruption. IDXCHECK works by:

  1. Checking that all index entries associated with each record can be found.
  2. Verifying that each value in the index is in the associated record.
  3. Undertaking a validation of all index blocks and reporting an associated error as appropriate.

IDXCHECK is designed to detect index corruption as sensitively as possible. It may take significant time to complete because many reads to take place during the process.

Available validation options for IDXCHECK are:

  1. Validate physical consistence of index blocks (Enabled by default).
  2. Validate keys for each record (Enabled by default).
  3. Validate record for each key.
  4. Validate key order (Enabled by default).
  5. Validate tree.
  6. Validate index against free cluster.

You can also choose to lock tables and change the error limit.

IDXCHECK processes the validation options in the following order:

  1. Options 1 and 2 are executed together.
  2. Options 3 and 4 are executed together.
Note:

If your hardware has adequate memory, CPUs, and serial disk access, then both online and offline execution of options 2, 3, and 4 now use multiple threads by default. Using threads enables serial operations to run in parallel, which may improve performance, depending on the size and layout of the database, indexes, tables, and the machine workload. Option 2 supports threading only on Type II tables. Threaded index checks are unsupported on word indexes.

Option 4 detects physical corruption, in addition to validating the key order. Running option 4 online may generate false positives, so if errors are detected, try running IDXCHECK again and enable the option to lock the tables. Locking the tables eliminates false positives.

Option 5 detects issues with indexes that span the 32 or 64 bit boundary of your index area in a deterministic fashion. This option is disabled by default. The issue is also detected with Option 3 but in a non-deterministic fashion.

Option 6 detects if index entries for the special schemas have been incorrectly deleted by PROUTIL MVSCH.

For complete details on IDXCHECK, see PROUTIL IDXCHECK qualifier.

Index fix

Index fix (PROUTIL IDXFIX) identifies and corrects errors in indexes.

Available validation options for IDXFIX are:

  1. Scan records for missing index entries.
  2. Scan indexes for invalid index entries.
  3. Both the options 1 and 2.

IDXFIX processes the validation options in the following order:

  1. Option 2 (scan indexes)
  2. Option 1 (scan records)

For complete details on IDXFIX, see PROUTIL IDXFIX qualifier

Comparison of IDXCHECK and IDXFIX

Both IDXCHECK and IDXFIX check for index corruption. The main difference is that IDXFIX can repair the corrupted indexes.

These two utilities have the following common attributes:

  • Both utilities can run online or offline.
  • Both utilities can run multiple online sessions, concurrently.
  • After-imaging does not need to be disabled before running either utility.
  • Both utilities display the error messages and log them in the log file. They also display and log a success or failure indication, along with the number of errors and warnings issued.
  • Both utilities display warning messages indicating that there is a problem but the index is still valid.

Use the following table to contrast the differences in behavior of the two utilities:

Issue IDXCHECK action IDXFIX action
Missing index entries Identify and report Identify, report, and fix by adding missing entries
Orphan index entries Identify and report Identify, report, and fix by deleting orphan entries

Operational comparison

IDXFIX and IDXCHECK are at parity and detect the same index corruption when IDXCHECK is run offline with validation options 1, 2, 3 (not enabled by default), and 4, and IDXFIX is run offline with option 3.

If IDXCHECK is run with the default settings, it does not detect the corruption that options 2 and 3 of IDXFIX detect. If IDXFIX is run online, it does not detect the errors found by Option 4 of IDXCHECK.

IDXCHECK is one of the few database utilities that you can run in read-only mode while the database is offline. Thus, you can run IDXCHECK concurrently with other diagnostic utilities that support read-only mode, saving the time required for diagnosing a corrupted database that cannot be restarted. IDXFIX cannot run in read-only mode. For more information, see Read-only connections.

Both utilities are resource intensive. When run online, they compete with other clients for database and system resources. Consider limiting the scope of the processed indexes by choosing a subset of indexes with the Some option versus choosing all indexes with the All option.

Index build

Index rebuild (PROUTIL IDXBUILD) deletes existing indexes and rebuilds them from scratch. IDXBUILD does not attempt to discover errors before deleting them and reporting the errors encountered during the index building process. If there is a fatal error, the database cannot be recovered and must be restored from backup. For this reason, it is critical to perform a backup before running IDXBUILD. When the IDXBUILD process completes, the rebuilt indexes replace the old indexes. If there is any index corruption in the old indexes, it is not detected because the IDXBUILD replaces the old indexes with the newly rebuilt indexes.

IDXBUILD runs strictly offline. Multi-tenant index rebuild (MTIDXBUILD) and table-partitioned index rebuild (TPIDXBUILD) can be run online. IDXBUILD is discussed in PROUTIL IDXBUILD qualifier.

For complete details on the index rebuild utilities, see: