The REPAIR SET Option

The REPAIR SET Option

When the REPAIR utility is executed with the SET option, it can be used to reset copy pending, check pending, and recover pending flags. Pending flags can be set at the partition level, as well as at the table space level. For an in-depth discussion of the pending status flags, refer to the section titled "The Pending States" in Chapter 37, "DB2 Utility and Command Guidelines." In general, these flags are maintained by DB2 to indicate the status of table spaces and indexes. When DB2 turns on a flag for a table space or index, it indicates that the object is in an indeterminate state.

When the copy pending flag is set, it indicates that the COPY utility must be used to back up the table space or partition to ensure adequate recoverability. Copy pending status is set when unlogged changes have been made to DB2 table spaces, or when a reference to a full image copy is no longer available in the DB2 Catalog.

The check pending flag indicates that the CHECK DATA utility should be run because data has been inserted into a table containing a referential constraint without ensuring that the data conforms to the referential integrity. The auxiliary check pending flag indicates that there is a problem with a base table reference to a LOB column in an auxiliary table.

The recover pending flag indicates that the table space or the index must be recovered because a utility operating on that object has ended abnormally, possibly causing inconsistent or corrupted data.

The rebuild pending flag indicates that an index does not match the table data and needs to be rebuilt. Sometimes, however, these flags are set by DB2 but the corresponding utility does not need to be run because of other application factors. In this case, the REPAIR SET utility can be run to reset the appropriate pending flag.

Listing 31.7 shows JCL that can be used to reset check pending, copy pending, and recover pending restrictions for the sample table spaces. It also contains a REPAIR statement to reset the recover pending status for an index on one of the sample tables.

Listing 31.7. REPAIR SET JCL

 //DB2JOBU  JOB (UTILITY),'DB2 REPAIR SET',MSGCLASS=X,CLASS=X, //   NOTIFY=USER //* //**************************************************************** //* //*       DB2 REPAIR UTILITY  : : RESET PENDING FLAGS //* //**************************************************************** //* //UTIL EXEC DSNUPROC,SYSTEM=DSN,UID='REPRSETP',UTPROC=" //* //*  UTILITY INPUT CONTROL STATEMENTS //*    1. The first REPAIR statement resets the copy pending //*       status for the named table space. //*    2. The second REPAIR statement resets the check pending //*       status for two table spaces. //*    3. The third REPAIR statement resets the recover pending //*       status for the named table space. //*    4. The fourth and final REPAIR statement resets the //*       copy pending status for the named index. //* //DSNUPROC.SYSIN    DD  *     REPAIR SET TABLESPACE DSN8D81A.DSN8S81E  NOCOPYPEND     REPAIR SET TABLESPACE DSN8D81A.DSN8S81E  NOCHECKPEND            SET TABLESPACE DSN8D81A.DSN8S81C  NOCHECKPEND     REPAIR SET TABLESPACE DSN8D81A.DSN8S81R  NORCVRPEND     REPAIR SET INDEX      DSN8810.XPROJAC1   NORCVRPEND /* // 

REPAIR SET Guidelines

Favor the COPY Utility over REPAIR SET NOCOPYPEND

To reset the copy pending flag, it is almost always better to run the COPY utility to take a full image copy rather than use REPAIR . Situations contrary to this advice follow:

• Data loaded from a stable source does not need to be copied if the source is maintained. (The data can always be reloaded.) If the data is loaded with the LOG NO option, run REPAIR to reset the check pending condition rather than create an image copy that will never be used.

• When the MODIFY RECOVERY utility is run ”deleting the last image copy for a table space ”DB2 sets the copy pending flag. If the image copy data set deleted from the SYSIBM.SYSCOPY table is still available, however, recovery to that image copy can be accomplished using the DSN1COPY service aid. This requires manual intervention to recover a table space and is not recommended.

• Test data with a short life span often does not need to be copied because it can be easily re-created. If the copy pending restriction is set for a table of this nature, it is usually quicker to run REPAIR than to create an image copy.

Favor the CHECK DATA Utility over REPAIR SET NOCHECKPEND

To reset the check pending flag, it is almost always better to run the CHECK DATA utility to enforce referential constraints rather than use REPAIR . Situations contrary to this advice follow:

• If referential constraint violations are checked by an application program later in a job stream, the REPAIR utility can be run to reset the copy pending restriction. This allows the subsequent deletion of referential constraint violations by the application program. However, the DB2 CHECK DATA utility generally is infallible, and application programs are not, so this scenario should be avoided unless you are retrofitting referential integrity into a system that already exists without it.

• If check pending has been set for a table space containing a table that will have data loaded into it using the LOAD utility (with the REPLACE and ENFORCE CONSTRAINTS options) before data will be accessed, the CHECK DATA utility can be bypassed because the LOAD utility enforces the referential constraints.

Favor the RECOVER Utility over REPAIR SET NORCVRPEND

To reset the recover pending flag, it is almost always better to run the RECOVER utility to recover a DB2 table space or index to a time or state rather than use REPAIR .

There is only one situation contrary to this advice. When the LOAD utility abnormally terminates, the recover pending flag is set, and running LOAD REPLACE rather than RECOVER is appropriate. It is never advisable to set the recover pending flag using REPAIR unless the data is not critical and can be lost without dire consequences.

Specify LEVELID to Use a Down-Level Data Set

The LEVELID parameter sets the level identifier of the named table space or partition to a new identifier.

You cannot use LEVELID with an open table space or partition, a table space or partition with outstanding in-doubt log records, or pages in the logical page list (LPL).

CAUTION

Actions impacting a down-level data set might cause data integrity and accuracy problems. Use this option at your own risk as IBM will take no responsibility for data problems resulting from the use of down-level data sets.