DB2DART - Hints & Tips for the DBA

Published 17 January 2012 - by Iqbal Goralwalla

The DB2 Analysis & Reporting tool or DB2DART is one of the lesser used performance tools and has traditionally been used with IBM support. However, DB2DART can be one of the most powerful tools in the DBA's kit bag. It can help increase DBA productivity in crisis situations and earn the DBA some well-deserved brownie points!

DB2DART is an "offline" tool for checking the architectural correctness of a database. Using DB2DART you are able to check the validity of meta-data structures, page headers, row headers etc. It is critical for investigating problems involving data corruption. 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.

Don't forget - db2dart accesses the data and metadata in a database by reading them directly from disk. Therefore 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. 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.

Top 3 DB2DARTs
I've been using DB2DART for many years and my "Top DARTs" which I think you will benefit from are:

1. Storage Reclamation and High Water Mark
2. Index Corruption
3. Extracting Data

DART No.1
DBA Dilemma
Our DBA is trying to free up some space and tries several things including; deleting thousands of rows, dropping a large table and re-orging the table. So why is the space still not being released?

Storage Reclamation - HWM for DMS tablespaces
The High Water Mark (HWM) refers to the highest currently allocated page in a DMS tablespace. This value may not reflect the actual amount of data. If the last page in the tablespace is in use but the rest of it is empty, the high water mark of tablespace still refers to the last page. The HWM can be much higher than the number of pages currently in use due to:
• Dropped tables or delete activity
• Offline table reorg using same tablespace
• Index reorg using "allow read or write access"

This makes it very difficult to release unused disk space to reuse.

TOP TIP- HWM can be seen using LIST TABLESPACES SHOW DETAIL or GET SNAPSHOT FOR TABLESPACES

The following scenarios show some example of how the HWM behaviour when tables are dropped or an offline reorg is performed.

Example 1: Dropped Table - HWM remains unchanged

Example 2: Dropped Table - HWM changes

Example 3: Offline Table Reorg - HWM increases

Example 4: Offline Table Reorg - HWM decreases 

High Water Mark Headaches

• Space not released even though free extents available
• Redirected RESTORE needs space equal to or greater than the HWM. This could result in carrying over unused space.
• All extents in DMS table spaces up to the HWM copied to the backup image.
• § Tablespaces containers cannot be reduced below HWM
• ALTER TABLESPACE <drop container/reduce containers size> only affects extents above the HWM

What next?  Call IBM Support?  No, use DB2DART!

Using the DB2DART Database High Water Mark display option (/DHWM) you can view:

• A map of the extents in the table space, showing objects owning them
• The ID and type of the object holding the high-water mark extent
• Amount of free extents and in-use extents below the high-water mark

TOP TIP - If online, use db2pdcfg -flushbp (V9.1FP5, V9.5FP1) before running DB2DART /DHWM. This will sync in-memory tablespace meta-data structures with on-disk versions

The DB2DART/LHWM assists in lowering the high watermark.

• Table space ID and desired high-water mark are required
• May not be possible to actually lower it to the desired level
• Use "0" for the desired high-water mark to go as low as possible
• Result is a list of operations to execute (EXPORT/LOAD, REORG, etc.)
• Estimate of resulting in use and free extents below the high-water mark is displayed for each step

DB2DART /RHWM - Removes empty SMP extents holding up the high-water mark

• DB2 9.5 - ALTER TABLESPACE <tbsp> REDUCE/DROP
  - Drops/reduces containers to the table space's high-water mark
  - First removes empty SMP extents
  - No need for db2dart /RHWM

DART No.2

Index Corruption

Possible causes of index corruption include:

• Hardware or software failure
• Killing an executing process
• After migration from one system to another

The symptoms you will experience include:

• Bad page errors
• SQL0980C during a backup database command
• SQL0902C - database marked bad

DBA Dilemma - Should I restore from backup?  Should I call IBM Support?   No, use DB2DART!

• Find out which indexes are problematic
  - db2dart <db> /DD /TSI <pool id> /OI <object id>
  - db2dart <db> /DI /TSI <pool id> /OI <object id>
• Mark indexes as invalid
  - db2dart <db> /MI /TSI <pool id> /OI <object id>
• Indexes recreated based on INDEXREC (dbm/db configuration parameter)
  - RESTART
  - ACCCESS
  - Monitor using db2diag.log

DART No.3

Extracting Data

Another problem DBAs face is extracting data.  This can be due to a number of causes:

• Table space or database is corrupt or inaccessible
• Access to "bad/damaged" data causes an instance crash
• Need to salvage data
    o Cannot use SQL to select data
    o Cannot use Export
    o Restore from backup image
• Does one exist?
• Circular logging?

The DBA Dilemma - Should I call IBM Support? No, use DB2DART!

• db2dart <db> /DDEL
• Data dumped in delimited ASCII format
• Prompted for
  - table name (or ID),
  - table space ID,
  - starting page,
  - number of pages, and
  - output file name

LONG VARCHAR or LOB data will not be dumped

• May require multiple invocations
  - Dump up to "bad/damaged" page using /DDEL option
     - Aborts on reaching corruption
  - Dump "bad/damaged" page using /DD option
    - Incorrupt data can be viewed
  - Dump rest of pages after "bad/damaged" page using /DDEL option and appropriate start page
• Some data loss inevitable

Back-up Pending State (aka "Forced backup")

The DBA Dilemma - Do I really need to back-up?

Backup-pending state results from the following operations:

• LOAD with COPY NO (default)
• Logging strategy changed (circular to archival)
• LOGARCHIVEenabled
• LOGFILSIZchanged while using raw device for logging

This necessitates a database or tablespace backup to remove the backup-pending state before normal activity can be resumed on the database. Although this is a meant to be a safeguard, it may not be necessary to always take a backup following one of the operations above. This is especially true in environments where a nightly backup is scheduled anyway, or in a development/test environment. And this is where our bonus DB2DART comes into play.

Bonus DB2DART!

• db2dart <db> /CHST /WHAT DBBP OFF

The /CHST /WHAT DBBP OFF option of DB2DART will effectively take the database out of backup pending state without having to perform a database or tablespace backup.

Of course, the above can also be achieved using the following commands:

• Backup database <dbname> to /dev/null (UNIX, Linux)
• Backup database <dbname> to NUL (Windows)

However, DB2 still has to read tablespace information before completing these operations .

Does DB2DART work with DPF?

Yes!  You can run DB2DART on the desired partition or on all partitions.  Use db2_all to run DB2DART on all partitions in a single invocation.  The default destination of the report file is in the diagnostic directory.

Summary

DB2DART is a very powerful tool.  DB2DART increases productivity in crisis situations and can be used by DBAs to deal with:

• Storage reclamation
• Index corruption
• Extracting data
• Database backup pending state