IDXCHECK introduced 4 levels of checks since
OpenEdge 10.1C+. Choose o/O to change ‘
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 (since 10.1C01)
L - Lock tables during the check
R - Reset error limit, current: 500
Default IDXCHECK Options Offline are: 1, 2 and 4
Default IDXCHECK Options Online are: 1 and 2 in OpenEdge 10.1C and the same as offline IDXCHECK (1,2 and 4) since OpenEdge 10.2A and later.
The syntax of online IDXCHECK is the same as the syntax of offline IDXCHECK:
$ proutil db-name -C idxcheck [ all | table [owner-name.]table-name | area area-name | schema schema-owner] [-NL] [-Bp] [-lkwtmo]
OE11.x on multi-tenant enabled databases: [ tenant tenant-name | group group-name | shared ]
Online IDXCHECK Locking Options: When -NL is specified on the IDXCHECK command line:
- IDXCHECK will not lock the records it processes during the check, ie there is no record/table lock. However, other utilities cannot alter indexes or tables because it holds a schema reader lock while the operation is running.
- The "L – Lock tables during the check" Option will not be available in the Validation Options menu selection when -NL is specified, because the session has already been instructed not to lock records.
- Running IDXCHECK with -NL will reduce contention since record locks are ignored. However it may report false errors if the operation runs in the middle of record updates. It therefore should be re-run against a subset of the errors identified without -NL to verify.
When Validation Options "L – Lock tables during the check" is selected:
- High locking contention on the tables during the index integrity check can result.
- IDXCHECK locks the entire tables of the indexes. It checks in shared mode during the scan so updates to the tables will be frozen as shared table locks are raised on the tables as their indexes are checked.
- Additionally, indexes cannot be altered by other utilities through an admin lock which is raised on the schema.
- While IDXCHECK is running with the "Lock Tables" Option, since other processes cannot update any records, nor delete/insert records, nor affect schema changes during the check, there will be no changes to the records and keys during the scan and a static report of the indexes in the table can be produced online allowing static information on the indexes such as key counts and record counts comparison in the report. This is the reason that the Lock Tables feature is provided as an Option for IDXCHECK online.
- If user has locked a record or table in EXCLUSIVE mode while running in this mode, IDXCHECK cannot be granted the shared table lock. The index integrity check process will need to wait (-lkwtmo) because the share mode and exclusive mode are in conflict. The online IDXCHECK operation will abort if the lock wait timeout is exceeded.
When neither "-NL" is specified nor "L – Lock tables during the check" are selected:
- IDXCHECK holds a shared schema lock and shared table lock on _db to prevent schema changes during the check and it will lock the records as it processes a key or record in shared-lock mode.
- It will not run into middle of record updates by other processes because of the shared lock, for same reason - other process can insert and delete records while IDXCHECK is running.
- Compared with the table lock in "L – Lock tables during the check", the record lock is held for a short time, after it is finished with the record or key, it will release the record lock, however, it will not compare key counts with record counts because those numbers can change.
- IDXCHECK may still be blocked on a record lock when it is processing the record, if a user holds an EXCLUSIVE-LOCK exceeding the lock wait timeout (-lkwtmo) period, the online IDXCHECK operation will abort.
The following Examples demonstrate the above:1. Run the following client code against an online sports2000 database:
FIND LAST Customer EXCLUSIVE-LOCK.
UPDATE name.
Example 1: Run IDXCHECK (while the EXCLUSIVE-LOCK record update is still running), where -NL AND -L are not used, in other words the 'default online' behaviour:
$ proutil <dbname> -C IDXCHECK all -lkwtmo 10 -Bp 32
Started index check with the following options:
Validate physical consistency of index blocks (14460)
Validate keys for each record (14461)
Validate key order (14463)
... <runs and then returns>
Processing blocks of table 2 in area 20.
Lock wait timeout of 60 seconds expired (8812)
Index check aborted. (2808)
IDXCHECK was blocked on the
record lock when it is processing the record that the other process has an exclusive lock on that was not released prior to the
lkwtmo. In this case, IDXCHECK has already processed some records/keys: “
Processing blocks of table 2 in area 20.” If
-NL had been used, it would not have been blocked but would have run into the middle of update and got a stale record.
Example 2: Run IDXCHECK (while the EXCLUSIVE-LOCK update is still running), where -NL is not used and -L is used, in other words, selecting the Option
O then
L to turn on Lock tables during validation.
$ proutil -C IDXCHECK all -lkwtmo 10 -Bp 32
Started index check with the following options:
Validate physical consistency of index blocks (14460)
Validate keys for each record (14461)
Validate key order (14463)
Lock tables during the validation (14464)
Lock wait timeout of 60 seconds expired (8812)
Unable to get table lock for table 2, returned -1218
Index check aborted. (2808)
IDXCHECK was blocked on the
record lock when it is processing the record that the other process has an exclusive lock on which was not released prior to the
lkwtmo. In this case, IDXCHECK
has not processed any keys on the customer table (table number 2) because when the "
Lock Tables" Option is in effect, the entire table is locked in shared mode. When -NL is specified, the Lock Tables option is not available.
In both Example 1 and 2 above, because the client is holding an exclusive record lock, IDXCHECK has to wait for that EXCLUSIVE-LOCK to be released, since it was not released within the lkwtmo, it aborted.
Example 3: RUN the following client-code instead with SHARE-LOCK against a sports2000 database:
FIND LAST Customer SHARE-LOCK.
UPDATE name.
Re-running IDXCHECK
Example 1 and
Example 2 above results in the IDXCHECK operation completing, as the lock on the record has not been upgraded to an EXCLUSIVE-LOCK.
When running IDXCHECK online, consider balancing accurate information against time:
- Always use Private Buffers (-Bp) with IDXCHECK when run online against a production database.
- Add -NL to IDXCHECK. Should any errors are reported verify by re-running a limited IDXCHECK without -NL for that specific table.index where the error is raised. Alternatively consider running IDXCHECK against a offline database copy for further analysing the extent of index corruption. This is the recommended approach.
- Using the "L (Lock tables during check)" Option, may increase the time for the operation to complete as it will have wait on any other process locking a record or the schema on a given table. Try to run with this option during periods of low OLTP activity against the table.indexes being checked.
- When not using -NL, add the -lkwtmo parameter to IDXCHECK to specify how long IDXCHECK will wait for a lock. When unspecified the default is 30 minutes which could retard the whole operation especially when neither -NL nor Lock Tables are selected. On the other hand, this may expose an inherent application locking problem which would raise a code review project.
- Online IDXCHECK generally performs better than IDXFIX if the same validations are selected. Specifically in Type II Areas, IDXCHECK starts from the first block of an object and only needs to go through all the blocks belonging to that object, which is faster than scanning the whole area. Type I areas on the other hand are scanned sequentially in both IDXFIX and IDXCHECK and the i/o cost is the same. All blocks in a Type I area are read and processed where many of these might not belong to the tables/indexes to be checked.
- When Option 4 detects errors online, it is advisable to run it again with Option L (Table Lock) to eliminate false positives. Refer to Article: IDXCHECK: Key is out of sequence error 8781 after online IDXFIX
- Against the OpenEdge Replication Target database, any user or utility connecting to a target database (apart from the Replication Agent under limited scenarios), is not expected to acquire record locks. In other words, everything apart from RPLA is a -RO user (except this is enforced with -ERO at the database level). A replication target database is considered in a crashed state unless all updates from the source have been applied. An online IDXCHECK may report false errors about indexes or records because it runs without locks on an unrecovered database.
To verify with certainty if you're getting false positives on corruption:
- Run IDXCHECK (with locking) against the source database, or
- Assure all updates from the source have been applied beforehand and no further updates are occurring when running IDXCHECK against the target
- IDXCHECK fails against the target database
