The CHECK INDEX Option

 <  Day Day Up  >  

The CHECK INDEX Option

The CHECK INDEX option of the CHECK utility verifies the consistency of index data and its corresponding table data. This option identifies and reports RID pointer errors for missing index keys and index key mismatches . CHECK INDEX does not correct invalid index entries; it merely identifies them for future correction.

CHECK INDEX can run against an entire index or a single index partition. CHECK INDEX can identify three problems:

  • No corresponding row in the table for a given index entry.

  • No index entry for a valid table row.

  • The data in the indexed columns for the table does not match the corresponding index key for a given matching RID.

Additionally, when checking an auxiliary table index, CHECK INDEX verifies that each LOB is represented by an index entry, and that an index entry exists for every LOB.

To correct errors reported by CHECK INDEX , the user can execute the REBUILD INDEX utility to rebuild the index based on the current table data. Alternatively, the RECOVER INDEX utility can be used to apply an index image copy. If the RECOVER option is chosen , care must be taken to ensure that the recovery results in an index that matches the current state of the data. In general, REBUILD is a better option than RECOVER for fixing index errors.

When mismatch-type errors occur, however, a data analyst who is experienced with the application that contains the problem table or index should research the cause of the anomaly. The predominant causes of invalid indexes are the uncontrolled use of the DSN1COPY utility and the partial recovery of application tables or indexes.

The JCL to execute the CHECK INDEX utility is shown in Listing 31.4.

Listing 31.4. CHECK INDEX JCL
 //DB2JOBU  JOB (UTILITY),'DB2 CHECK INDEX',MSGCLASS=X,CLASS=X, //   NOTIFY=USER //* //**************************************************************** //* //*            DB2 CHECK INDEX UTILITY //* //**************************************************************** //* //UTIL EXEC DSNUPROC,SYSTEM=DSN,UID='CHEKINDX',UTPROC=" //* //*  UTILITY WORK DATASETS //* //DSNUPROC.SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(2,1)) //DSNUPROC.SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(2,1)) //DSNUPROC.SYSUT1 DD DSN=&&SYSUT1, //        UNIT=SYSDA,SPACE=(CYL,(2,1)),DCB=BUFNO=20 //DSNUPROC.UTPRINT DD SYSOUT=X //* //*  UTILITY INPUT CONTROL STATEMENTS //*       The first CHECK INDEX statement checks all indexes //*       for the named table space. //*       The next two CHECK INDEX statements check only the //*       specifically named indexes. //* //DSNUPROC.SYSIN    DD  *     CHECK INDEX(ALL) TABLESPACE DSN8D81A.DSN8S81D     CHECK INDEX (DSN8810.XACT1)     CHECK INDEX (DSN8810.XACT2) /* // 

CHECK INDEX Phases

The are five phases of the CHECK INDEX utility:

UTILINIT

Sets up and initializes the CHECK INDEX utility

UNLOAD

Unloads index entries to the SYSUT1 DD

SORT

Sorts the unloaded index entries using SORTOUT DD

CHECKIDX

Scans the table to validate the sorted index entries against the table data

UTILTERM

Performs the final utility cleanup


Estimating CHECK INDEX Work Data Set Sizes

graphics/v7_icon.gif

The CHECK INDEX utility requires work data sets to accomplish index checking. Once again, though, as of DB2 V7 you can set up the JCL for CHECK INDEX to allocate the required data sets dynamically. But this is not required; you may still choose to explicitly allocate work data sets for your utility jobs.


The following formulas help you estimate the sizes for the work data sets required by the CHECK INDEX utility. These calculations provide estimated sizes only. More complex and precise calculations can be found in the DB2 Utility Guide and Reference manual, but these formulas should produce comparable results:

 

 SYSUT1 = (size of the largest index + 13) x (total number of rows in largest index to be graphics/ccc.gif checked) SORTWKxx = (size of SYSUT1) x 2 

After calculating the estimated size, in bytes, for each work data set, convert the number into cylinders , rounding up to the next whole cylinder. Allocating work data sets in cylinder increments enhances the utility's performance. This is true for all utilities.

CHECK INDEX Locking Considerations

The CHECK INDEX utility can run concurrently with all utilities except the following:

CHECK DATA

LOAD

REBUILD INDEX

RECOVER INDEX

RECOVER TABLESPACE

REORG INDEX

REPAIR REPLACE

REPAIR DELETE

REORG TABLESPACE UNLOAD CONTINUE

 

REORG TABLESPACE UNLOAD PAUSE

 

graphics/v8_icon.gif

CHECK INDEX will drain write claim classes for both the index or index partition and the table space being processed . The partition can be a partition of a partitioning index, a partition of a DPSI, or a logical partition of a (type 2) index.


CHECK INDEX Guidelines

Run CHECK INDEX Only When Needed

Inconsistencies in DB2 indexes are rare in adequately controlled and administered environments. For this reason, do not regularly schedule the execution of the CHECK INDEX utility for the production indexes in your shop. It usually wastes processing time and increases an application's batch window.

The CHECK INDEX utility should be run only when inconsistent data is observed or when an uncontrolled environment allows (or permits ) the liberal use of DSN1COPY or partial recovery.

NOTE

Consider running CHECK INDEX for an entire DB2 subsystem prior to a migration. If a corrupt index exists, you can correct it prior to the migration.


Use CHECK INDEX After Potentially Dangerous Operations

Execute CHECK INDEX after a conditional restart or a partial application recovery.

Use CHECK INDEX on the DB2 Catalog When Necessary

CHECK INDEX can be used to check DB2 Catalog and DB2 Directory indexes.

CAUTION

If you run CHECK INDEX on either SYSIBM.DSNLUX01 or SYSIBM.DSNLUX02 , then CHECK INDEX must be the only utility within the job step.


Check Indexes at the Partition Level When Possible

CHECK INDEX can be run at the partition. Pinpoint integrity checking can be performed if the user knows which index partition has corrupted entries. Running CHECK INDEX on that partition only can save processing time.

Keep in mind, however, that when running CHECK INDEX on a single logical partition of a secondary index, certain types of errors will not be detected . For example, duplicate unique keys might exist across partitions, but if only one partition is being checked the problem will not be found.

Another problem that might go undetected if partition-level checking is being run is out of sequence keys. For example, consider two partitions where the keys are as follows :

Partition 1 ”1,3,3,5,7

Partition 2 ”6,8,9

The keys within each partition are in sequence, but the keys for the entire index are out of sequence because the 6 in partition 2 comes after the 7 in partition 1. But if only one partition is being checked, DB2 will not uncover this problem.

Rerun CHECK INDEX After an Abend

The CHECK INDEX utility cannot be restarted. If it abends during execution, determine the cause of the abend, terminate the utility, and rerun. The most common cause for CHECK INDEX failure is a timeout because the index is locked out by another user.

Buffer CHECK INDEX Work Data Sets Appropriately

Ensure that adequate data set buffering is specified for the work data sets. The BUFNO parameter can be used on the DCB information of JCL DD statements to change buffering. The BUFNO parameter creates read and write buffers in main storage for this data set, thereby enhancing the performance of the utility. The default value for BUFNO is 20.

Ensure that sufficient memory (real or expanded) is available, however, before increasing the BUFNO specification for your CHECK INDEX work data sets.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net