When a DB2 database is first created and loaded, it’s in fine shape; data is in sequence and free space elements exist. However, over time, the objects can become disorganized, especially the indexes. There are two main reasons to reorganize a DB2 database: I/O performance and space reclamation/consolidation.
In the first case, performance is critical and is closely monitored in production DB2 applications. A long-running transaction may abort or a user may cancel it. Many DB2 applications are Web-enabled. A user doesn’t know or care what the cause of the long-running transaction is and may simply abort the transaction and go to another Website. Once this business is lost, it’s lost forever.
In the second case, effective space management can lead to lower operating costs and higher availability. Even in newer releases of DB2, you can eventually run out of available space for inserting or loading data, resulting in a failed transaction or an outage.
DB2 objects are designed to support both online transactions and query reporting. Online transactions are generally record-level events (although set-level updates are possible). Queries generally sweep many records into a result set. The query records may be joined from several tables, resulting in significant work to derive the result set.
Inserts, updates, and deletes affect data location and space usage. A new row inserted into a table can result in hundreds of I/O events due to processing on the table and all its indexes. Indexes want to maintain data sequence, so an insert may cause data movement and page splits in the index. These index leaf page splits can become quite expensive, so it’s important to maintain enough free space in the index pages to prevent splits. Reorganizing the index sorts all the index entries and reclaims wasted space, making subsequent processing more efficient.
Query processing presents a different challenge. Queries generally pull data from several tables into a result set. The join processing can become complicated and it isn’t unusual for a query to process millions of rows of data to derive a relatively small result set. A disorganized table space or index can result in processing many more pages than would be required if the objects were properly organized. DB2 generates an access path during the bind process. The access path is affected by the catalog statistics on the objects, so a disorganized object can result in an ineffective access path and wasted resources.
Free Space Reclamation/Consolidation
When you define indexes and table spaces, you allocate free space with CREATE or ALTER parameters. As application data is modified, some data movement occurs and free space can be consumed. When no space is available for an insert, DB2 must do some work to make room for the new data. For index processing, this can result in moving hundreds of pages. It’s important to monitor the appropriate statistics and trigger reorganization when the objects reach a critical free space shortage.
DB2 data is stored in VSAM data sets. It’s possible to run out of space on these types of data sets, so monitoring space at the data set level is critical. Failure to monitor space usage can result in a significant application outage.