Syntax and options of the REPAIR control statement

The REPAIR utility control statement, with its multiple options, defines the function that the utility job performs.

You can create a control statement with the ISPF/PDF edit function. After creating it, save it in a sequential or partitioned data set. When you create the JCL for running the job, use the SYSIN DD statement to specify the name of the data set that contains the utility control statement.

Start of change

REPAIR syntax diagram

Read syntax diagramSkip visual syntax diagram REPAIR OBJECT LOGYESLOGNOlocate blockset statementdbd statementlevel-id statementcatalog statementsystem-pages statementwrite-log statementCLONE
locate block
Read syntax diagramSkip visual syntax diagram LOCATE locate-table-space-speclocate-index-specverify statementreplace statementdelete statementdump statementSHRLEVEL CHANGELOB-table-space-specXML-table-space-spec
locate-table-space-spec
Read syntax diagramSkip visual syntax diagram TABLESPACE database-name. table-space-name RIDX' byte-string'PAGEX' byte-string'PARTintegerPAGEintegerKEYliteralINDEXindex-name
locate-index-spec
Read syntax diagramSkip visual syntax diagramINDEXindex-nameINDEXSPACEindex-space-namePARTinteger PAGE integerX' byte-string'
LOB-table-space-spec
Read syntax diagramSkip visual syntax diagram TABLESPACE database-name. table-space-name ROWID X' byte-string' VERSION X' byte-string' delete statementdump statement
XML-table-space-spec
Read syntax diagramSkip visual syntax diagram TABLESPACE database-name. xml-table-space-name DOCID X' byte-string' delete statement
verify statement
Read syntax diagramSkip visual syntax diagram VERIFY OFFSET0OFFSETintegerX' byte-string' DATA X' byte-string'' character-string'
replace statement
Read syntax diagramSkip visual syntax diagram REPLACE RESETOFFSET0OFFSETintegerX' byte-string'DATAX' byte-string'' character-string'
delete statement
Read syntax diagramSkip visual syntax diagram DELETE DATAONLY
dump statement
Read syntax diagramSkip visual syntax diagram DUMP OFFSET0OFFSETintegerX' byte-string'LENGTHX' byte-string'integerPAGESX' byte-string'integer*MAPpagesDATApages
set statement
Read syntax diagramSkip visual syntax diagram SET TABLESPACEdatabase-name.table-space-namePARTintegerNOCOPYPENDNORCVRPENDNOCHECKPENDNOAUXWARNNOAUXCHKPNOAREORPENDSTARNOAREORPENDPRONOPROINDEX( index-namePARTinteger)(ALL)TABLESPACEdatabase-name.table-space-nameINDEXSPACE(database-name. index-space-namePARTinteger)(ALL)TABLESPACEdatabase-name.table-space-nameNOCOPYPENDNORCVRPENDNORBDPENDNOCHECKPENDNOAREORPENDSTARNOAREORPENDRBDPENDPSRBDPEND
dbd statement
Read syntax diagramSkip visual syntax diagram DBD DROPDATABASEdatabase-nameDBIDX' dbid'TESTDIAGNOSEREBUILDDATABASEdatabase-nameOUTDDNddname
level-id statement
Read syntax diagramSkip visual syntax diagram LEVELID TABLESPACEdatabase-name.table-space-nameINDEXcreator-id.index-nameINDEXSPACEdatabase-name.index-space-namePARTinteger
catalog statement
Read syntax diagramSkip visual syntax diagram CATALOG TABLESPACEdatabase-name.table-space-nameINDEXcreator-id.index-nameINDEXSPACEdatabase-name.index-space-nameTEST
system-pages statement
Read syntax diagramSkip visual syntax diagram INSERTVERSIONPAGES SETCURRENTVERSION TABLESPACE database-name. table-space-name SHRLEVEL CHANGE1
write-log statement
Read syntax diagramSkip visual syntax diagram WRITELOG TABLESPACE database-name. table-space-name PARTinteger TYPE X'4400' SUBTYPE X'0083' TEXT X' byte-string'
Notes:
  • 1 SHRLEVEL CHANGE is not valid if SETCURRENTVERSION is specified
End of change

REPAIR option descriptions

OBJECT
Indicates that an object is to be repaired. This keyword is optional.
LOG
Indicates whether the changes that REPAIR makes are to be logged. If the changes are to be logged, they are applied again if the data is recovered.
YES
Indicates that the changes are to be logged.

