Improving Performance for Index Checks

The oncheck utility provides better concurrency for tables that use row locking. When a table uses page locking, oncheck places a shared lock on the table when it performs index checks. Shared locks do not allow other users to perform updates, inserts, or deletes on the table while oncheck checks or prints the index information.

If the table uses page locking, the database server returns the following message if you run oncheck without the -x option:

WARNING: index check requires a s-lock on stable whose 
lock level is page.

For detailed information on oncheck locking, see the utilities chapter in the IBM Informix: Dynamic Server Administrator's Reference.

The following summary describes locking performed during index checks:

• By default, the database server does not place a shared lock on the table when you check an index with the oncheck -ci, -cI, -pk, -pK, -pl, or -pL options unless the table uses page locking. When oncheck checks indexes for a table with page locking, it places a shared lock on the table, so no other users can perform updates, inserts, or deletes until the check has completed.
• By not placing a shared lock on tables using row locks during index checks, the oncheck utility cannot be as accurate in the index check. For absolute assurance of a complete index check, execute oncheck 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 completes.

You can query the systables system catalog table to see the current lock level of the table, as the following sample SQL statement shows:

SELECT locklevel FROM systables
   WHERE tabname = "customer"

If you do not see a value of R (for row) in the locklevel column, you can modify the lock level, as the following sample SQL statement shows:

ALTER TABLE tab1 LOCK MODE (ROW);

Row locking might add other side effects, such as an overall increase in lock usage. For more information about locking levels, see Locking.