Working with reorganization guidelines for thresholds

Consider the following statistics to determine when to initiate a reorganization for a particular index or table space:

• Index reorganization

  • Percent Pseudo-Deleted RIDs (PSEUDODL) is the percentage of rows that have been pseudo-deleted in an index. As delete processing occurs, index entries are deleted from leaf pages. However, Db2 continues to keep leaf pages as long as one index entry exists on that page. If heavy insert processing occurs followed by a large number of deletes, the number of pages that Db2 scans when searching for an index entry can be high because only a few index entries might exist on each leaf page. In this case, you might need to reorganize the index.

    IBM recommends reorganizing an index when %Pseudo-deleted Keys is greater than ten percent. IBM also recommends to consider using the REUSE option with IBM REORG in this situation.

    This exception is true if:

    RS_INDEXPART.PSEUDO_DEL_ENTRIES / RS_INDEXPART.CARDF >= specifiedValue

  • Leaf TotalOff (LEAFTOFF) is the percentage of leaf pages that are not in their optimal positions. This value includes leaf pages that are physically located near the previous leaf pages as well as leaf pages that are physically located far from the previous leaf page.

    This exception is a much more effective measure of the disorganization of an index than the traditional LEAFDIST statistic. LEAFDIST measures the number of pages between successive leaf pages. This value can become quite large with a single page split (such as a single page being out of position). Because LEAFDIST is the average distance between successive leaf pages, the value might not change significantly from the first page split to the 100th page split. Instead, LeafTotalOff measures the percentage of pages in the index that are out of position, giving a more effective view of the overall organization of an index.

    The exception is true if:

     (RS_INDEXPART.LEAFNEAR + RS_INDEXPART.LEAFFAR) / RS_INDEXPART.NLEAF >=
    specifiedValue

  • Leaf FarOff (LEAFFOFF) is the percentage of leaf pages that are located physically far away from previous leaf pages (for successive active leaf pages accessed in an index scan).

    The exception is true if:

    RS_INDEXPART.LEAFFAR / RS_INDEXPART.NLEAF >= specifiedValue
  • The Leafdist (LEAFDIST) value is 100 times the average number of pages between successive leaf pages. The minimum number depends primarily on the FREEPAGE value, which is the leaf distribution immediately after a reorganization. The larger the number beyond the minimum, the more disorganized the index is. Consider reorganizing the index when the leaf distribution increases 200 beyond the minimum.
  • Dataset Extents (DSEXTENTS) is the number of extents in a data set. The maximum number of supported extents is 7257. Data sets with a large number of extents might indicate a potential problem that a reorganization can help resolve. You might not want to reorganize based solely on the number of extents, but when you do reorganize, try to eliminate extents by using the RESIZE parameter on the DB2 REORG utility or the BMC Software REORG PLUS utility.
  • The AREO* pending (AREOPEND) value indicates whether to raise an exception if the table space is in advisory REORG-pending status.
  • The AREOR pending (ARERPEND) value indicates whether a table space, index, or partition needs to be reorganized for optimal performance and whether to apply pending definition changes.
  • The Append inserts (APPNDINS) is the percentage of index entries that have been inserted since the last REORG, REBUILD INDEX, or LOAD REPLACE on the index space or partition that have a key value that is greater than the maximum key value in the index or partition.
  • The Mass del reorg (REORMDEL) value indicates whether to evaluate for any mass deletion since the last REORG (evaluated at the partition level).
  • The Mods since reorg (REORMODS) value identifies whether to evaluate the percentage of keys that have been modified since the last REORG (evaluated at the partition level).
  • The Ins since reorg (REORINS) value identifies whether to evaluate the percentage of keys that have been inserted since the last REORG (evaluated at the partition level).
  • The Del since reorg (REORDEL) value identifies whether to evaluate the percentage of keys that have been deleted since the last REORG (evaluated at the partition level).
  • The Reorg Leaf (REORLEAF) value identifies whether to evaluate the percentage of total pages in comparison to number of active leaf pages (evaluated at the partition level).
  • The Level Min (LEVELMIN) exception identifies indexes whose levels are greater than the number that are required to reorganize the index (evaluated at the index partition level if you specify Partlvl Y; otherwise, it is evaluated at the index level).
  • Reorgspace (REORSPACE) indicates the estimated amount of space that the index should occupy after a reorganization. This value can be greater than or less than the space that is being used. The value might be greater if a large number of rows has been added to the table. A reorganization can add free space back into the index, based on the FREEPAGE and PCTFREE parameters.
