Ability to Use db2dart

The db2dart is used to verify that the architectural integrity of a database is correct.

For example, this tool confirms that:

• The control information is correct.

• There are no discrepancies in the format of the data.

• The data pages are the correct size and contain the correct column types.

• Indexes are valid.

You must run this tool on the DB2 server where the database resides. You must also ensure that there are no active connections to the database. (Use the LIST APPLICATIONS FOR DATABASE database-alias command and disconnect any applications that are listed.)

In a DB2 UDB Enterprise Server Edition (ESE) with multiple database partitions, db2dart must be run on each database partition. For information on db2dart options, type db2dart without any options.

db2dart

Help screen from db2dart:

[View full width]
____________________________________________________________________________
                        DB2 V810   DB2DART HELP
Syntax:
                      DB2DART <DB Alias> <option>  ...

Help:
/H                    Help. This help text.

Inspect Action:
/DB                   (default) Inspects entire database.
/T                    Inspects a single table.  (See notes 1, 3)
/TSF                  Inspects only the tablespace files and containers.
/TSC                  Inspects a tablespace's constructs (but not its tables).
/TS                   Inspects a single tablespace and its tables.
                      (/TSC and /TS require a tablespace id.  See notes 1, 2)
/ATSC                 Inspect constructs of all tablespaces (but not their tables).

Data Format Action:
/DD                   Dumps formatted table data.  (See notes 1, 4)
/DM                   Dumps formatted block map data (See notes 1, 4)
/DI                   Dumps formatted index data.  (See notes 1, 4)
/DP                   Dumps pages in hex format.   (See notes 1, 7)
/DTSF                 Dumps formatted tablespace file information.
/DEMP                 Dumps formatted EMP information for a DMS table.
                      (See notes 1, 3)
/DDEL                 Dumps formatted table data in delimited ASCII format.
/DHWM                 Dumps highwater mark information.  (See notes 1, 2)
/LHWM                 Suggests ways of lowering highwater mark. (See notes 1, 8)

(press <enter> for more text)

Repair Action: (MAKE SURE DATABASE IS OFFLINE)
/MI                   Mark index as invalid (make sure db is offline)
                      (See notes 1, 5)
/MT                   Mark table with drop-pending (unavailable) state.
                      (See notes 1, 6, 9.  Make sure db is offline.)
/IP                   Initialize data page of a table as empty.
                      (See note 9.  Make sure db is offline.)
/UBPF                 Updates the bufferpool file with a new specification.

Change State Action: (MAKE SURE DATABASE IS OFFLINE)
/CHST                 Change a state of the database.

(press <enter> for more text)

Input values options:
/OI object-id         Object ID
/TN table-name        Table name
/TSI tablespace-id    Tablespace ID
/ROW sum              (2) Check LF/LOB descriptors
                      (1) Check control info of varying types in rows
                      (see note 10)
/PS number            Page number to start with
                      (suffix page number with 'p' for pool relative)
/NP number            Number of pages
/V Y/N                Y or N for verbose option
/PW password          Contact DB2 service for valid password
/RPT path             Optional path to place report output file
/RPTN file-name       Optional name for report file
/CONN Y/N             Specify whether DB2DART processing includes operations
                      that connect to database (Y) or not (N).  Default (Y)
/SCR Y/M/N            (Y) normal output produced to screen
                      (M) minimize output to screen
                      (N) no output to screen
/RPTF Y/E/N           (Y) normal output produced to report file
                      (E) only error information to report file
                      (N) no report file output
/ERR Y/N/E            (Y) normal error log DART.ERR file
                      (N) minimize output to error log DART.ERR file
                      (E) minimize DART.ERR file output, minimize screen
                      output, only error information to report file
                      Default (Y)
/WHAT DBBP OFF/ON     Database backup pending state
                      (OFF) off state
                      (ON) on state
(press <enter> for more text)

Notes:
1. For actions that require additional input values for identifying the data to act on,
 the input values can be specified as arguments along with the action, or if not specified
 then you will be prompted for input values. This does not apply for actions /DDEL and /IP,
 the input values required will be prompted for.
2. Actions /TSC, /TS, /ETS, and /DHWM require 1 input value?the tablespace ID.
3. Actions /T and /DEMP require two input values consisting of tablespace ID, and either
 of table object ID or table name.
4. Actions /DD and /DI require five input values consisting of either table object ID or
 table name, tablespace ID, page number to start with, number of pages, and verbose choice.
5. Action /MI requires two input values consisting of tablespace ID and index object ID.
6. Action /MT requires three input values consisting of either table object ID or table
 name, tablespace ID, and password.
7. Action /DP requires three input values consisting of tablespace ID (DMS tablespace
 only), page number to start with, and number of pages.
8. Action /LHWM requires a tablespace ID and the number of pages for the desired highwater
 mark after lowering it.
9. For password required by some actions, please contact DB2 service.
10. For value options where unique values identify different choices for the option, sum
 up the values to get the combination of choices.
11. Default location for report output file is the current directory in a non-MPP
 environment, and in the db2dump directory in a MPP environment.
