DB2 Version 9.7 for Linux, UNIX, and Windows

db2dart - Database analysis and reporting tool command

Examines databases for architectural correctness and reports any encountered errors.

Reports generated by the db2dart command are encoded in the same code page as the database that is being analyzed by the command. Similarly, parameter values specified as part of the db2dart command are interpreted with the same code page as the database that is being analyzed. The character string that is in the db2dart report file and input value is encoded in the database code page.

The db2dart command does not perform code page conversions.

When invoking the db2dart command, you can specify only one action. An action can support a varying number of options.

Authorization

You must have SYSADM authority to use the db2dart command.

Required connection

None. db2dart must be run with no users connected to the database.

Command syntax


>>-db2dart--database-name--+---------------------+-------------><
                           '-action--+---------+-'   
                                     '-options-'

Command parameters

Inspection actions

/DB: Inspects the entire database. This is the default option.
/T: Inspects one or more tables. Requires two inputs: a table space ID, and either, the table object ID or a list of table object IDs, or the table name.
/TSF: Inspects only table space files and containers.
/TSC: Inspects the table space constructs of one or more table spaces, but does not inspect tables. Requires one input: the table space ID or a list of table space IDs.
/TS: Inspects one or more table spaces and their tables. Requires one input: the table space ID or a list of table space IDs.
/ATSC: Inspects constructs of all table spaces, but not their tables.

Data formatting actions

/DD

Dumps formatted table data. If present, inline LOB data is also shown. Requires five input values: either a table object ID or table name, table space ID, page number to start with, number of pages, and verbose choice.

/DI

Dumps formatted index data. Requires five input values: either a table object ID or table name, table space ID, page number to start with, number of pages, and verbose choice.

For nonpartitioned indexes on partitioned tables, the /DI action uses INDEX_OBJECTID and TBSPACEID from SYSCAT.INDEXES as the first two inputs to the /OI and /TSI options. The table name (/TN) option is not supported for the action.
For partitioned indexes on partitioned tables, the /DI action uses PARTITIONOBJECTID and TBSPACEID from SYSCAT.DATAPARTITIONS. The table name (/TN) option is not supported for the action.

/DM

Dumps formatted block map data. Requires five input values: either a table object ID or table name, table space ID, page number to start with, number of pages, and verbose choice. The data shows whether a block has been reclaimed for use by the table space after a reorganization to reclaim multidimensional clustering (MDC) table blocks that were empty.

/DP

Dumps pages in hex format.

For permanent object in DMS table space, action /DP requires three input values consisting of table space ID, page number to start with, and number of pages.
For permanent object in SMS table space, action /DP requires five input values consisting of table space ID, object ID, page number to start with, number of pages, and object type.

/DTSF

Dumps formatted table space file information.

/DEMP

Dumps formatted extent map page (EMP) information for a DMS table. Requires two input values: table space ID and the table object ID or table name.

/DDEL

Dumps formatted table data in delimited ASCII format. Requires four input values: either a table object ID or table name, table space ID, page number to start with, and number of pages.

The dumped delimited ASCII file is encoded in the database code page. The db2dart command does not perform code page conversions.

The /DDEL parameter supports only the following column data types. If a table contains columns with any other data type, the column is skipped and not included in the delimited ASCII file.

SMALLINT
FLOAT
REAL
INTEGER
TIME
DECIMAL
CHAR()
VARCHAR()
DATE
TIMESTAMP
BIGINT

If a column of type CHAR and VARCHAR contains any binary data, or is defined with FOR BIT DATA, the /DDEL parameter generates the DEL file which contains the binary data. When you load data from the DEL file to the table using the LOAD command, ensure that you always specify the modified by delprioritychar option. When you insert data into the table from the DEL file using the IMPORT command, make sure that you always specify the modified by delprioritychar codepage=x option where x is the code page of the data in the input data set.

/DHWM

Dumps high water mark information. Requires one input value: table space ID.

/DXA

Dumps formatted XML column data in ASCII format. Requires five input values: either a table object ID or table name, table space ID, page number to start with, number of pages, and verbose choice.

/DXH

Dumps formatted XML column data in HEX format. Requires five input values: either a table object ID or table name, table space ID, page number to start with, number of pages, and verbose choice.

/LHWM

Suggests ways of lowering the high water mark. Requires two input values: table space ID and number of pages (wanted high water mark).

