PROUTIL IDXFIX qualifier

Checks database records and indexes to determine whether an index is corrupt or a record has a missing or incorrect index. IDXFIX will also repair corrupted indexes.

Syntax

proutil db-name -silent -C idxfix 
     [ tenant tenant-name | group group-name | shared ]
     [ recs numrecs ]     
        -NL -rusage
     [ -userid username [ -password passwd ] ]

Parameters

db-name
Specifies the database whose index you are checking.
-silent
Indicates that repetitive messages are not sent to the screen or the log file.
tenant tenant-nam
Specifies that the IDXFIX operation is to be performed only on the index partitions of tenant-name. If tenant-name does not own any partition of the specified indexes, IDXFIX exits with an error. Specifying tenant is only allowed on databases enabled for multi-tenancy.
group group-name
Specifies that the IDXFIX operation is to be performed only on the index partitions of group-name. If group-name does not own any partition of the specified indexes, IDXFIX exits with an error. Specifying group is only allowed on databases enabled for multi-tenancy.
shared
Specifies that the IDXFIX operation is to be performed only on the shared indexes.
recs numrecs
The number of records to process in one transaction. If not specified, numrecs defaults to 1.
-NL
Avoids issuing record locks and index admin locks.
-rusage
When specified, IDXFIX reports resource statistics including user and system CPU usage and approximate disk reads and write I/O statistics, and elapsed time.
-userid username
Identifies a user privileged to access protected audit data when executing this utility.
-password passwd
Password for the privileged user.

With PROUTIL IDXFIX, you can specify whether to scan the database, the indexes, or both. The Index Fix Utility displays the following menu:

                     Index Fix Utility

 1. Scan records for missing index entries with index block validation.
 2. Scan indexes for invalid index entries.
 3. Both 1 and 2 above.
 4. Cross-reference check of multiple indexes for a table.
 5. Build indexes from existing indexes.
 6. Delete one record and its index entries.
 8. Scan records for missing index entries.
 q. Quit

 Enter your selection:

The table below describes the PROUTIL IDXFIX options.

Table 1. PROUTIL IDXFIX options
Option Action
1 Scans the database records for missing or incorrect indexes with index block validation.
2 Scans the index for corrupt index entries. You can specify whether to scan all indexes or a specific set of indexes.
3 Checks the index entries, then checks the database entries.
4 Prompts you for the table and indexes for which you want to run a cross-reference check.For the specified indexes the following processing takes place:
  • Scan the indexes and each entry in the index.\
  • For each index entry, fetch the associated record by ROWID and validate that the fields in the record match the fields used to build the index key
  • After verifying the individual indexes, an index-to-index comparison verifies that the list of ROWIDs generated by one index exist in the other index(es)
5 Allows you to rebuild multiple indexes based on one known index. At the prompts, enter the following:
  • At the Table name: prompt, enter the name of the table for the indexes.
  • At the first Index name: prompt, enter the name of the source index. This is the good index to be used for locating the rows of the table.
  • At subsequent Index name: prompts, enter the name of index(es) to rebuild.
  • Enter ! when you finish entering index names.
The utility asks you to verify your entries, and then rebuilds the specified indexes.
6 Prompts you to specify the RECID of the record you want to delete and the name or number of the area where the record resides.

Deletes one record and all its indexes from the database. It can identify the table, index, and other objects affected by record removal, and can delete records without index entries. Use this option when a record has damaged indexes. It deletes the leading fragment that can be retrieved and then returns errors that describe why other fragments cannot be deleted.

Note: You can specify either the area name or area number as the area. If an area is invalid, IDXFIX displays a message declaring the area invalid and then halts the action.

RECID numbers identify a record and the block where it resides, but RECID numbers can exist in multiple areas. So before you perform an option 6 operation, make sure you know the exact area in which the table resides. Specifying a wrong area could potentially delete a record from another table than the one you intended.
8 Scans records for missing index entries. This option performs the same scan as option 1, but does not perform the index block validation.
q (formerly 7) Ends the PROUTIL Index Fix utility.

If you specify option 1, 2, 3 or 8 in the table above, the following menu appears:

Select one of the following:
All           (a/A) - Fix all the indexes
Some          (s/S) - Fix only some of the indexes
By Area       (r/R) - Fix indexes in selected areas
By Schema     (c/C) - Fix indexes  by schema owners
By Table      (t/T) - Fix indexes in selected tables
By Partition  (p/P) - Choose indexes in selected table partitions
By Activation (v/V) - Fix selected active or inactive indexes
-------------------
Multi-Tenancy (m/M) - Choose tenants or groups
-------------------
Quit     (q/Q) - Quit, do not fix

Enter your selection:

The table below describes the PROUTIL IDXFIX options.

