Learning Visually with Examples [Electronic resources]

Raul F. Chong, Clara Liu, Sylvia F. Qi, Dwaine R. Snow

نسخه متنی -صفحه : 312/ 173

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" 
```

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

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

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.