The /LHWM action is not supported on the system catalog table space.

Repair actions

/ETS: Extends the table limit in a 4 KB table space (DMS only), if possible. Requires one input value: table space ID.
/MI: Marks index as invalid. When specifying this parameter the database must be offline. Requires two input values: table space ID and index object ID. For partitioned indexes, these values can be obtained from INDPARTITIONOBJECTID and INDPARTITIONTBSPACEID for SYSCAT.INDEXPARTITIONS.
/RHWM: Reduces high water mark through empty SMP extents. When specifying this parameter the database must be offline. Requires one input value: table space ID.
The /RHWM action is not supported on the system catalog table space.

Change state actions

/CHST: Change the state of a database. When specifying this parameter the database must be offline. Requires one input value: database backup pending state.

Help

/H: Displays help information.

Input value options

/OI object-id

Specifies the object ID. For the /T action, a comma-separated list of up to 64 objects IDs can be specified. If the corresponding /TSI option contains more that one input ID, only the first ID is used. Duplicate IDs are skipped. Logical IDs can be specified for the /T action.

/TN table-name

Specifies the table name in upper case unless it is a delimited identifier.

/TSI tablespace-id

Specifies the table space ID. For the /TS or /TSC actions, a comma-separated list of up to 64 physical table space IDs can be specified. Duplicate IDs are skipped.

/ROW sum

Identifies whether long field descriptors, LOB descriptors, and control information should be checked. You can specify just one option or add the values to specify more than one option.

1: Checks control information in rows.
2: Checks long field and LOB descriptors.

/RPT path

Optional path for the report output file.

/RPTN file-name

Optional name for the report output file.

/PS number

Specifies the page number to start with. When used with the /DP action, the p suffix can be used for pool relative addressing. Specifying /PS 0 /NP 0 causes all pages in the specified object to be dumped.

/NP number

Specifies the number of pages. Specifying /PS 0 /NP 0 causes all pages in the specified object to be dumped.

/V option

Specifies whether the verbose option should be implemented. Valid values are:

Y: Specifies that the verbose option should be implemented.
N: Specifies that the verbose option should not be implemented.

/SCR option

Specifies type of screen output, if any. Valid values are:

Y: Normal screen output is produced.
M: Minimized screen output is produced.
N: No screen output is produced.

/RPTF option

Specifies type of report file output, if any. Valid values are:

Y: Normal report file output is produced.
E: Only error information is produced to the report file.
N: No report file output is produced.

/ERR option

Specifies type of log to produce in DART.INF, if any. Valid values are:

Y: Produces normal log in DART.INF file.
N: Minimizes output to log DART.INF file.
E: Minimizes DART.INF file and screen output. Only error information is sent to the report file.

/WHAT DBBP option

Specifies the database backup pending state. Valid values are:

OFF: Off state.
ON: On state.

/QCK sum

Specifies which quick option to perform. You can specify one option or add the values together to perform multiple quick options.

1: The /QCK 1 option applies to only the /DB, /T, and /TS actions. This option inspects page 0 of the DAT objects and partially inspects the index objects (does not inspect BMP, LOB, LF objects and does not traverse the entirety of the DAT or INX objects). This is the default option.
2: The /QCK 2 option applies to only the /DB, /T, /TS, /DD, /DI, /DM, /DEMP, /DDEL, /DXA, and /DXH actions. This option skips the system catalog table lookup on nonpartitioned database environments and on the catalog partition of partitioned database environments. This option has no effect on non-catalog partitions of partitioned database environments. The /QCK 2 option does not apply to the actions mentioned earlier if the /TN option is specified with a table name or if the /OI and /TSI options are specified with logical IDs.
4: The /QCK 4 option applies to only the /T, /TS, and /TSC actions. This option skips special system catalog table inspection or system catalog table space inspection. For the /TS, and /TSC actions, the /QCK 4 option skips the special system catalog table inspection. For the /T action, the /QCK 4 option skips inspection of the system catalog table space constructs.
8: The /QCK 8 option applies to only the /T, and /TS actions. This option skips the inspection of containers. For the /T action, the /QCK 8 option skips the inspection of all container files. For the /TS action, the /QCK 8 option inspects only container files that are associated with the specified table space.

/TYP option

Specifies the type of object. Valid values are:

DAT: Object type is DAT.
INX: Object type is INDEX.
BKM: Object type is BMP.

Examples

Example 1

To dump 1000 pages of formatted index data on a non-range partitioned table with object ID 4 in a table space with ID 2 and starting with page zero, use the following db2dart command:

 db2dart IXMLDB /DI /TSI 2 /OI 4 /V Y /PS 0 /NP 1000

Example 2

To dump formatted block map data on range partitioned tables, use the following db2dart command:

 db2dart IXMLDB /DM /TSI 2 /OI 8 /V Y /PS 0 /NP 1000

Where 8 is the value of partitionobjectid and 2 is the value of tbspaceid from SYSCAT.DATAPARTITIONS.

Example 3

To inspect table spaces with IDs 3, 4, 5, 8, 9, and 11, and to skip the system catalog table lookup and the special system catalog table inspection, use the following db2dart command. Only the containers associated with the specified table spaces are inspected.

 db2dart <dbname> /TS /TSI 3,4,5,6,9,11 /QCK 14

Where the /QCK 14 option represents the addition of quick options 2, 4, and 8. The /QCK 14 option performs all the individual operations of quick options 2, 4, and 8.

Example 4

To dump formatted table data in delimited ASCII format, use the following command:

 db2dart inspdb /ddel

The following is an example output generated by the previous command:

Connecting to Buffer Pool Services...

   Table object data formatting start.
   Please enter
Table ID or name, tablespace ID, first page, num of pages:
260,3,0,0

   11 of 11 columns in the table will be dumped.
   Column numbers and datatypes of the columns dumped:
         0  SMALLINT
         1  INTEGER
         2  DECIMAL
         3  FLOAT
         4  REAL -SINGLE PRECISION FLOATING-POINT
         5  BIGINT
         6  CHAR() -FIXED LENGTH CHARACTER STRING
         7  VARCHAR() -VARIABLE LENGTH CHARACTER STRING
         8  DATE
         9  TIME
        10  TIMESTAMP
   Default filename for output data file is TS3T260.DEL,
do you wish to change filename used? y/n
n

   Filename used for output data file is TS3T260.DEL.  If existing file, data will be appended to it.

   Formatted data being dumped ...
   Dumping Page 0 ....
   Dumping Page 1 ....
   Dumping Page 2 ....
   ...
   Dumping Page 832 ....
   Dumping Page 833 ....
   Dumping Page 834 ....
   Dumping Page 835 ....
   Dumping Page 836 ....
   Dumping Page 837 ....
   Dumping Page 838 ....
   Dumping Page 839 ....
   Table object data formatting end.
.
.

Usage notes

If you do not specify all the required input values when you invoke the db2dart command, you are prompted for the values. For the /DDEL action, the options cannot be specified from the command line, and must be entered when prompted by db2dart.
The /ROW, /RPT, /RPTN, /SCR, /RPTF, /ERR, and /WHAT DBBP options can all be invoked in addition to the action. They are not required by any of the actions.
The /DB, /T and /TS options inspect the specified objects, including associated XML storage objects. The /DB option includes all XML storage objects in the database, the /T option includes XML storage objects associated with the specified table, and the /TS option inspects all XML storage objects whose parent objects exist in the specified table space. As well, the /DEMP option dumps formatted EMP information including that for associated XML storage objects.
When db2dart is run against a single table space, all dependent objects for a parent table in that table space are checked, irrespective of the table space in which the dependent objects reside. However, extent map page (EMP) information is not captured for dependent objects that reside outside of the specified table space. EMP information is captured for dependent objects found in the specified table space even when the parent object resides in a table space other than the one specified.
For partitioned tables, the /DD, /DM, /DEMP, /DDEL, /DP, /DXA, /DXH actions use the PARTITIONOBJECTID and TBSPACEID columns from the SYSCAT.DATAPARTIONS catalog view as the input to the table object ID (/OI) and table space ID (/TSI) for a specific partition. The table name option (/TN) is not supported for these actions. The /T action supports the table name or global table object ID when use with global table space ID to check the entire table, and also supports using the PARTITIONOBJECTID and TBSPACEID columns from the SYSCAT.DATAPARTIONS catalog view as the input to /OI and /TSI to check a specific partition.
In general, run the db2dart command when the database is offline. However, you do not need an offline database if you are specifying either the /DHWM and /LHWM actions. The report could be generated without the database being offline, but the reliability of the results will vary depending on how much write/update activity has occurred recently (less activity implies more reliable results).