12. The scope of db2dart is single node.
13. In a MPP environment, you can use dart_all to invoke db2dart at all DB2 logical nodes
 in a single invocation. As well, db2_all can be used.
______________________________________________________________________
_____    D A R T    P R O C E S S I N G    C O M P L E T E    _______

You can use the db2dart tool to obtain information about a database's tables and table space. While many of the dump or inspection options can be run while a connection to the database exists, it is good practice to ensure that you have terminated all connections to the database prior to running db2dart.

The syntax of the db2dart command is:

db2dart database dart_command [command parameters] [global options ]

If you select a command that requires one or more parameters but do not specify them on the command line, db2dart will prompt you for the appropriate values.

Some of the more common command actions you will want to run:

/DEMP

This command dumps all the EMP pages for all data types (DAT, INX, LF, LOB, LOBA) for a given table. This command requires that you specify the table space ID (parameter /TSI) and either the table name (parameter /TN) or the table object ID (parameter /OI). Note that, if a table spans multiple table spaces (for example, longs or indices are in a different table space), you have to specify the "main" table space, not the "secondary" ones.

/DTSF

This command dumps formatted information on all the table spaces in the database. It doesn't require any options.

/DP

This command dumps a range of pages from a table space. It requires three parameters:

• the table space ID

• the starting page to dump

• the number of pages to dump

These are the command parameters that you would be likely to use:

/OI <object ID>

Object ID of the table.

/TN <table name>

Name of the table. This should match what's listed in SYSTABLES.

/TSI <tablespace ID>

The tablespace ID.

/PS <page number>

The page number at which to start dumping. For DMS table spaces, you can add the suffix p to signify a pool-relative page number.

/NP <number of pages>

The number of pages to dump.

By default, db2dart outputs to a file called X.RPT in the current directory:

On Windows NT/2000:
driver:\..\SQLLIB\<instance_name>\DARTnnnn\
On UNIX:
$DB2INSTANCE_HOME/sqllib/db2dump/DARTnnnn/
where
nnnn is the partition number, such as 0000, 0001, etc.
X is the name of the database being inspected.

You can change the name of this file by using the /RPTN option and adding /RPTN <filename> to the command line.

For example, to dump the information for the table TEST_1 in table space 9 in the TESTDB database and save the result in a file called MYDB.RPT:

db2dart TESTDB /DEMP /TN TEST_1 /TSI 9 /RPTN MYDB.RPT

Assuming that the table TEST_1 has an object ID of 6, the following command does the same thing:

db2dart TESTDB /DEMP /OI 6 /TSI 9 /RPTN MYDB.RPT

To dump 100 pages from table space ID 2 in the database TESTDB, starting from pool-relative page number 1000, with output going to MYDB.RPT:

db2dart TESTDB /DP /TSI 2 /PS 1000p /NP 100 /RPTN MYDB.RPT

To verify that a container was created as a striped container, you can use the /DTSF option of DB2DART to dump table space and container information, then look at the type field for the container in question.

If the container type is file, the DMS table space was created without striping or DB2_STRIPED_CONTAINERS is not set. If the container type is striped file, the DMS table space was created with striping or the DB2_STRIPED_CONTAINERS is already set to ON.

As an example, a table space ts1 is created without striping:

  db2 connect to sample
  db2 "create tablespace ts1 managed by database using (file '/data/v8inst/dms/ts1.dat' 1000)"
  db2 terminate

  db2dart sample /DTSF
  pg SAMPLE.RPT
  ...
Container list:
#            Total Pages    UseablePages     Container Type    Container Name
==================================================================================
0            1000           992              file              /data/v8inst/dsm/ts1.dat
Container checksum for disk space: 641033847

Now, table space ts2 is created with striping:

  db2set DB2_STRIPED_CONTAINERS=ON
  db2stop
  db2start
  db2 connect to sample
  db2 "create tablespace ts2 managed by database using (file '/data/v8inst/dms/ts2.dat' 1000)"
  db2 terminate

  db2dart sample /DTSF
  pg SAMPLE.RPT
  ...
Container list:
#            Total Pages    Useable Pages    Container Type    Container Name
=================================================================================
0            1000           960              striped file      /data/v8inst/dsm/ts2.dat
Container checksum for disk space: 641168976

Let's take a look at a potential problem with corrupt indexes in DB2. In order to avoid this problem, you should go through the following steps:

Scenario #1: With rebuild all indexes Option

If you have run the db2uiddl optional migration step to rebuild all indexes immediately after migration, you should run:

db2dart <dbname> /ts /tsi 0

In the <DBNAME>.RPT file, there will be errors reported if there are any indexes that need to be rebuilt. There will also be the commands reported to the <DBNAME>.RPT file to mark the indexes as bad:

db2dart <dbname> /MI .....

The exact steps to follow are:

Scenario #2: Without rebuild all indexes Option

If you have not run db2uiddl immediately after migration:

db2dart <dbname> /db

Any indexes that db2dart identifies errors in should be marked as bad and rebuilt.

As above, there will be errors reported to the <DBNAME>.RPT file, as well as the commands to rebuild the offending indexes.

The exact steps to follow are: