Database Administration

Reference : PROUTIL Utility : PROUTIL IDXCHECK qualifier

PROUTIL IDXCHECK qualifier

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

Syntax

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:

Note: Depending on the features enabled on your database, some menu items do not appear.

The table below describes the PROUTIL IDXCHECK options.

Table 100. PROUTIL IDXCHECK options
Option	Action
All	Checks all the indexes
Some	Prompts you for the indexes you want to check 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, IDXCHECK then prompts for a partition name.
By Area	Prompts you for the area containing the indexes you want to check
By Schema	Prompts you for the schema owner of the indexes you want to check
By Table	Prompts you for the table containing the indexes you want to check
By Partition	Prompts you for the table partitions containing the indexes you want to check.
Validation	Change the validation options
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 a tenant, group, or shared was not specified on the command line.
Quit	Quits without checking any indexes

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
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.

Table 101. PROUTIL IDXCHECK VALIDATION options
Option	Action
1	Validates physical consistency of index blocks
2	Validates keys for each record
3	Validates record for each key
4	Validates key order
5	Validates the tree. Detects a vulnerability with indexes that span the 32/64 bit boundary within your index area.
L	Locks the tables during the check Note: This option is only available when the database is online
R	Resets the error limit; this value is set to 500
C	Continues to execute the check
Quit	Quits without checking any indexes

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 MaintainingDatabase 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.