REPAIR LOG YES cannot override the LOG NO attribute of a table space.

NO
Start of changeIndicates that the changes are not to be logged. You cannot use this option with a DELETE or WRITELOG statement.

REPAIR LOG NO can override the LOG YES attribute of a table space.

End of change
TABLESPACE database-name.table-space-name
Specifies the table space (and, optionally, the database to which it belongs) whose level identifier is to be reset (if you specify LEVELID) or whose version identifier is to be updated (if you specify CATALOG).
database-name
The name of the database to which the table space belongs.

The default value is DSNDB04.

table-space-name
The name of the table space.
INDEX
Start of changeSpecifies the index whose level identifier is to be reset (if you specify LEVELID) or whose version identifier is to be updated (if you specify CATALOG).End of change
creator-id
The creator of the index. Specifying this qualifier is optional.
index-name
The name of the index. Enclose the index name in quotation marks if the name contains a blank.
You can specify either INDEX or INDEXSPACE to identify an index. To specify multiple indexes, repeat the keyword.
INDEXSPACE
Start of changeSpecifies the index space for the index whose level identifier is to be reset (if you specify LEVELID) or whose version identifier is to be updated (if you specify CATALOG). You can obtain the index space name for an index from the SYSIBM.SYSINDEXES catalog table. The index space name must be qualified.End of change
database-name
The name of the database to which the index space belongs.
index-space-name
The name of the index space.
You can specify either INDEX or INDEXSPACE to identify an index. To specify multiple indexes, repeat the keyword.
PART
Identifies a partition of the table space or index (including a partition of a data-partitioned secondary index).

integer is the physical partition number. It must be in the range from 1 to the number of partitions that are defined for the table space.

CLONE
Indicates that REPAIR is to process only the specified objects that are table spaces that contain clone tables, indexes on clone tables, or index spaces that contain indexes on clone tables. If you specify CLONE, you cannot specify CATALOG or WRITELOG. Clones cannot be created for tables with active versions.

If you specify SET with CLONE, the status is changed for only the specified table spaces and their indexes. The CLONE keyword applies to all SET statements and LOCATE statements within the same REPAIR utility control statement.

LOCATE block

A LOCATE block is a set of statements, each with its own options, that begins with a LOCATE statement and ends with the next LOCATE or SET statement, or with the end of the job. You can include more than one LOCATE block in a REPAIR utility statement.

In any LOCATE block, you can use VERIFY, REPLACE, or DUMP as often as you like; you can use DELETE only once.

LOCATE block: LOCATE TABLESPACE statement option descriptions

The LOCATE TABLESPACE statement locates data that is to be repaired within a table space.

One LOCATE statement is required for each unit of data that is to be repaired. Several LOCATE statements can appear after each REPAIR statement.

If a REPAIR statement is followed by more than one LOCATE statement, all processing that is caused by VERIFY, REPLACE, and DUMP statements is committed before the next LOCATE statement is processed.
TABLESPACE
Specifies the base table space or XML table space (and, optionally, the database to which it belongs) in which data is to be located for repair.
database-name
The name of the database to which the base table space or XML table space belongs. This is optional.
table-space-name
The name of the base table space that contains the data to be repaired.
xml-table-space-name
The name of the XML table space that contains the data to be repaired.
Start of changePAGEEnd of change
Start of changeSpecifies the relative or absolute page number within the table space, partitioned table space, or index that is to be operated on. The first page, in either case, is 0 (zero). For a table space with absolute page numbering, you can specify a PAGE keyword with a relative or absolute page number. For a table space with relative page numbering, you must specify a PART keyword and a PAGE keyword with a relative page number.
integer
integer is a decimal number from one to six digits in length.
X'byte-string'
Specifies that the data of interest is an entire page. Use X'byte-string' for only absolute page numbers, in which case the partition number is embedded in the page.

The specified offsets in byte-string and in subsequent statements are relative to the beginning of the page. The first byte of the page is at offset 0.

byte-string is a hexadecimal value from one to eight characters in length. You do not need to enter leading zeros. Enclose the byte-string between apostrophes, and precede it with X.

End of change
Start of changePART integer End of change
Start of changeSpecifies the partition that contains the page that is to be located. Part is valid only for partitioned table spaces, and must be specified for partitioned table spaces with relative page numbering.

integer is the number of the partition.

If you specify PART, you must specify an integer value for PAGE.

End of change
Start of changeRID X'byte-string'End of change
Start of changeSpecifies that the data that is to be located is a single row. The specified offsets in byte-string and in subsequent statements are relative to the beginning of the row. The first byte of the stored row prefix is at offset 0.

byte-string can be a hexadecimal value from one to eight characters in length. You do not need to enter leading zeros. Enclose the byte string between apostrophes, and precede it with an X.

End of change
KEY literal
Specifies that the data that is to be located is a single row, identified by literal. The specified offsets in subsequent statements are relative to the beginning of the row. The first byte of the stored row prefix is at offset 0.

literal is any SQL constant that can be compared with the key values of the named index.

Character constants that are specified within the LOCATE KEY option cannot be specified as ASCII or Unicode character strings. No conversion of the values is performed. To use this option when the table space is ASCII or Unicode, you should specify the values as hexadecimal constants.

If more than one row has the value literal in the key column, REPAIR returns a list of record identifiers (RIDs) for records with that key value, but does not perform any other operations (verify, replace, delete, or dump) until the next LOCATE TABLESPACE statement is encountered. To repair the proper data, write a LOCATE TABLESPACE statement that selects the row that you want, using the RID option, the PAGE option, or a different KEY and INDEX option. Then, execute REPAIR again.

SHRLEVEL
Indicates the type of access that is to be allowed for the index, table space, or partition that is to be repaired during REPAIR processing.

If you do not specify SHRLEVEL and you do specify DUMP or VERIFY, applications can read but not write the area.

If you do not specify SHRLEVEL and you do specify DELETE or REPLACE, applications cannot read or write the area.

CHANGE
Specifies that applications can read and write during the VERIFY, REPLACE, DELETE, and DUMP operation.
ROWID X'byte-string'
Specifies that the data that is to be located is a LOB in a LOB table space.

byte-string is the row ID that identifies the LOB column.

Use the ROWID keyword to repair an orphaned LOB row. You can find the ROWID in the output from the CHECK LOB utility. If you specify the ROWID keyword, the specified table space must be a LOB table space.

VERSION X'byte-string'
Specifies that the data that is to be located is a LOB in a LOB table space.

byte-string is the version number that identifies the version of the LOB column.

Use the VERSION keyword to repair an orphaned LOB column. You can find the VERSION number in the output of the CHECK LOB utility or an out-of-synch LOB that is reported by the CHECK DATA utility. If you specify the VERSION keyword, the specified table space must be a LOB table space.

LOCATE block: LOCATE INDEX statement and LOCATE INDEXSPACE statement option descriptions

The LOCATE INDEX (or INDEXSPACE) statement locates data that is to be repaired within an index. You can specify indexes by either their index name or their index space name.

One LOCATE statement is required for each unit of data that is to be repaired. Multiple LOCATE statements can appear after each REPAIR statement.

If a REPAIR statement is followed by multiple LOCATE statements, all processing that is caused by VERIFY, REPLACE, and DUMP statements is committed before the next LOCATE statement is processed.
INDEX index-name
Specifies a particular index that is to be used to find the row that contains the key. When you are locating an index by key, the index that you specify must be a single-column index.

index-name is the qualified or unqualified name of the index. If you omit the qualifier creator ID, the user identifier for the utility job is used. Enclose the index name in quotation marks if the name contains a blank.

INDEXSPACE index-space-name
Specifies the index space for a particular index that is to be used to find the row that contains the key. Look in the SYSIBM.SYSINDEXES catalog table to find the index space name for an index. When you are locating an index by key, the index that you specify must be a single-column index.

index-space-name is the qualified name of the index space, in the form database-name.index-space-name.

PAGE integer
Specifies the relative page number within the index space that is to be operated on. The first page is 0 (zero).
integer
integer is a decimal number from one to six digits in length.
X'byte-string'
Specifies that the data of interest is an entire page. The specified offsets in byte-string and in subsequent statements are relative to the beginning of the page. The first byte of the page is at offset 0.

byte-string is a hexadecimal value from one to eight characters in length. You do not need to enter leading zeros. Enclose the byte-string between apostrophes, and precede it with X.

PART integer
Specifies the partition number of the partitioning index that contains the page that is to be located. The PART keyword is valid only for indexes of partitioned table spaces.

integer is the number of the partitioning index.

LOCATE block: VERIFY statement

