1. Why error 15913 reports more index entries vs records found amounts(15913) SYSTEM ERROR: Index 10 (PUB.<table>, <index>) contains 378487433 entries, but 378485816 records found.
== 1617
This is a consequence of the 8781 error, which has been fixed in later versions. Previously, this would cause IDXCHECK to loop when the sequence of index keys was out of order:
IDXCHECK was reworked under the presumption that while there are an infinite number of ways that indexes can be corrupted, in certain cases where it is known that errors are occurring and will need to be fixed anyway, it is worthwhile rather skip over them and start processing the next index, so that at least one can find out which indexes need to be rebuilt without exhausting the IDXCHECK error limit on the same index. This means that as soon as the 8781 error is encountered, it is reported then instead of moving on to the next key, moves onto the next index without finding the remaining records in this index.
After verifying with certainty and fixing index corruption (see below), re-run IDXCHECK with locking to confirm the presence of 8781 and 15913 errors to determine if an IDXBUILD is required to fix physical index corruption.
2. Why the RECID <recid from 8781> finds a different recordWhen the RECID
<recid from 8781> is used to find the record, the key(s) in index are different from those in the error message, because this is the record that is returned by the current entry.
This is because the same key value exists in more than one record's key field. When the next record on the same key value is processed, it finds the
out-of sequence problem and reports the key values found on that record.
This is why before addressing corruption, it's good to try and retrieve the information in another way, in the hopes that in your understanding of the application it can provide further clues as to how this situation may have arisen (not always, but often sight of the key values starts to trigger potential reasons).
3. Why IDXCHECK still reports an "out of sequence" 8781 error after an online IDXFIX a. IDXCHECK runs with Validation Option: 4. Validate key order { ON by default }
- This option also detects physical index corruption (as opposed to logical corruption for missing entries in other validation options), which is why an IDXBUILD may be required. This is the only means of correcting physical index corruption.
- When performing key order validation, IDXCHECK traverses the b-tree, comparing the key value of each key with previous one.
- Raw values of index keys are expected to be in ascending order on the index B-Tree.
- If the current key is not larger, this is an "out of sequence" key order and the 8781 error is reported.
- When the key order is validated online, it can generate a **false positive** because there is no locking and database online transaction processing (OLTP) is taking place at the same time.
- This is why the Locking Option was added { OFF by default }. Together with the additional online Locking, IDXCHECK will be slower having to wait (lkwtmo) as any other connection at the time. This is further clarified (with an example) in Article: What are the implications of the online IDXCHECK locking options?
b. When IDXFIX is run online, it cannot and does not detect the errors found by Option 4 of IDXCHECKThe reason is that since IDXFIX can selectively be run to fix the index, false positives are not ideal. The key order can only be validated offline in order to be reliably addressed with
"Fix indexes on Scan"Historically, this has not been clear. As a result, this information is available in the following Documentation:
When IDXCHECK Option 4 online detects an error Before drawing conclusions, run a selective idxcheck again against the indexes reported with the
"L (table lock)" validation option to eliminate the possibility of a false positive:
$ proutil -C IDXCHECK -lkwtmo 30 -Bp 32
- Select: O (Validation), then L (Lock tables during the check) & "C"
- Select: T (By Table), then toggle the number next to the required table(s) & "G"
- Toggle the number next to the required index & "G" & "Y"
If
key order index corruption 8781 are still reported:
First export the data from the record by RECID. This allows field data to be reviewed for both
accuracy and
uniqueness. It also allows data re-in-statement if needed, once corrective actions have been carried out.
OUTPUT TO <filename>.
FOR EACH <table> WHERE recid(<table>) = <RECID from 8781>:
PUT UNFORM <tablename>.
OUTPUT CLOSE.
To find if there are correlating
duplicate records, the record field contents need to be reviewed in order to construct a new ABL query based on the field values of the record, specifically any
unique field values for which an index does exist. These can then be compared to verify that the records are identical or not and next decisions can be taken.
If duplicate records are found:ONLINE: Run IDXFIX > Option 6. Delete one record and its index entries
Then supply the RECID and the area number of the table.
After duplicate records have been removed, or if there are no duplicate records:Run IDXFIX Option 2 (scan indexes)
offline, assuring
"Validate recids for index entries" &
"Fix indexes on Scan" are confirmed (Y).
- When IDXFIX is run online, it cannot and does not detect the errors found by Option 4 of IDXCHECK. The key order can only be validated when IDXFIX is run offline. Once the errant index has been rebuilt offline, the sequence order will be restored.
- While IDXFIX offline will fix the out-of-sequence, IDXBUILD is faster and better in that it rebuilds from scratch, except after-imaging needs to be disabled. Strictly, only need this index needs to be rebuilt.
Re-run IDXCHECK offline or online with locking to confirm the presence of 8781 and 15913 errors to determine if an IDXBUILD is required to fix physical index corruption.
