Debunking the online vs offline db2dart myths

Body

There is a common misconception that db2dart is only an offline tool.   To understand the truth we have to understand 2 things:  that db2dart only reads data from disk and that there are 3 distinct types of db2dart options.     

The answers begin by understanding that db2dart only reads data that is stored on disk.  This can cause issues when multiple pages are compared, such as during an inspection operation.   For example, if db2dart is validating a table and a user has inserted a row into a table with one or more indexes, then its possible only the data page has been flushed to disk.  The modified index page may still be sitting in the buffer pool.  This can lead to the situation where db2dart detects a mismatch between the  row count of the table and the row count of the index.    There is nothing wrong with the table,  its just that db2dart is comparing a current data page with a stale index page.

Now lets looks at the primary options for each type of db2dart

  Inspect actions:
    /DB    (default) Inspects entire database.
    /T       Inspects one or more tables.
    /TS    Inspects one or more table spaces and their tables.

  Data format actions:
    /DD       Dumps formatted table data.  
    /DI         Dumps formatted index data.  
    /DP        Dumps pages in hex format.   
    /DEMP   Dumps formatted EMP information for a DMS table.
    /DDEL    Dumps formatted table data in delimited ASCII format.
    /DHWM  Dumps high water mark information.

  Repair actions:
  Make sure the database is offline for these actions.
    /MI    Marks index object as invalid.
    /MT   Marks data object as invalid.  

Now lets break down the details of each type of db2dart

Details of repair actions:   

This one is easy.  Repair operations are only allowed when the database is offline.    Db2dart will fail and produce an error indicating the database must be deactivated prior to running these commands.   

Details of format actions:

These options are used to display the contents of pages from disk.   The decision to produce formatted output with the database online or offline really comes down to how you intend to use the data.   

If you are going to dump an entire table and require that it be in a consistent state, then the database must be offline.   One example of this is the db2dart /ddel option of db2dart.    This /ddel option is used to extract data from a table while excluding data from corrupted and unreadable data pages.    Since valid pages in the table may still be loaded into the bufferpool and modified,   the db2dart /ddel option might miss these modifications that are not yet flushed to disk.

During FODC_BadPage data collection multiple db2dart commands will be executed for diagnostic purposes.  As part of database resiliency the database is kept online unless transactional consistency is compromised.   In this case multiple db2dart dump options will be executed while the database is online to collect as much diagnostics about the problem as possible.     The fact that the data dumped may or may not be the most current version of the page in memory must be considered when doing analysis on db2dart data collected.   This is rarely a problem as the dump options are typically over a small set of pages.

Often times the purpose of these online dumps is to validate the basic sanity of the page.   This can be determined from the most recent copy of the page written to disk without regard to whether the contents of the page have been changed in memory.
 

Details of Inspect actions:

This is probably the most complex of all the options to determine if it should be run offline or online.   Unfortunately the real answer to this is ‘it depends’.    The inspect options do many different types of validations.    For a complete list of validations performed see https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.trb.doc/doc/c0020763.html

The easiest way to think about it is whether or not the validation is on a single page or whether it involved multiple pages.    An online db2dart doing validation on a single page will still produce valid results.    An online db2dart doing validation of multiple pages may produce inconsistent results,  often called a ‘false positive’ result.

Single page validations include:
    validating the page header to make sure all values are as expected
    validating the checksum or consistency bit
    validating length and position of varchar columns
    verify if rows are overlapping

Multiple page validations include:
    comparing the number of data rows with the number of index keys
    comparing order of key values or next/previous pointers between index pages
    checking for orphaned extents
    rows that include forwarding pointers to another page

Additional details:

    ▪    If the database was stopped without rolling back all uncommitted transactions then the database will be in an inconsistent state.   Crash recovery will be necessary when restarting the database.   If a db2dart is executed while the database is offline but crash recovery has not yet completed then the following warning will appear in the output file

Warning: The database state is not consistent.
         False errors may be reported.

    ▪    Since db2dart was originally designed to be an offline tool it does a check to see if the database is online while db2dart is being executed.   If the database is online the following message will appear in the output file

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

If either of these 2 messages appear then any errors reported in the output file must be manually evaluated to determine if they are real errors or if they are a ‘false positive’ error.    If the table reporting the error is relatively small, then rerunning db2dart on the table again may be a way to determine if the error is a false positive.   If the same error is not reported on the second iteration, then the first error was indeed a ‘false positive’ error.  Unfortunately getting the same error on both iterations does not guarantee that the error is real, as its still possible for one of the modified pages to still be sitting in the buffer pool.

    ▪    The checking of whether the database is online or not can take several seconds to complete.   In the case where the dump options of db2dart are being used when the database is known to be online (such as during FODC_BadPage collection) this can cause a significant delay in collecting the output.   Db2 has started using an undocumented option to bypass the online database check for any db2dart commands that are executed as part of diagnostics collection.   This same option can be used to speed up the manual collection of db2dart dump commands.     In 9.7 the option is /serv 64.  In 10.1, 10.5 and 11.1 the option is /serv 16.    This option can be appended to a typical db2dart command.    An example for version 10.x or 11.1 would be  “db2dart sample /dd /tsi 0 /oi 5 /ps 0 /np 1 /v y /rptn dd.ts0.oi5.pg0.np1.rpt /serv 16”