Learning Visually with Examples [Electronic resources] نسخه متنی

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.

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

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.