db2dart tool

    db2dart is a command which can be used to verify the architectural correctness of

databases and the objects within them. It can also be used to display the contents
of database control files in order to extract data from tables that might otherwise
be inaccessible.

    To display all of the possible options, simply execute the db2dart utility without
any parameters. Some options that require parameters, such as the table space ID,
are prompted for if they are not explicitly specified on the command line.

    By default, db2dart will create a report file with the name databaseName.RPT. For
single-partition database environments, the file is created in the current directory.
For multiple-partition database environments, the file is created under a
subdirectory in the diagnostic directory. The subdirectory is called DART####, where
#### is the partition number.

    db2dart accesses the data and metadata in a database by reading them directly
from disk. Because of that, db2dart should never be run against a database that
still has active connections. If there are connections, db2dart will not know about
pages in the buffer pool, control structures in memory, etc. and may report false
errors as a result. Similarly, if you run db2dart against a database that requires
crash recovery or that has not completed roll-forward recovery, similar
inconsistencies might result due to the inconsistent nature of the data on disk.

Inspecting databases, table spaces, and tables via db2dart:

    The default behavior for db2dart is to inspect the entire database. Only the
database name must be provided in this case. For example:

C:>db2dart sample
The requested DB2DART processing has completed successfully!
Complete DB2DART report found in:
C:IBMSQLLIBDB2DART0000SAMPLE.RPT

    As the output states, the full db2dart report can be found in the file SAMPLE.RPT.
You will also notice that in this case db2dart did not find any problems with the
database.

    If a database is very large and you are only interested in one table space, you can
use the /TS option. When using this option, you must either provide the table
space ID on the command line (by specifying the /TSI parameter) or you can let
db2dart prompt you for it. If you do not know the table space ID, you can obtain
it via the command DB2 LIST TABLESPACES command. For example, to inspect the
USERSPACE1 table space (which has a table space ID of 2 in the sample database),
either of these commands will work:

db2dart sample /ts /tsi 2

or

db2dart sample /ts <= When prompted for the table space ID, enter "2".

    Similarly, a single table and its associated objects (LOBs, indexes, etc.) can be
inspected using the /T option. When using this option, you must provide either the
table name or object ID and the ID of the table space in which the table resides. To
determine the object ID and table space ID for a table, you can query the FID and
TID columns of the SYSIBM.SYSTABLES catalog table. For example, determine the
object ID and table space ID for the EMP_PHOTO table in the sample database by
executing the following query:

C:>db2 connect to sample
Database server = DB2/NT 8.2.0
SQL authorization ID = LISAC
Local database alias = SAMPLE
C:>db2 "select creator,name,tid,fid from sysibm.systables where name =
’EMP_PHOTO’"

CREATOR NAME TID FID
------------------ --------------------- ------ ------
DB2 EMP_PHOTO 2 8
1 record(s) selected.

C:>db2 connect reset
DB20000I The SQL command completed successfully.

To inspect this table, execute either of the following db2dart commands:

db2dart sample /t /tsi 2 /oi 8
db2dart sample /t <= When prompted for the table ID and table space ID,
enter "8 2".

As mentioned above, the table name can be specified instead of the object ID:

db2dart sample /t /tsi 2 /tn EMP_PHOTO
db2dart sample /t <= When prompted for the table name and table space ID,
enter "EMP_PHOTO 2".

Dumping formatted table data via db2dart:

If a table space or table becomes corrupt for any reason (for example due to a bad
disk or disk controller), attempts to access the table through SQL may not work.
(The SQL statement may fail with an error or the database may be marked bad
and all connections will be dropped.) In such a case, entries will likely be written
to the db2diag.log file, indicating that a bad page was encountered.

2004-10-12-16.49.20.119228+120 I3292G436 LEVEL: Error
PID : 14974 TID : 605992128 PROC : db2bm.14206.5
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-777 APPID: *LOCAL.db2inst1.000
FUNCTION: DB2 UDB, buffer pool services, sqlbrdpg, probe:1143
RETCODE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad"
DIA8400C A bad page was encountered.

    If you see such entries, you should run db2dart against the database (or table
space) to determine the extent of the damage.

    If this happens, it may be necessary to extract all of the data possible so that the
table space and table can be rebuilt. In such a situation, the /DDEL option of
db2dart can be used to extract the table data and place it into a delimited ASCII
file. Note that due to the nature of ASCII files, some columns (such as LOB
columns) cannot be extracted from the table. db2dart will tell you if this is the case.

    When using the /DDEL option, you must provide a table space ID, object ID,
starting page number, and number of pages. To extract all of the pages, use 0 for
the starting page number and some very large number for the number of pages.
(Specifying more pages than actually exist will not cause any problems.)

The ORG table in the sample database resides in table space 2 and has an object ID
of 2. To extract all of the data from this table, execute this command:

db2dart sample /ddel

When prompted, enter either of the following lines of input:

2 2 0 1000
ORG 2 0 1000

You will then be presented with the column definitions for the table and will be
asked to specify an output file name:

Table object data formatting start.
Please enter

Table ID or name, tablespace ID, first page, num of pages:
(suffic page number with ’p’ for pool relative)
2 2 0 1000
5 of 5 columns in the table will be dumped.
Column numbers and datatypes of the columns dumped:
0 SMALLINT
1 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
2 SMALLINT
3 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
4 VARCHAR() -VARIABLE LENGTH CHARACTER STRING

Default filename for output data file is TS2T2.DEL,

do you wish to change filename used? y/n

You can choose the default or specify a new one.
The output file will be created in the current directory be default.

When the extraction is complete, you will see output as follows:

Filename used for output data file is TS2T2.DEL.
If existing file, data will be appended to it.
Formatted data being dumped ...
Dumping Page 0 ....
Table object data formatting end.
The requested DB2DART processing has completed successfully!

---------------------------------------------------------db2 9.5例子(仅适用于9.7之前)：
1.发现表空间 High water mark (pages) 大于Used pages：
> db2 list tablespaces show detail
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 994240
 Useable pages                        = 994208
 Used pages                           = 183968
 Free pages                           = 810240
 High water mark (pages)              = 994208
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Minimum recovery time                = 2014-07-11-14.42.04.000000

2.用DHWM来显示HWM信息：
> db2dart conform /DHWM
查看输出文件，最后部分。 Object ID: 270

3. 通过系统表查出tableid为270的表：
db2 "select tabname, tbspaceid, tableid from syscat.tables where tbspaceid=2 and tableid=270"

TABSCHEMA                                                                                                                        TABNAME                                                                                                                          TBSPACEID TABLEID
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------- -------
SYSTOOLS                                                                                                                         FED_CACHE_OPTIONS                                                                                                                        2     270
4.通过LHWM来给出降低HWM的建议和方法：
> db2dart conform /LHWM

Please enter tablespace ID, and number of pages (desired highwater mark):
2,0

FYI: An active connection to the database has been detected.
     False errors may be reported. 
     Deactivate all connections and re-run to verify.

         The requested DB2DART processing has completed successfully!
                        Complete DB2DART report found in:
/db2eiw02/sqllib/db2dump/DART0000/CONFORM.RPT

5.vi /db2eiw02/sqllib/db2dump/DART0000/CONFORM.RPT
建议做离线reorg：

Step #1:  Object ID = 270

 => Offline REORG of this table (do not specify a temporary tablespace
    and do not use the LONGLOBDATA option).

    Table: SYSTOOLS.FED_CACHE_OPTIONS

     DAT object size:   2
     INX object size:   0
     XDA object size:   0
     LF object size:    0
     LOB object size:   0
     LOBA object size:  0
     BMP object size:   0

     Total size of object parts: 2
     Minimum number of extents that will move by this operation: 2

     Current highwater mark:                     31066
     Desired highwater mark:                     0
     Number of used extents in tablespace:       5749
     Number of free extents below original HWM:  25320
     Number of free extents below desired HWM:   0
     Number of free extents below current HWM:   25318


** Run the suggested offline REORG for the table first, and then run LHWM
   for the suggestion on other objects.

6.

> db2 reorg table SYSTOOLS.FED_CACHE_OPTIONS
DB20000I  The REORG command completed successfully.

7.db2 list tablespaces show detail 发现HWM变小了