To fix records in a table that do not have matching index entries: Whenever repair options are used, it is a best practice to re-run IDXCHECK or IDXFIX with the scan Option afterwards to ensure that the issue has been addressed fully and no other index issues are still present in the database.
Option 1: Fix index entries for that record with IDXFIX Option 1 or Option 3 with the fix Option.Unlike IDXBUILD, IDXFIX
scan and fix is is not an all or nothing operation. If the utility is interrupted, only the last incomplete index modification is undone and the utility can be started again from where it was interrupted.
To fix missing index entries for records, run IDXFIX (online or offline):
- Command: proutil <dbname> -C idxfix
- Select: Option 1. Scan records for missing index entries or Option 3. Both 1 and 2 (IDXFIX scans records for missing index entries and scans indexes for invalid index entries).
- Select: Option T - to fix indexes by selected Table.
- Enter: the Option number corresponding to the Table the record is in from the 8783 error.
- Select: G to Go - indicating that that selection of tables is completed.
- Select: A - for All Indexes
- Select: G - to Go
- Answer: y - to the "Is this correct?" question
- Answer: y - to the "Fix indexes on Scan. Is this correct?" question.
- Enter: the RECID of the record to start the scan at.
- Enter: a second RECID to use as an endpoint of the scan or hit return to indicate just the one RECID.
- Enter: the Storage Area Number that the RECID of the table is located in.
- Answer: y to the "Is this correct?" question
- Once the scan and fix are complete, a prompt to enter another RECID range, or Q can be used to quit the utility.
Once complete, IDXFIX will report for any record it deleted or added index keys with the following messages:
SYSTEM ERROR: Index<index-num> (<owner-name>.<table-name>, <index-name>): couldn't find key <key> recid <DBKEY>. (8783)
Index <index number> (<owner name>.<table name>, <index neme>): Deleted key <key value> recid <record identifier>. (8828)
Index <index name> (<owner name>.<table name>, <index name>): Added key <index value> recid <record identifier>. (8827)
The success of IDXFIX scan and fix Option 1 or 3 depends on the record, schema and index tree being physically correct. There are times when IDXFIX Options 1 or 3 with the fix Option cannot address the issue. This is because IDXFIX Option 1 is only able to create missing index entries for records when:
- Mismatched index entries can be successfully deleted.
- The records do not have some other issue that results in building key entries unsuccessfully.
- The index manager does not experience an issue with trying to insert the new key entries.
In cases where the 8783 errors are not addressed by IDXFIX Options 1 or 3 with the fix option, another method to address the issue is outlined in
Option 2 below.
Option 2: Delete the record and index entries associated with that record with IDXFIX Option 6. Apart from running an (offline) IDXBUILD to rebuild index entries from scratch, another method is to export the records, then delete the record using IDXFIX Option 6 and finally re-import the records back into the database.
IDXFIX Option 6 should only be used if after first retrieving the record using the RECID from error 8783, unless the record was found to not be logically consistent or no longer needed in that table. If the record is a valid record, export the record first before deleting it with IDXFIX Option 6. The record can then be imported back into the database which will result in new indexes being created for the record.
To delete the record and its associated index entries run IDXFIX (online or offline):
- Command: proutil <dbname> -C idxfix
- Select: Option 6. Delete one record and its index entries
- Enter: the RECID to delete listed in the 8783 error.
- Enter: the Storage Area Number that the RECID is located in.
- Answer: y to the "Is this correct?" question.
- Repeat for each RECID that is corrupt. When there are many records to be deleted, this can be scripted by parsing a list of the recid's. The following Article provides an example:
