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 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 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 storage group and 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
- /DC
- Dumps formatted column-organized table
data. Requires five input values: a table object ID or table name,
table space ID, starting page number, number of pages, and verbose
choice.
- /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 following a reorganization to reclaim multidimensional clustering
(MDC) or insert time clustering (ITC) 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 and storage
group 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: a table object ID or table name, table space ID,
page number to start with, and number of pages.
For column-organized tables,
the /DDEL parameter accepts a range of logical
row numbers instead of a range of pages. The logical row number uniquely
identifies a row in a column-organized table
and is analogous to a RID in a row-organized table.
If both the first logical row number and the number of logical rows
are 0, all rows in the column-organized table
are dumped. If only the number of logical rows is 0, all rows from
first logical row number to the last row of the table are dumped.
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 (required high water mark).
- 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.
Note: You
must use the ALTER TABLESPACE REDUCE command to
remove empty SMP extents. You can also use the ALTER TABLESPACE
LOWER HIGH WATER MARK command to remove empty SMP extents
and lower high water mark if the table space is enabled with reclaimable
storage. You can use the ALTER TABLESPACE command
while the database is online.
- 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 will cause all pages in the specified object to be
dumped.
- /NP number
- Specifies the number of pages. Specifying /PS 0 /NP 0 will
cause all pages in the specified object to be dumped.
- /V option
- Specifies whether or not 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.
- Keystore password options
- The following options are valid for an encrypted database if the
keystore password is not stashed.
- /KPW password
- Specifies the password to use when opening the keystore.
- /KPWA fd:file_descriptor | filename:file_name
- Specifies the keystore password arguments. The file_descriptor parameter
specifies a file descriptor that identifies an open and readable file
or pipe that contains the password to use. The file_name parameter
specifies the fully qualified name of the file that contains the password
to use.
- /KPWP
- Specifies that the user is to be prompted for a password.
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 dump formatted table space and
storage group file information for database
testdb,
use the following command:
db2dart testdb /DTSF
The following is an example output generated
by the previous command:
Storage group file (automatic storage) report phase start.
Header version: 33
Header flavour: 1
Checksum: 0x5402d18f
Number of storage groups: 1
Default storage group ID: 0
Header Last LSN: 0x000000000003D4A4
Storage group ID: 0
Storage group name: IBMSTOGROUP
Data Tag: 5
Flavour: 3
Version: 5
State flags: 0x0000000000000000
Last LSN: 0x000000000003D4A4
Initial LSN: 0x0000000000000000
Checksum: 0x1e587275
Number of storage paths: 1
Storage path # 0: /filesystem1 (id = 0, state = 0x0)
Storage group file (automatic storage) report phase end.
Tablespace file report phase start.
Tablespace information for current database:
--------------------------------------------
Number of defined tablespaces: 4
High water mark of used pools: 3
Number of disabled tablespaces: 0
Individual tablespace details:
-------------------------------
Information for Tablespace ID: 0
-------------------------------------
Tablespace name: SYSCATSPACE
Table space flags (HEX): 3102
Table space type: Database Managed Space (DMS), Automatic Storage, Auto-Resize
Page size: 4096
Extent size: 4
Prefetch size: 4
Version: 104
Tablespace state: 0
Number of quiescers: 0
Storage Group ID: 0
Source Storage Group ID: -1
Data Tag: 0
Usable pages in tablespace: 25340
Total pages in tablespace: 25344
Initial Size: 1048576 bytes
Increment : -4096 bytes
Maximum Size: None
Last Resize: None
Last Resize Failed: No
SMP page for first free extent: 4
SMP page for last allocated tablespace extent. 4
SMP extent number of the last initialized SMP extent: 0
High Water Mark: 25164
.
.
.
- Example 4
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 5
- 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 or logical row, num of pages
or logical rows:(may suffix page number with 'p' for pool relative if working
with a pool-relative tablespace)
39,2,0,0
4 of 4 columns in the table will be dumped.
Column numbers and datatypes of the columns dumped:
0 INTEGER
1 INTEGER
2 BIGINT
3 BIGINT
Default filename for output data file is BLUDB_TS2T39.DEL,
do you wish to change filename used? y/n
n
Filename used for output data file is BLUDB_TS2T39.DEL. If the file exists,
the data will be overwritten.
Dumping delimited ASCII data of COL object ...
Table object data formatting end.
.
.
Usage
notes
- If you do not specify all the required input values when you invoke
the db2dart command, you will be 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
will dump 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 partitionobjectid and tbspaceid from syscat.datapartitions 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 partitionobjectid and tbspaceid
from syscat.datapartitions 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).
- db2dart does not process log records
or read bufferpool pages. So false error messages can be generated
if the report is run when DB2 is either in a recovery pending state
or in an active state. When the "database is not consistent" message
is raised, apply the required transaction log record to make the database
consistent. Then, evaluate the error messages carefully before you
decide to restore the database. If the database is online, either
stop the database or use the INSPECT command.