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
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
What next? Call IBM Support? No, use DB2DART!
Using the DB2DART Database High Water Mark display option (/DHWM) you can view:
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.
DB2DART /RHWM - Removes empty SMP extents holding up the high-water mark
Possible causes of index corruption include:
The symptoms you will experience include:
DBA Dilemma - Should I restore from backup? Should I call IBM Support? No, use DB2DART!
Another problem DBAs face is extracting data. This can be due to a number of causes:
The DBA Dilemma - Should I call IBM Support? No, use DB2DART!
LONG VARCHAR or LOB data will not be dumped
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:
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.
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:
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.
DB2DART is a very powerful tool. DB2DART increases productivity in crisis situations and can be used by DBAs to deal with: