Section 13.12. Using DB2 Tools to Inspect the Health of Your Database | Understanding DB2: Learning Visually with Examples (2nd Edition)

13.12. Using DB2 Tools to Inspect the Health of Your Database

Both hardware and software problems can potentially corrupt data pages in your database. When a page is corrupted, its data becomes unusable, and users trying to access this page will receive an error. If a page in the system catalog tables is corrupted, your whole database may become unusable.

DB2 provides two tools to check for database corruptions and possibly fix them. One is db2dart, the database analysis and reporting tool, and the other tool is called the INSPECT tool.

13.12.1. The db2dart Tool

You can only use the db2dart tool when the database is offline, so no connections are allowed while the database is being inspected.

You can use db2dart to inspect the whole database, a table space in the database, or a single table. When the inspection ends, it presents the results in a nicely organized report, deposited in the directory where the db2dart command was issued (on Linux/UNIX), or the db2_install_ dir\instance_name\DART0000 directory (on Windows). The report has the name dbalias.RPT.

The syntax for the command is:

 db2dart DBALIAS [OPTIONS]

Type db2dart from the command line to see the list of all available options.

The following are some ways you can use db2dart.

To perform an inspection on all objects in the sample database, issue:
```
 db2dart sample 
```
To inspect table space USERSPACE1 in the sample database, issue:
```
 db2dart sample /TSI 2 
```
where 2 is the table space ID for table space USERSPACE1. Table space IDs can be found in the LIST TABLESPACES output.
To inspect the sales table in the sample database, issue:
```
 db2dart sample /TSI 2 /TN "sales" 
```

If db2dart reports some data pages being corrupted, restore the database using a good backup image.

If db2dart reports some index pages being corrupted, you can fix this instead of having to restore from a backup.

If db2dart reports an index is corrupted, take the following steps to fix it.

Mark the index invalid using:

 db2dart dbalias /MI /OI objectID of the index /TSI tablespaceID

where both the objectID and tablespaceID can be found in the db2dart report.

Let DB2 automatically rebuild the index. When DB2 actually rebuilds this index depends on the INDEXREC database configuration parameter setting. Its values can be ACCESS, RESTART, or SYSTEM.

- ACCESS: DB2 rebuilds invalid indexes when they are accessed again for the first time, after they have been invalidated by db2dart. With this method, the first user who accesses this index will experience a longer wait while the index is recreated.

- RESTART: DB2 rebuilds invalid indexes when the database is restarted. With this method, the time taken to restart the database will be longer due to index re-creation, but normal processing is not impacted once the database has been brought back online.

- SYSTEM: DB2 uses the setting in the INDEXREC at the database manager level.

NOTE

INDEXREC is available as a database configuration parameter and a Database Manager Configuration parameter. As a Database Manager Configuration parameter, the value of INDEXREC affects all databases that have INDEXREC set to SYSTEM. With this dual-level setting, you can choose to control the index recreation at instance level or at individual database levels.

13.12.2. The INSPECT tool

Unlike db2dart, the INSPECT tool runs while the database is online. The INSPECT tool inspects databases for architectural integrity and checks the pages of the database for page consistency. However, it cannot be used to mark an index invalid as can the db2dart tool.

The results file of the inspection is generated in the DB2 diagnostic data directory (i.e., where the db2diag.log file is). This is a binary file; you need to format it with the DB2INSPF command. If no errors are found, by default, the results file is erased after the inspect operation is complete, unless the KEEP option is used.

To inspect the SAMPLE database and write the results to a file called inspect.out, issue:
```
 CONNECT TO sample INSPECT CHECK DATABASE RESULTS inspect.out 
```
To inspect the table space with table space ID 2 and keep the results and write it to the file inspect.out, issue:
```
 CONNECT TO sample INSPECT CHECK TABLSPACE TBSPACEID 2 RESULTS KEEP inspect.out 
```
To format the results file, issue:
```
 DB2INSPF results_file output file 
```
where results_file is from the inspect command.