The VERIFY statement tests whether a particular data area contains a specified value. Depending on the outcome of this test, the REPAIR utility performs the following actions:
  • If the data area does contain the value, subsequent operations in the same LOCATE block are allowed to proceed.
  • If any data area does not contain its specified value, all subsequent operations in the same LOCATE block are inhibited.

LOCATE block: VERIFY statement option descriptions

OFFSET
Locates the data that is to be tested by a relative byte address (RBA) within the row or page.
integer
Identifies the offset as an integer.

The default value is 0, the first byte of the area that is identified by the previous LOCATE statement.

X'byte-string'
Identifies the offset as one to four hexadecimal characters. You do not need to enter leading zeros. Enclose the byte string between apostrophes, and precede it with X.
DATA
Specifies what data must be present at the current location before a change is made.
Character constants that are specified within the VERIFY DATA option cannot be specified as ASCII or Unicode character strings. No conversion of the values is performed. To use this option when the table space is ASCII or Unicode, you should specify the values as hexadecimal constants.
X'byte-string'
Specifies an even number, from 2 to 32, of hexadecimal characters that must be present. You do not need to enter leading zeros. Enclose the byte string between apostrophes, and precede it with X.
'character-string'
Specifies any character string that must be present.

LOCATE block: REPLACE statement

The REPLACE statement replaces data at a particular location. The statement is contained within a LOCATE block. If any VERIFY statement within that block finds a data area that does not contain its specified data, the REPLACE operation is inhibited.

LOCATE block: REPLACE statement option descriptions

RESET
Specifies that the inconsistent data indicator is to be reset. A page for which this indicator is on is considered in error, and the indicator must be reset before you can access the page. Numbers of pages with inconsistent data are reported at the time that they are encountered.

The option also resets the PGCOMB flag bit in the first byte of the page to agree with the bit code in the last byte of the page.

OFFSET
Indicates where data is to be replaced by a relative byte address (RBA) within the row or page. Only one OFFSET and one DATA specification are acted on for each REPLACE statement.
integer
Specifies the offset as an integer.

The default value is 0, the first byte of the area that is identified by the previous LOCATE statement.

X'byte-string'
Specifies the offset as one to four hexadecimal characters. You do not need to enter leading zeros. Enclose the byte string between apostrophes, and precede it with X.
DATA
Specifies the new data that is to be entered. Only one OFFSET and one DATA specification are acted on for each REPLACE statement.
Important: Do not run REPAIR with the REPLACE, OFFSET, and DATA options on a compressed table space.
Character constants that are specified within the VERIFY DATA option cannot be specified as ASCII or Unicode character strings. The values are not converted. To use this option when the table space is ASCII or Unicode, specify the values as hexadecimal constants.
X'byte-string'
Specifies an even number, from 2 to 32, of hexadecimal characters that are to replace the current data. You do not need to enter leading zeros. Enclose the byte string between apostrophes, and precede it with X.
'character-string'
Specifies any character string that is to replace the current data.

LOCATE block: DELETE statement

The DELETE statement deletes a single row of data that has been located by a RID or KEY option. The statement is contained within a LOCATE block. If any VERIFY statement within that block finds a data area that does not contain its specified data, the DELETE operation is inhibited.

The DELETE statement operates without regard for referential constraints. If you delete a parent row, its dependent rows remain unchanged in the table space.

In any LOCATE block, you can include no more than one DELETE option.

If you have coded any of the following options, you cannot use DELETE:
  • The LOG NO option on the REPAIR statement
  • A LOCATE INDEX statement to begin the LOCATE block
  • The PAGE option on the LOCATE TABLESPACE statement in the same LOCATE block
  • A REPLACE statement for the same row of data

When you specify LOCATE ROWID for a LOB table space, the LOB that is specified by ROWID is deleted with its index entry. All pages that are occupied by the LOB are converted to free space. The DELETE statement does not remove any reference to the deleted LOB from the base table space.

When you specify LOCATE DOCID for an XML table space, the XML document that is specified by DOCID is deleted with its NodeID index entries. All rows that are occupied by the XML document are deleted from the XML table space. The DELETE statement does not remove any reference to the deleted XML document from the base table space. The LOCATE DOCID statement is generated by CHECK DATA SHRLEVEL CHANGE in order to remove corrupted XML documents from the XML table space.

REPAIR DELETE can delete the following data rows when the specified conditions exists:

  • A compressed row without an index defined on the table
  • A compressed row with an index defined on the table and a valid dictionary exists to decompress the row
  • A compressed or uncompressed data row that is missing an index entry
  • A compressed row with an index defined on the table, but the dictionary is invalid
  • An uncompressed row without an index
  • An uncompressed row with valid Index

