Correcting reported errors

CHECK PLUS message severity codes and return codes

Each reported message number ends with a letter that indicates its severity. Some messages identify conditions that prohibit further use of the structure by Db2 and require you to perform corrective action. These messages end with the letter E (error) or S (severe) and generate return code 8 or 12, respectively. Other messages identify conditions that were not expected but might allow further use of the structure by Db2. These messages end with the letter I (informational) or W (warning) and generate return code 0 or 4, respectively. For more information about the return codes and severity codes that CHECK PLUS issues, see the Utility Products for DB2 Messages Manual.

To assign your own severity indicators and override the default values, use the MSGSEV installation option. Use this option to change the severity indicator for any one or more of the messages from BMC52401 through BMC52599 to any of the severity indicators described in the Utility Products for DB2 Messages Manual. In addition, you can specify the letter O to omit the message from the listing.

Responding to space and index errors

CHECK SPACE and CHECK INDEX processing reports errors in the SYSPRINT output. This output contains messages that detail any structural or consistency errors or anomalies that CHECK PLUS finds during processing. In
addition to providing overall processing statistics, CHECK PLUS categorizes the errors by Db2 object and type of error.

The messages provide detailed information about intrapage errors, interpage errors, and any other structural or data integrity problems or anomalies that CHECK PLUS finds in the Db2 objects checked. You might need to refer to IBM’s Db2 documentation to fully understand the situations reported. In particular, the IBM DB2 Diagnosis Guide and Reference manual provide details about the format and contents of individual items on the various page types in spaces and index spaces.

Using this detailed information, you can analyze your data structures, the applications running against them, and your recent maintenance activity to determine the probable cause of each error. The IBM REPORT and DIAGNOSE utilities can assist you further in this process.

Not all errors are equally severe. Because the causes of structural problems vary, based on environment and processing factors, the information provided in the Utility Products for DB2 Messages Manual for each individual message provides an explanation of the message, but does not always require a user response. You are in the best position to determine the most effective course of action, if any is required, based on your analysis of the problem and knowledge of your environment.

Once you have analyzed the problem and taken appropriate corrective action, if any, to ensure that the problems do not continue to occur, you might need to correct the data structures themselves. Generally, if the errors prohibit further use of the data structures by Db2, the safest course of action is to restore or recover the Db2 object to a state in which the errors are not present.

However, in a few situations, you might want to actually change the incorrect information. If you are confident that you can make the appropriate corrections after examining the messages and analyzing your Db2 structures and processing environment, consider using the IBM Db2 REPAIR utility to physically modify information in Db2 objects. However, most data centers discourage the use of the REPAIR utility to modify Db2 objects because it can be destructive if used improperly.

Examples-of-CHECK-PLUS-jobs contain examples of the types of errors reported in the SYSPRINT data set.

Responding to constraint errors

CHECK DATA processing also reports errors in the SYSPRINT output. This output contains messages that detail any RI or check constraint violations found during processing. CHECK PLUS provides overall processing statistics and categorizes the individual RI violations by Db2 object and type of error. In addition to the errors reported in the SYSPRINT output, CHECK PLUS can also write the foreign keys that violate RI constraints to a discard data set. For more information, see Using the discard data set.

Examples-of-CHECK-PLUS-jobs contain examples of the types of errors reported in the SYSPRINT data set, plus examples of the discard data sets.

Using the discard data set

Depending on the DISCARDFMT option that you specify, the discard data set that CHECK PLUS creates contains either SQL DELETE statements or the keys for the rows with RI violations. This section describes these data sets.

For details about specifying the format of the discard data set, see the DISCARDFMT option.

DISCARDFMT SQLDEL or SPUFI

If you specify DISCARDFMT SQLDEL or SPUFI, the formatted data set contains one SQL DELETE statement for each distinct foreign key that is in violation. The only difference between the two formats is the record length of the DELETE statement.

Record length

If you specify SQLDEL, the record length is dictated by the length of the longest possible DELETE statement, which can exceed 72 characters. CHECK PLUS does not generate multiple-line DELETE statements as it would if using SPUFI format.

If you specify SPUFI, the record length is always 80 bytes, and only the first 72 bytes are used. This fixed record length allows you to use SPUFI, QMF, DSNTEP2, or DSNTIAD to execute the DELETE statements.

How to execute the DELETE statements

You can execute the DELETE statements by using either an SQL execution program or the SQL batch execution program that CHECK PLUS provides (ACKESQL). By deleting rows with RI violations outside of the checking process, CHECK PLUS allows the space to remain available during the delete process.

The ACKESQL program provides the following benefits:

• restart capability
• commit frequency control
• no record-length limitation
• optional ability to reset CHKP status during ACKESQL execution
• optional lock control

For more information about the ACKESQL program, including options to set during your CHECK job, see SQL-batch-execution-program.

Note the following considerations when executing the DELETE statements:

• You must clear the CHKP status before executing the DELETE statements. You can use one of the following methods to do so:

  • If you are using DISCARDFMT SQLDEL, specify RESET or RESETLOCK on the ACKESQL EXEC statement to reset the CHKP status of the affected object during ACKESQL execution.

    Important

    To enable RESET or RESETLOCK in ACKESQL, you must specify DSER=YES in the CHECK PLUS installation options or DISCARDSERIALIZE YES on the CHECK DATA command.

  • Use the IBM Db2 REPAIR utility to reset the CHKP status of the affected object.
  • Use START ACCESS(FORCE) to start the space. Normally, we do not recommend START ACCESS(FORCE) because this option also clears all other pending statuses.

• If you use SPUFI, QMF, DSNTEP2, or DSNTIAD to execute the DELETE statements, we recommend adding periodic commits.

Example discard data sets

The first example illustrates example DELETE statements in a discard data set that was created in the SQLDEL format of the DISCARDFMT option. The second example illustrates the same statements but created in the SPUFI format.

Discard data set in SQLDEL format

 Discard data set in SPUFI format

DISCARDFMT KEY

If you specify DISCARDFMT KEY, the formatted data set contains the key for each row that has an RI violation. You can use this information as input to user-written programs to make appropriate corrections. illustrates the record format for discard data sets that were created in the KEY format of the DISCARDFMT option.

Record format of a discard data set in KEY format