In contrast, when reuse is active, the database support will process the added record more like an update
operation than an add operation. The database support will maintain a bit map to keep track of deleted
records and to provide fast access to them. Before a record can be added, the database support must use
the bit-map to find the next available deleted record space, read the page containing the deleted record
entry into storage, and seize the deleted record to allow replacement with the added record. Lastly, the
added records are blocked as much as permissible and then written to the file.
To summarize, additional CPU processing will be required when reuse is active to find the deleted
records, perform record level seizes and maintain the bit-map of deleted records. Also, there may be some
additional disk I/O required to read in the deleted records prior to updating them. However, this extra
overhead is generally less than the overhead associated with a sequential update operation.
Performance Expectations
The impact to performance from implementing the reuse deleted records function will vary depending on
the type of operation being done. Following is a summary of how this function will affect performance
for various scenarios:
y
When blocking was not specified, reuse was slightly faster or equivalent to the normal insert
application. This is due to the fact that reuse by default blocks up records for disk I/Os as much as
possible.
y
Increasing the number of indexes over a file will cause degradation for all insert operations,
regardless of whether reuse is used or not. However, with reuse activated, the degradation to insert
operations from each additional index is generally higher than for normal inserts.
y
The RGZPFM (Reorganize Physical File Member) command can run for a long period of time,
depending on the number of records in the file and the number of indexes over the file and the chosen
command options. Even though activating the reuse function may cause some performance
degradation, it may be justified when considering reorganization costs to reclaim deleted record
space.
y
The reuse function can always be deactivated if the customer encounters a critical time window where
no degradation is permissible. The cost of activating/de-activating reuse is relatively low in most
cases.
y
Because the reuse function can lead to smaller sized files, the performance of some applications may
actually improve, especially in cases where sequential non-keyed processing of a large portion of the
file(s) is taking place.
4.14 Performance References for DB2
1. The home page for DB2 Universal Database for System i is found at
http://www-1.ibm.com/servers/eserver/iseries/db2/
This web site includes the recent announcement information, white paper and technical articles, and
DB2 education information.
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008
Chapter 4 - DB2 Performance
62
