AIX > Administrator > DB2

Boosting DBA Productivity

In this installment, we'll examine the new features in DB2* V8 that make it more available, scalable, and easier to build applications allowing you to focus on your business goals, rather than the technology.

A More Available Foundation

Much effort went into making DB2 more available. Availability encompasses far more than setting up disaster recovery or failover scenarios (which DB2 does well). What about maintenance? Taking a database down for maintenance may not be a disaster, but it still means lack of access until the maintenance is complete. With increasing demand for higher availability and shrinking maintenance windows, it's more important than ever for databases to provide online maintenance utilities. DB2 V8.1 fits this bill.

Tables now can be reorganized online using an innovative approach to table REORGs. Unlike the shadow approach used by other databases, DB2 can REORG a table in piecemeal fashion (think of it as a "trickle" REORG)--a set number of pages at a time. Online table REORG goes through vacate and fill phases, committing to disk each piece, then moving on (see Figure 1).

This means that the benefits of an online table REORG are immediate, and minimal extra storage is required to perform the REORG. During the REORG process, the DB2 table is available for full read and write operations. Because the REORG works on only a small number of pages per vacate and fill phase, concurrency is high. At worst, a transaction may be trying to update a page that's being REORGed and must wait during those brief moments while the REORG phases complete. The new online table REORG is also pauseable, stoppable and can be directed to specific database partitions.

Because the DB2 approach isn't a shadow implementation, you may be concerned about its performance impact. The DB2 V8.1 online table REORG was built with concurrency and minimal performance impact from the start. In a TPC-C-like workload, DB2 demonstrated a less than or equal to 1-percent performance degradation during an online table REORG.

DB2 V8.1 also supports read and write operations to existing indexes during an index REORG and an online index create. The online CREATE INDEX and REORG are implemented via the shadow approach. During online index reorganization, the entire index object is rebuilt. A shadow copy of the index object is made, leaving the original indexes and the table available for read and write access. Any concurrent transactions that update the table are logged.

Kelly Masci is a member of the DB2 Information Development team, specializing in data movement and database backup and recovery. Kelly can be reached at

Paul C. Zikopoulos is a database specialist IBM. Paul can be reached at

comments powered by Disqus



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Data Basics, Part 2

More tips for avoiding common DB2 performance mistakes

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters