Checks database indexes to determine whether an index is corrupt, and if it is, diagnoses the problem.

Syntax

proutil db-name -C idxcheck  
   [ all | 
     table [owner-name.]table-name 
        [partition partition-name | composite initial] |  
     area area-name | schema schema-owner]
     [ tenant tenant-name| group group-name| shared ]

Parameters

db-name

Specifies the database whose index you are checking.

all

Specifies that you want to check all your indexes. If you also specify tenant or group on the command line with all, only the indexes belonging to the tenant or group are checked.

table [owner-name.]table-name

Specifies that you want to check the indexes defined for the named table.

partition partition-name

Specifies that the IDXCHECK operation is performed only on the indexes of the partition-name table partition. partition-name must be a defined partition of [owner-name.]table-name. Specifying partition is only allowed on databases enabled for table partitioning.

composite initial

Specifies that the IDXCHECK operation is performed on the indexes of the initial partition for a partitioned table.

area area-name

Specifies that you want to check all the indexes defined in the named area.

schema schema-owner

Specifies that you want to check all the indexes owned by the named schema-owner.

tenant tenant-name

Specifies that the IDXCHECK 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, IDXCHECK exits with an error. Specifying tenant is only allowed on databases enabled for multi-tenancy.

group group-name

Specifies that the IDXCHECK 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, IDXCHECK exits with an error. Specifying group is only allowed on databases enabled for multi-tenancy.

shared

Specifies that the IDXCHECK operation is to be performed only on the shared indexes.

If you do not specify all, table, area, or schema, the following menu appears:

      Index Check Utility
      ===================

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

      Enter your selection:

The table below describes the PROUTIL IDXCHECK options.

PROUTIL IDXCHECK lets you know whether you have to perform an index rebuild before actually running PROUTIL IDXBUILD. IDXCHECK 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 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

If PROUTIL IDXCHECK completes successfully, it ensures that all FIND, CAN-FIND, GET, FOR EACH, and PRESELECT statements that use those indexes work correctly. If errors result, the problem index might produce unexpected results with those statements.

You can modify the validations performed by PROUTIL IDXCHECK. Selecting Validation (o/O) from the main menu, presents you with the following menu:

Validation Options
----------------
* 1 - Validate physical consistency of index blocks
* 2 - Validate keys for each record
  3 - Validate record for each key
* 4 - Validate key order
  5 - Validate tree
  6 - Validate index against free cluster
  L - Lock tables during the check
  R - Reset error limit, current: 500
  C - Continue to execute
  Q - Quit

The options with asterisks are selected by default.

The table below describes the PROUTIL IDXCHECK VALIDATION options.

Notes

• PROUTIL IDXCHECK automatically determines if a server is connected to the database, then decides which mode (online or offline) to run.
• When PROUTIL IDXCHECK is executed on an offline database, no other process can access the database until IDXCHECK completes.
• 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 IDXCHECK exits.
• When PROUTIL IDXCHECK finds any corruption, it displays error messages. If error messages appear, save the database and the output messages for analysis by Technical Support, back up your database, and then run PROUTIL IDXBUILD.
• IDXCHECK displays error and warning 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.
• IDXCHECK 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.
• See Maintaining Database Structure for a description of how to monitor the progress of this utility using the _UserStatus virtual system table (VST).
• PROUTIL IDXCHECK uses a temporary file to store the list of indexes being checked 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, if you specify a tenant or group only those indexes belonging to the tenant or group are checked. If you specify shared, only shared indexes are checked.
• 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.