• Table space reorganization
  • Reorg Pend (REORPEND) is the indicator that the table space is in REORG PENDING status.
  • FARINDREF (used in Farind (FARIND) and Totalind (TOTALIND)) is the number of referenced rows that are more than 16 pages from the original page. NEARINDREF (used in Totalind (TOTALIND)) is the number of referenced rows that are less than 16 pages from the original page. A large FARINDREF value can indicate an increase in I/O to the data set that are caused by indirect references to updated VARCHAR columns.
  • FAROFFPOS (used in Faroff (FAROFF) and Totaloff (TOTALOFF)) is the number of referenced rows that are greater than or equal to 16 pages from their optimal positions. NEAROFFPOS (used in Totaloff (TOTALOFF)) is the number of referenced rows that are less than 16 pages from their optimal positions. The product collects these statistics for indexes, but the statistics apply to the data. The statistics indicate the degree of clustering, and the DB2 Optimizer uses them for clustering and nonclustering indexes. FAROFFPOS and NEAROFFPOS are used as exceptions only for clustering indexes, and PCTCLUST is preferred. Both statistics will approach zero for clustered data.
  • Reorgspace (REORSPACE) indicates the estimated amount of space that the table space should occupy after a reorganization. This value can be greater than or less than the space that is being used. The value might be greater if a large number of rows has been added to the table. A reorganization can add free space back into the table space, based on the FREEPAGE and PCTFREE parameters.
  • Pctclus (PCTCLUS) applies to the degree of clustering of the data. This value is collected for a clustering index. You might want to begin by setting a low threshold, such as 85 percent, and handle the worst-case table spaces first.
  • Dataset Extents (DSEXTENTS) is the number of extents in a data set. The maximum number of extents supported is 7257. Data sets with a large number of extents might indicate a potential problem that a reorganization can help resolve. You might not want to reorganize based solely on the number of extents, but when you do reorganize, you should attempt to eliminate extents at that time. You can do this by using the RESIZE parameter on the DB2 REORG utility or the BMC Software REORG PLUS utility.
  • The AREO* pending (AREOPEND) value indicates whether to raise an exception if the table space is in advisory REORG-pending status.
  • The AREOR pending (ARERPEND) value indicates whether a table space, index, or partition needs to be reorganized for optimal performance and whether to apply pending definition changes.
  • The Mass del reorg (REORMDEL) value indicates whether to evaluate for any mass deletion since the last REORG (evaluated at the partition level).
  • The LOB ORGRATIO (ORGRATIO) value indicates the percentage of organization in the LOB table space and this value is triggered if it is lower than the value that you entered.
  • The LOB Freespace (LOBFRSPC) value indicates whether a table space needs to be reorganized based on the percentage of the LOB that is freespace.
  • Unclust inserts (UNCLUST) is the percentages of unclustered inserts. Use this exception to initiate a reorganization of a table space that has a high number of unclustered inserts.
  • The Mods since reorg (REORMODS) value identifies whether to evaluate the percentage of rows that have been modified since the last REORG (evaluated at the partition level).
  • The Ins since reorg (REORINS) value identifies whether to evaluate the percentage of rows that have been inserted since the last REORG (evaluated at the partition level).
  • The Del since reorg (REORDEL) value identifies whether to evaluate the percentage of rows that have been deleted since the last REORG (evaluated at the partition level).
  • Pct over alloc (REORDSPC) value identifies the percentage by which space allocated to a table space or partition exceeds space occupied by row data.