| Resolution | Although IDXCHECK, IDXFIX and IDXBUILD are all Progress utilities that address indexes, they are very different routines which use different algorithms and logic. IDXBUILD:
- IDXBUILD must be run offline.
- When AI is enabled, IDXBUILD cannot be run without first disabling After-imaging. For alternate options refer to Article Building indexes with After-Imaging enabled
- IDXBUILD's functionality is, if you will, the 'brute force' or ‘black box’ utility for rebuilding indexes. It is an offline utility that literally 'rebuilds’ indexes from scratch off the key fields then re-activates them.
- During the execution of IDXBUILD at no time are the existing indexes ever checked but errors encountered during the index building process will be reported. Some errors are fatal, and if these occur the database cannot be recovered. For this reason before running IDXBUILD a verified backup of the database must be taken.
- When the process is complete, the rebuilt indexes will replace old indexes. In other words, if there were index corruption in old indexes, they would not be detected, because the utility replaces them with the newly rebuilt indexes.
- IDXBUILD consolidates index records to use disk space as efficiently as possible. That is, 100% of the index block is available for Index data. Unless the packing factor' -pfactor <%> since OpenEdge 10.1C is used on databases with 8K blocksizes. For further information refer to Article When to use IDXCOMPACT instead of IDXBUILD?
- Only IDXBUILD can fix corruption within the index block itself, by rebuilding the entire index and creating new index entries within the index blocks. Physical corruption can be revealed when performing record operations like finding, updating, creating and deleting database records, where traversing the index tree to update, create or delete index entries encounters a problem.
- There is a fair amount of sorting that ensues. The speed sort parameters -TB and -TM should be considered together with -B for buffer and available disc space to accommodate the size that the .srt file may reach. Approximately 80% of the database size will be needed for sorting, more if word indexes are involved. If the database exceeds 2GB in size, then a multi-volume sort file must be defined beforehand. See Article How to scope and define a multi-volume srt file for idxbuild and What are the new Index Rebuild parameters that were introduced in 10.2B06?
IDXCHECK:
- IDXCHECK can only be run offline in versions pre-OpenEdge 10.1B and online or offline since OpenEdge 10.1B. When run online there are pros and cons to the locking options that can be used. Refer to Article What are the implications of the online IDXCHECK locking options?
- Since OpenEdge 10.2B04 multiple online IDXCHECK sessions can be run concurrently.
- When AI is enabled, it does not need to be disabled to run IDXCHECK
- The functionally, as the name implies, is the checking of indexes and does not alter them in any way, even if it finds errors.
- IDXCHECK works by:
- Checking that all index entries associated with each record can be found
- Verifying that each value in the index is in the associated record
- Undertaking a validation of all index blocks and reporting an associated error as appropriate.
- If an IDXCHECK operation is interrupted or crashes, it will neither cause data corruption nor yield the database inaccessible.
- IDXCHECK does not perform crash recovery and does not access the BI.
- It was designed to detect index corruption as sensitively as possible; there are lots of reads taking place during the process which, depending on the number of records and associated indexes, may take some time to complete. For further information, refer to Article Why does idxcheck take such a long time to run?
IDXFIX:
- The IDXFIX utility can be run online or offline.
- When AI is enabled, it does not need to be disabled to run IDXFIX
- Option 3 of IDXFIX is very much like IDXCHECK, except that IDXFIX can also detect orphan index entries as well as orphan records while IDXCHECK (with the default options 1,2 and 4) cannot detect orphan index entries. IDXCHECK should be run with Options 1, 2, 3, and 4 in order to detect same corruption as IDXFIX option 3. IDXBUILD will not recycle orphan index blocks for schema indexes.
- When IDXFIX is run in “report” mode, there are similarities to IDXCHECK except that the process order is different. For a detailed explanation of the differences, refer to Article Is it expected that idxcheck option 3 detects 1422 issues but takes longer than idxfix option 3?
- When IDXFIX is run in “report and fix” mode online or offline, it will repair faulty indexes on the fly, which IDXCHECK does not.
- When run offline, IDXFIX will activate inactive indexes. For online alternatives refer to Article Can indexes be built online?
- IDXFIX can repair relational integrity problems, such as adding missing index entries to an index for an existing database table record, and deleting existing index entries that do not have a corresponding database table record. It cannot fix corruption within the index block itself.
- When IDXFIX is run online, it is run SHARE-LOCK, so there may be contention for records that slows the process down.
- Ending the operation online when run in “report and fix mode’, should not cause corruption because changes performed by IDXFIX are logged in the before-image file (and in the after-image files, if after-imaging is enabled), so normal crash recovery will take place and any incomplete index changes will be undone. Unlike an IDXBUILD operation which must be run to completion, it will merely wait to backout the last operation. For further information refer to Article Why does idxfix ALL option 3 online seem to grind to a halt? and Is it possible to interrupt an offline IDXFIX operation?
Note: Both IDXCHECK and IDXFIX by default only scan SHARED indexes in a Multi-tenant enabled database.In order to select all tenant partitions, all the tenants need to be checked under the "Multi-Tenancy (m/M) - Choose tenants or groups" menu option. |
|
|---|
