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 ] [ -RO ]
   [ -thread 0 | 1 ] [ -threadnum num-threads ] [ -lkwtmo seconds ]

Parameters

db-name

Specifies the name of 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 allowed only on databases enabled for table partitioning.

composite initial

Specifies that the IDXCHECK operation is performed on the indexes of the initial partition of 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 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 allowed only on the databases enabled for multi-tenancy.

group group-name

Specifies that the IDXCHECK operation is 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 allowed only on the databases enabled for multi-tenancy.

shared

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

-RO

Runs the utility in read-only mode. Use read-only mode to connect to databases on the read-only media or to diagnose and repair issues on a stopped database before starting it. Read-only mode allows any number of connections at once, up to the maximum number of connections, thus reducing bottlenecks and expediting disaster recovery on large databases.

Note: Use -RO with caution because it is designed to work only with a read-only connection. Unexpected errors may result if you specify -RO when using a single-user or multi-user connection.

-thread 0 | 1

Enables threaded index check, which is enabled by default, for options 2, 3, and 4. 0 specifies no threading and 1 turns on threaded index check. Using threads enables serial operations to run in parallel, improving performance. Threads are not spawned during offline index checks. Option 2 does not spawn threads for small table objects. Options 3 and 4 do not spawn threads for word indexes or small indexes.

-threadnum num-thread

This optional parameter indicates the maximum number of threads created by the main process for a threaded index check. If -threadnum is not specified and the number of CPUs is:

• Less than six—The number of CPUs is the maximum number of threads.
• Equal to or greater than six—The default maximum number of threads is six.

These settings ensure that threading does not overwhelm the system.

The maximum number of threads allowed for -threadnum is the number of available CPUs. If you specify a value greater than the number of CPUs, -threadnum is reset to the number of CPUs.

For options 3 and 4, the specified num-thread need not be the actual number of threads running, because the index check utility determines the number of threads based on the structure of the index trees.

If all the key entries in the index tree are identical, the index check utility runs in non-threaded mode.

-lkwtmo seconds

An optional parameter that specifies how long the index check process waits, in seconds, when encountering a locked resource. When the time is exceeded, if the resource is still locked, the process times out and cancels the wait. The default wait time is 1800 seconds (30 minutes). The minimum wait time is 10 seconds.

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 following table describes the PROUTIL IDXCHECK options:

PROUTIL IDXCHECK lets you know whether you have to perform an index rebuild before running PROUTIL IDXBUILD. IDXCHECK performs the following operations for each index it checks:

• Reads the contents of the index and those of the file to verify that all the records are indexed and 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

You can run this utility using a read-only connection. You may run multiple diagnostic utilities concurrently, while the database broker is down, if they all support read-only connection and you run them in read-only mode. To run it in read-only mode, append -RO to the utility start command.

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 may 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 enabled by default.

The following table describes the PROUTIL IDXCHECK VALIDATION options.

Notes

• PROUTIL IDXCHECK automatically determines if a server is connected to the database and then decides in which mode to run (online or offline).
• When PROUTIL IDXCHECK is executed on an offline database in read-only mode, multiple read-only connections can run at the same time. Otherwise, no other process can access the database until IDXCHECK completes. See Read-only connections.
• 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 can 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 Maintain 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 named <dbname>.<pid>.xb to store the list of indexes being checked during execution of some options. This file is deleted when the process completes successfully. If the process terminates unsuccessfully, the file may not be deleted.
• For databases enabled for multi-tenancy, if you specify a tenant or group, then only those indexes belonging to the tenant or group are checked. If you specify shared, only the shared indexes are checked.
• For databases enabled for table partitioning, the Choose Indexes page 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 you select By Table, selecting a table and local index selects all partitions. You cannot select individual partitions.
  • When you select By Area, 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 available for selection.
  • When you select By Schema, all partitions of the local indexes are available for selection.
  • When you select By Partition, only local indexes belonging to the selected partitions are available for selection.
  • When you select the Some option and enter a local index, 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.