Bereitgestellt von Rolf Loeben, IBM Deutschland GmbH, 2001: Triggered by recent events and by an excellent presentation given by Akira on the Las Vegas Technical Conference, here a few lines  about FREEPAGE and PCTFREE.

 Recommendations for allocating free space

The goal for allocating free space is to maintain the physical clustering of the data and to reduce the need to frequently reorganize table spaces and indexes. However, you do not want to allocate too  much DASD space, because it might not be cost-justified. Use of PCTFREE or FREEPAGE depends on the ratio of insertions to deletions, and the distribution of that activity across the index or table space.

When to use FREEPAGE: Use FREEPAGE if: Inserts are concentrated in small areas of the table space or index. Inserts are concentrated in small areas of the table space or index. For indexes where most  of the inserts will be random, set FREEPAGE so that when an index split occurs, the new page is often relatively close to the original page. However, if the index is a type 2 index and the majority of the inserts  occur at the end of the index, set FREEPAGE to 0 to maintain sequential order in the index leaf pages. For table spaces, set FREEPAGE so that new data rows can be inserted into a nearby page when the target page is  full or locked. A nearby page for a nonsegmented table space is within 16 pages on either side of the target page. For a segmented table space, a nearby page is within the  same segment as the target page. MAXROWS 1 or rows are larger than half a page, because you cannot insert a second row on a page.

When to use PCTFREE: Use PCTFREE if inserted rows are distributed evenly and densely across the key or page range. If the volume is heavy, use a PCTFREE value greater than the default. Hints: Consider  setting PCTFREE to 0 to save storage space if: Inserts are in ascending order by the key of the clustering index Setting PCTFREE and FREEPAGE to 0 can maximize insert rate in this case, too, by avoiding page splits.  Inserts, and updates that lengthen the row, are few. For concurrency, use MAXROWS or larger PCTFREE values for small tables and shared table spaces that use page locking. This reduces the number of rows per page,  thus reducing the frequency that any given page is accessed.

For the DB2 catalog table spaces and indexes, use the defaults for PCTFREE. If additional free space is needed, use FREEPAGE.

When a table, whose indexes are already defined, is populated by using the INSERT statement, both the FREEPAGE and the PCTFREE parameters are ignored. FREEPAGE and PCTFREE are only in effect during a  LOAD or REORG operation.

 

 

Haben Sie noch Anregungen zu diesem Beitrag?