Improving performance for index checks

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.

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);