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:
If db2dart reports a problem with an index, use the tool's /MI option to mark the index as invalid. The index is rebuilt based on the value of the indexrec database and database manager configuration parameters.
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.
Help screen from db2dart:
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:
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.
This command dumps formatted information on all the table spaces in the database. It doesn't require any options.
This command dumps a range of pages from a table space. It requires three parameters:
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: