oncheck -ci and -cI: Check index node links

Use the oncheck -ci and oncheck -cI commands to check the ordering of key values and the consistency of horizontal and vertical node links for all indexes associated with the specified table.

The oncheck -cI command also checks that the key value tied to a rowid in an index is the same as the key value in the row. The -cI option does not cross-check data on a functional index.

Figure 1. Syntax:

If you do not specify an index, the option checks all indexes. If you do not specify a table, the option checks all tables in the database.

The same -ci repair options are available with -cI. If oncheck -ci or oncheck -cI detects inconsistencies, it prompts you for confirmation to repair the problem index. If you specify the -y (yes) option, indexes are automatically repaired. If you specify the -n (no) option, the problem is reported but not repaired; no prompting occurs.

If oncheck does not find inconsistencies, the following message appears:

validating indexes......

The message displays the names of the indexes that oncheck is checking.

Note: Using oncheck to rebuild indexes can be time consuming. Processing is usually faster if you use the SQL statements DROP INDEX and CREATE INDEX to drop and re-create the index.

The following example checks all indexes on the customer table:

oncheck -cI -n stores_demo:customer

The following example checks the index zip_ix on the customer table:

oncheck -cI -n stores_demo:customer#zip_ix

If indexes are fragmented on multiple partitions in the same dbspace, the oncheck -ci and oncheck -cI commands show the partition names. The following example show typical output for an index that has fragments in multiple partitions in the same dbspace:

Validating indexes for multipart:informix.t1...
                Index idx_t1
                  Index  fragment partition part_1 in DBspace dbs1
                  Index  fragment partition part_2 in DBspace dbs1
                  Index  fragment partition part_3 in DBspace dbs1
                  Index  fragment partition part_4 in DBspace dbs1
                  Index  fragment partition part_5 in DBspace dbs1

By default, the database server does not place a shared lock on the table when you check an index with the oncheck -ci or oncheck -cI commands unless the table uses page locking. For absolute assurance of a complete index check, you can execute oncheck -cior oncheck -cI with the -x option. With the -x option, oncheck places a shared lock on the table, and no other users can perform updates, inserts, or deletes until the check has completed. For more information about using oncheck -ci and oncheck -cI with the -x option, Turn On Locking with -x.

When you execute oncheck on an external index, the user-defined access method is responsible for checking and repairing an index. If an index that employs a user-defined access method cannot find the access method, the database server reports an error. The oncheck utility does not repair inconsistencies in external indexes. You should not use oncheck -cI on a table that contains more than one type of index.

The oncheck utility requires sort space when examining an index. The amount of sort space required is the same as that needed to build the index. For information about calculating the amount of temporary space needed, see Estimating temporary space for index builds. If you receive the error "no free disk space for sort," you must estimate the amount of temporary space needed and make that space available.

For more information about indexes, see Structure of B-Tree Index Pages.