LOCATE block: DELETE statement option descriptions

DATAONLY
Specifies that REPAIR should delete only the data record that is specified by the LOCATE RID statement. Any associated indexes, LOB columns, XML columns, or referential integrity constraints are not deleted.

You can specify the DATAONLY option only when REPAIR locates a single row by using a RID.

If the table has indexes or LOB or XML columns, ensure that after you run the DELETE DATAONLY statement, the data is consistent with the other associated objects.

LOCATE block: DUMP statement

The DUMP statement produces a hexadecimal dump of data that is identified by offset and length. DUMP statements have no effect on VERIFY or REPLACE operations.

When you specify LOCATE ROWID for a LOB table space, one or more map or data pages of the LOB are dumped. The DUMP statement dumps all of the LOB column pages if you do not specify either the MAP or DATA keyword.

LOCATE block: DUMP statement option descriptions

OFFSET
Optionally, locates the data that is to be dumped by a relative byte address (RBA) within the row or page.
integer
Specifies the offset as an integer.

The default value is 0, the first byte of the row or page.

X'byte-string'
Specifies the offset as one to four hexadecimal characters. You do not need to enter leading zeros. Enclose the byte string between apostrophes, and precede it with X.
LENGTH
Optionally, specifies the number of bytes of data that are to be dumped. If you omit both LENGTH and PAGE, the dump begins at the specified OFFSET and continues to the end of the row or page.
If you specify a number of bytes (with LENGTH) and a number of pages (with PAGE), the dump contains the same relative bytes from each page. That is, from each page you see the same number of bytes, beginning at the same offset.
X'byte-string'
Specifies one to four hexadecimal characters. You do not need to enter leading zeros. Enclose the byte string between apostrophes, and precede it with X.
integer
Specifies the length as an integer.
PAGES
Optionally, specifies a number of pages that are to be dumped. You can use this option only if you used PAGE in the preceding LOCATE TABLESPACE control statement.
X'byte-string'
Specifies one to four hexadecimal characters. You do not need to enter leading zeros. Enclose the byte string between apostrophes, and precede it with X.
integer
Specifies the number of pages as an integer.
*
Specifies that all pages from the starting point to the end of the table space or partition are to be dumped.
MAP pages
Specifies that only the LOB map pages are to be dumped.

pages specifies the number of LOB map pages that are to be dumped. If you do not specify pages, all LOB map pages of the LOB that is specified by ROWID and version are dumped.

DATA pages
Specifies that only the LOB data pages are to be dumped.

pages specifies the number of LOB data pages that are to be dumped. If you do not specify pages, all LOB data pages of the LOB that is specified by ROWID and version are dumped.

SET statement

The SET TABLESPACE statement resets the COPY-pending, RECOVER-pending, CHECK-pending, auxiliary warning (AUXW), auxiliary CHECK-pending (ACHKP), and advisory REORG-pending (AREO* and AREOR) statuses for a table space or data set. The SET TABLESPACE statement also turns on and off Persistent Read Only (PRO) restricted status for a table space partition. The SET INDEX statement resets the informational COPY-pending (ICOPY), RECOVER-pending, REBUILD-pending, CHECK-pending , and advisory REORG-pending (AREO* and AREOR) statuses for an index.

If you do not specify a status to reset, REPAIR takes no action.

SET statement option descriptions

SET TABLESPACE database-name.table-space-name
Specifies the table space (and, optionally, the database to which it belongs) whose pending status is to be reset.
database-name
The name of the database to which the table space belongs.

The default value is DSNDB04.

table-space-name
The name of the table space.
SET INDEX
Specifies the index whose RECOVER-pending, CHECK-pending, REBUILD-pending, or informational COPY-pending status is to be reset.
(index-name)
The index that is to be processed. Enclose the index name in quotation marks if the name contains a blank.
(ALL)
Specifies that all indexes in the table space will be processed.

You can also repair all indexes by specifying INDEX(ALL) followed by a table-space-spec.

SET INDEXSPACE
Specifies the index space for the index whose RECOVER-pending, CHECK-pending, REBUILD-pending, or informational COPY-pending status is to be reset.
(database-name.index-space-name)
The index space that is to be processed.
(ALL)
Specifies that all indexes in the table space will be processed.
PART integer
Specifies a particular partition whose COPY-pending, or RECOVER-pending status is to be reset. If you do not specify PART, REPAIR resets the pending status of the entire table space or index.