Table 2. PROUTIL IDXFIX scan options
Option Action
All Prompts you to verify whether you fix all indexes
Some Prompts you for the indexes you want to fix by first entering the table name, and then the index name; if the table name entered is for a partitioned table and the index name is for a partitioned (local) index, IDXFIX then prompts for a partition name. IDXFIX then prompts you to verify the action.
By Area Prompts you for the area containing the indexes you want to fix, and then prompts you for the indexes in the area.
By Schema Prompts you for the schema owner of the indexes you want to fix, then prompts you for the indexes, then prompts you to verify the action.
By Table Prompts you for the table containing the indexes you want to fix, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting
By Partition Prompts you for the table partitions containing the indexes you want to fix, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for sorting
By Activation Prompts you to chose active or inactive indexes, then prompts you for the indexes, then prompts you to verify the action.
Multi- Tenancy Prompts you to choose tenants or groups, and then prompts you for the specific tenants or groups. This menu option is only available for databases enabled for multi-tenancy when tenant, group, or shared was not specified on the command line.
Quit Quits without fixing any indexes.

Note that only options valid for your database appear.

Notes

  • PROUTIL IDXFIX performs the following operations for each index it checks:
    • Reads the contents of the index and the contents of the file, verifies that all the values in the records are indexed, and verifies that each value in the index is in the associated record
    • Performs various checks on the data structures in the index to verify that there is no corruption
  • IDXFIX displays error messages on the screen and logs them in the log file. It also displays and logs a success or failure indication, along with the number of errors and warnings issued. IDXFIX might also display warning messages. Although these messages indicate that some condition is a problem, the index is still valid. Check the log file for details.
  • Index Fix does not provide a comparison of the index scan and the database scan when you run them online because the database can change during operation.
  • Index Fix is designed to wait if a record is in the process of being updated, thus ensuring that it does not incorrectly change a user action in process. However, because changes can occur during the scans, the reported data might not exactly match the database at completion. Index Fix displays this warning when you run it online.
  • You can run IDXFIX online or offline.
  • Index Fix does not delete or disable indexes, but when you run a full database scan with fix offline and it is successful, it enables a disabled index if no errors are found.
  • Enabling indexes online is not advisable because it is not possible to detect changes that are being made to indexes while the process is running.
  • While IDXFIX can ensure that an index is complete and correct, it cannot make any improvement to the index's utilization level.
  • See Maintain Database Structure for a description of how to monitor the progress of this utility using the _UserStatus Virtual System Table (VST).
  • IDXFIX requires additional security when auditing is enabled for your database. Only a user with Audit Archiver privilege can run this utility. Depending on how user identification is established for your database, you may need to specify the -userid and -password parameters to run this utility. For more information on auditing and utility security, see Auditing impact on database utilities.
  • During the record scan phase of options 1 and 3, logical verification of the a record's index(es) is performed for all RECIDS in the range. However, if an index for the record resides in another area of the database, physical validation of the index block is not performed. You can use the Validation option of PROUTIL IDXCHECK to perform a physical validation of a specific index.
  • Option 6 returns messages if the ROWID is invalid, if the specified area does not exist, or if you specify a LOB instead of a record. If option 6 cannot find all parts of a fragmented record, it returns informational messages about the missing fragment, for example:
    Record with rowid 386 has a fragment of rowid 488 that could not be found.
    Do you want to delete the corrupt record? (y/n)
    y
    If the attempt to delete the record fails, messages describe the location of issues:
    Failed to upgrade record in table 'Customer' , recid = 386. (6733)
    Deletion of key failed for record with rowid 386 for index 14 of table 2, partion 0 in area 8, Error - 1214
  • If an index and table reside in different areas, and if an area number (representing the location of the area table) is provided for RECID validation, index blocks residing in another area are not verified.
  • For Type II areas, options 1 and 8 only scan the blocks belonging to Type II tables and indexes instead of the whole Type II area. Options 1 and 8 output messages that specify the Type II areas and the object number of the blocks scanned for a Type II object.
  • PROUTIL IDXFIX uses a temporary file to store the list of indexes being fixed during execution of some options. The temporary file is named dbname.pid.xb. The file is deleted when the process successfully completes. If the process terminates unsuccessfully, the file may not be deleted.
  • For databases enabled for multi-tenancy, options 4 and 5 of PROUTIL IDXFIX operate on shared tables if tenant or group is not specified on the command line.
  • For databases enabled for table partitioning, the Choose Indexes screen provides an additional column to identify indexes. A partitioned table can have global (G) and local(L) indexes, as indicated in the G/L column. If the column is blank, the index does not belong to a partitioned table.
  • For databases enabled for table partitioning, index partition selection is governed as follows:
    • When By Table is first selected, if a table and local index are selected, all partitions are selected. You can not select individual partitions.
    • When By Area is first selected, only the index partitions in the selected areas are available for selection. Consequently if a local index has multiple partitions in multiple areas, only the index partitions in the selected areas are selectable.
    • When By Schema is selected, all partitions of the local indexes are available for selection.
    • When By Partition is selected, only local indexes belonging to the selected partitions are available for selection.
    • When the Some option is selected, if a local index is entered, you are warned that the index is partition-aligned, and prompted to enter a partition or continue without specifying a partition. If you do not specify a partition, all partitions of the index are processed.
  • If db-name is a UTF-8 database, you must specify -cpinternal UTF-8 on the command line. If not specified, an error message is generated and IDXFIX exits.