11.1.4 Difference in functions from pdrorg

The pdrorg utility should be used for reorganization when online operations can be suspended or when the inner replica facility can be used for reorganization in the online mode. This is because pdrorg provides more data rearrangement functions than pdreclaim. When pdrorg is used, data is rearranged as shown in Figure 11-6, thereby releasing more used free pages and used free segments.

Figure 11-6 When pdrorg is used to release used free pages and used free segments

[Figure]

Compared with pdrorg, pdreclaim provides the following advantages:

Therefore, if all of the following conditions are satisfied, we recommend that you use pdreclaim:

  1. No variable-length character strings have been updated (no branch rows have been created).
  2. There have been no changes in the number of elements of repetition columns (no branch rows have been created).
  3. No null values have changed to real data, or vice versa (no branch rows have been created).
  4. No cluster key index is defined (there is no need to handle cluster keys).
  5. There is no LOB column or column of an abstract data type with the LOB attribute.
  6. During data deletions, a large amount of physically adjacent data was deleted at the same time (used free pages were created). Or, the table is a SEGMENT REUSE table.

A SEGMENT REUSE table with the FIX attribute for which no cluster key index is defined is an ideal candidate for pdreclaim processing. Even if these conditions are not all satisfied, pdreclaim can avoid the extreme performance degradation that can result from a free space search in used pages, because the utility releases used free pages. This could make it possible to change from weekly execution of pdrorg to monthly execution.

For indexes, use of pdreclaim is most suitable if deleted key values will never be re-registered. If key values are frequently updated and deleted, pdreclaim can reduce the frequency of space shortages in RDAREAs for indexes without having to reorganize the indexes with pdrorg.

To determine which utility should be used, check the results of executing the database condition analysis utility. If there are many pages whose usage is 0%, execute pdreclaim; if there are many pages whose usage is greatly different from the value specified for PCTFREE in the CREATE TABLE statement, execute pdrorg.