integer is the number of the partition and must be in the range from one to the number of partitions that are defined for the object.

You can specify PART for NOCHECKPEND on a table space, and for NORCVRPEND on indexes.

The PART keyword is not valid for a LOB table space or an index on the auxiliary table.

The PART keyword is not valid when NOAREORPEND is specified because the AREOR state can only be reset for the entire table space or index space.

NOCOPYPEND
Specifies that the COPY-pending status of the specified table space, or the informational COPY-pending (ICOPY) status of the specified index is to be reset.
NORCVRPEND
Specifies that the RECOVER-pending (RECP) status of the specified table space or index is to be reset.
NORBDPEND
Specifies that the REBUILD-pending (RBDP) status, the page set REBUILD-pending status (PSRBDP), or the RBDP* status of the specified index is to be reset.
NOCHECKPEND
Specifies that the CHECK-pending (CHKP) status of the specified table space or index is to be reset.
NOAUXWARN
Specifies that the auxiliary warning (AUXW) status of the specified table space is to be reset. The specified table space must be a base table space or a LOB table space.
NOAUXCHKP
Specifies that the auxiliary CHECK-pending (ACHKP) status of the specified table space is to be reset. The specified table space must be a base table space.
NOAREORPENDSTAR
Resets the advisory REORG-pending (AREO*) status of the specified table space or index.
NOAREORPEND
Resets the advisory REORG-pending (AREOR) status of the specified table space or index.
PRO
Turns on Persistent Read Only (PRO) restricted status for a table space partition. The PART keyword is required for this option.
NOPRO
Turns off Persistent Read Only (PRO) restricted status for a table space partition. The PART keyword is required for this option.
RBDPEND
Specifies that the REBUILD-pending (RBDP) status is to be set on the specified index.
PSRBDPEND
Specifies that the PAGE SET REBUILD-pending (PSRBDP) status is to be set on the specified index.

DBD statement

Use the DBD statement to perform one or more of the following actions:

  • Compare the database definition (DBD) in the Db2 catalog with its definition in the Db2 directory
  • Rebuild a database definition in the directory by using the information, including LOB information, in the Db2 catalog
  • Drop an inconsistent database definition from the Db2 catalog and the Db2 directory
For more information about how to use the DBD statement to perform these actions, see Repairing DBDs.

DBD statement option descriptions

DROP
Specifies that the named database is to be dropped from both the Db2 catalog and the Db2 directory. When you specify this option, Db2 also drops databases that contain tables that have been created with RESTRICT ON DROP. Use this keyword if the SQL DROP DATABASE statement fails because the description of the database is not in both the Db2 catalog and the Db2 directory. If you cannot use the ALTER command to remove the with RESTRICT ON DROP option on tables in a database that is badly damaged and you need to drop the database, you can use this keyword to drop the database.
Attention: Use the DROP option with extreme care. Using DROP can cause additional damage to your data. For more assistance, you can contact IBM® Software Support.
DATABASE database-name
Specifies the target database.

database-name is the name of the target database, which cannot be DSNDB01 (the Db2 directory) or DSNDB06 (the Db2 catalog).

If you use REBUILD, database-name cannot be DSNDB07 (the work file database).

If you use DROP, database-name cannot be DSNDB04 (the default database).

DBID X'dbid'
Specifies the database descriptor identifier for the target database.

dbid is the database descriptor identifier.

TEST
Specifies that a DBD is to be built from information in the Db2 catalog, and is to be compared with the DBD in the Db2 directory. If you specify TEST, Db2 reports significant differences between the two DBDs.

If the condition code is 0, the DBD in the Db2 directory is consistent with the information in the Db2 catalog.

If the condition code is not 0, then the information in the Db2 catalog and the DBD in the Db2 directory might be inconsistent. Run REPAIR DBD with the DIAGNOSE option to gather information that is necessary for resolving any possible inconsistency.

DIAGNOSE
Specifies that information that is necessary for resolving an inconsistent database definition is to be generated. Like the TEST option, DIAGNOSE builds a DBD that is based on the information in the Db2 catalog and compares it with the DBD in the Db2 directory. In addition, Db2 reports any differences between the two DBDs, and produces hexadecimal dumps of the inconsistent DBDs.

If the condition code is 0, the information in the Db2 catalog and the DBD in the Db2 directory is consistent.

If the condition code is 8, the information in the Db2 catalog and the DBD in the Db2 directory might be inconsistent.

For further assistance in resolving any inconsistencies, you can contact IBM Support.

REBUILD
Specifies that the DBD that is associated with the specified database is to be rebuilt from the information in the Db2 catalog.
Attention: Use the REBUILD option with extreme care, as you can cause more damage to your data. For more assistance, you can contact IBM Software Support.
OUTDDN ddname
Specifies the DD statement for an optional output data set. This data set contains copies of the Db2 catalog records that are used to rebuild the DBD.

ddname is the name of the DD statement.

LEVELID statement

Use the LEVELID statement to accept a down-level page set by changing its level ID. You cannot run REPAIR with any other REPAIR utility control statement.

Important: Accepting the use of a down-level data set might cause data inconsistencies. Problems with inconsistent data that result from resetting the level identifier are the responsibility of the user.

LEVELID statement option descriptions

LEVELID
Indicates that the level identifier of the named table space, table space partition, index, or index space partition is to be reset to a new identifier. Use LEVELID to accept the use of a down-level data set. You cannot specify multiple LEVELID keywords in the same REPAIR control statement.

You cannot use LEVELID with a table space, table space partition, index, or index space partition that has outstanding indoubt log records or pages in the logical page list (LPL).

TABLESPACE database-name.table-space-name
Specifies the table space whose level identifier is to be reset.
database-name
The name of the database to which the table space belongs.

The default value is DSNDB04.

table-space-name
The name of the table space.
INDEX
Specifies the index whose level identifier is to be reset.
creator-id
The creator of the index. Specifying this qualifier is optional.
index-name
The name of the index. Enclose the index name in quotation marks if the name contains a blank.
You can specify either INDEX or INDEXSPACE to identify an index. To specify multiple indexes, repeat the keyword.
INDEXSPACE
Specifies the index space for the index whose level identifier is to be reset. You can obtain the index space name for an index from the SYSIBM.SYSINDEXES catalog table. The index space name must be qualified.
database-name
The name of the database to which the index space belongs.
index-space-name
The name of the index space.
PART
Identifies a partition of the table space or index (including a partition of a data-partitioned secondary index).

integer is the physical partition number. It must be in the range from 1 to the number of partitions that are defined for the table space.

CATALOG statement

Use the CATALOG statement to check and correct inconsistencies between the catalog and a table space or index after DSN1COPY is used to create a copy of the object a different Db2 subsystem from the subsystem on which the original table space resides.

Start of changeWhen you specify REPAIR CATALOG TABLESPACE, the utility performs the following actions:End of change

  • Compares the following information in the catalog with the data and changes the values in the catalog to match the data if needed:
    • Row format (Row format can be either reordered row format or basic row format.)
    • RBA format (RBA format can be either 6-byte format or 10-byte format.)
    • Data version information
    • Hash space value
    • Start of changeFor a partition-by-range table space, the type of page numbering (absolute or relative)End of change
    • Start of changeFL 509 Compression algorithm usedEnd of change
  • Start of changeCompares the following information in the catalog with the data, and if the information in the catalog is different from the data, REPAIR determines whether conversion from the page set format to the catalog format is supported. Supported conversions are any conversions that can be performed through ALTER TABLE SET DATA TYPE. If the conversion is supported, REPAIR increments the table space version in the catalog. This action causes the column data in the page set to be converted to the data type and length in the catalog definition the next time that the data is accessed. If conversion is not supported, an error is issued.Start of change
    • Column data types
    • Column lengths
    • Number of columns

      For this case, the number of the columns in the catalog definition of the table must be greater than the number of columns in the page set. All columns in the page set must have types and lengths that can be converted to the types and lengths of columns in the catalog.

    End of change End of change
  • Validates the following information:

    For these items, if the information in the catalog is different from the data, REPAIR CATALOG does not correct the information in the catalog. Instead, REPAIR fails and reports the mismatched information in a message. To correct the mismatched information, take the action that is documented for the message that you receive.

    • DBID, PSID, and OBID
    • Table space type
    • SEGSIZE
    • PAGESIZE
    • Table definition
    • Start of changeWhether the system page exists in the page setEnd of change
    • Start of changeWhether a table version number wrapped from 255 to 1End of change
    • Start of changeWhether a table is marked as dropped in the database descriptor, but is still defined in the catalogEnd of change
    • Start of changeThe number of columns in the catalog definition of a table is fewer than the number of the columns in the page setEnd of change
  • Start of changeUpdates the CURRENT_VERSION or OLDEST_VERSION column of the SYSIBM.SYSTABLESPACE table when the data in the table space is at different versions from the versions that are recorded in the catalog.End of change

REPAIR CATALOG does not check limit key values.

REPAIR CATALOG does not make any corrections for indexes. If REPAIR or you made corrections to the data or catalog as a result of running REPAIR CATALOG, rebuild any indexes on the target tables.

CATALOG statement option descriptions

CATALOG
Specifies that REPAIR is to validate information in the catalog for the specified object.

You cannot specify CATALOG for LOB or XML table spaces.

TABLESPACE database-name.table-space-name
Specifies the table space for which catalog information is to be validated.
database-name
The name of the database to which the table space belongs.

The default value is DSNDB04.

table-space-name
The name of the table space.
INDEX
Specifies the index for which catalog information is to be validated.
creator-id
The creator of the index. Specifying this qualifier is optional.
index-name
The name of the index. Enclose the index name in quotation marks if the name contains a blank.
You can specify either INDEX or INDEXSPACE to identify an index. To specify multiple indexes, repeat the keyword.
INDEXSPACE
Specifies the index space for the index whose catalog information is to be validated. You can obtain the index space name for an index from the SYSIBM.SYSINDEXES catalog table. The index space name must be qualified.
database-name
The name of the database to which the index space belongs.
index-space-name
The name of the index space.
TEST
Indicates that REPAIR is not to correct any mismatched information. The utility checks all of the same information that it checks when you specify REPAIR CATALOG. However, any information differences between the data and catalog are only reported in messages. The utility does not take any corrective actions.

System pages statement

Use the system pages statement to insert missing system pages into the specified table space for tables that are in version 0 format. Insertion of system pages in version 0 format ensures that a table space or table is self-describing. That is, Db2 requires no catalog or directory information to determine the format of the table space or tables in the table space. Specification of this option is important for read-only objects that might be moved or copied to other Db2 subsystems.

Before running the system pages statement, run REPAIR CATALOG TEST to determine whether system pages need to be inserted. If REPAIR CATALOG TEST returns the message MISSING SYSTEM PAGE IN PAGESET, the system pages statement needs to be run.

The system pages statement cannot run on a table space that was the target of DSN1COPY, because REPAIR cannot determine whether the format of the data matches its format in the Db2 catalog.

System pages statement option descriptions

Start of changeINSERTVERSIONPAGESEnd of change
Start of changeIndicates that REPAIR inserts missing system pages into the table space for tables that are in version 0 format.
SETCURRENTVERSION
Indicates that REPAIR takes the following actions:
  • Inserts missing system pages for table spaces or tables in the table space that are in version 0 format.
  • Updates the version numbers in the VERSION column of the SYSIBM.SYSTABLES catalog table for all tables in the table space that are at version 0 to the same version number as in the CURRENT_VERSION column in SYSIBM.SYSTABLESPACE.

After running REPAIR with INSERTVERSIONPAGES and SETCURRENTVERSION, run REORG TABLESPACE to update the data rows to the current table space version. Then run MODIFY RECOVERY to remove old versions.

End of change
TABLESPACE database-name.table-space-name
Specifies the table space whose version information is to be updated.
database-name
The database to which the table space belongs.

The default value is DSNDB04.

table-space-name
The name of the table space.
SHRLEVEL CHANGE
Indicates the type of access that is to be allowed for the table space into which system pages are inserted.

If you do not specify SHRLEVEL CHANGE, applications can read but not write in the table space.

If you specify SHRLEVEL CHANGE, you cannot specify SETCURRENTVERSION.

Start of change

WRITELOG statement

Use the WRITELOG statement to write a Db2 log record.

End of change
Start of change

WRITELOG statement option descriptions

TABLESPACE database-name.table-space-name
Specifies the table space whose log record is to be written.
database-name
The database to which the table space belongs. The default value is DSNDB04.
table-space-name
The name of the table space.
TYPE X'4400'
Specifies that a diagnostic log record is to be written.
SUBTYPE X'0083'
Specifies that the add or alter column diagnostic log record (SCHEMA ALTER DIAGNOSTIC LOG RECORD as documented in DSNDQJ00) is to be written.
Related information:
TEXT X'byte-string'
Specifies the content of the log record to be written.
End of change