Reserving free space for table spaces

By reserving free space in table spaces you can enable your data to remain clustered longer between reorganizations and you can reduce the number of indirect references and overflow records, which can harm performance.

Before you begin

Begin general-use programming interface information.
To determine the amount of free space that is currently available on a page, run the RUNSTATS utility and examine the PERCACTIVE column of the SYSIBM.SYSTABLEPART catalog table.

About this task

When insufficient free space is available for insert or update operations, DB2® often appends new rows at the end of the table, out of clustering sequence. When updates to existing rows mean that they cannot fit on the original page, DB2 creates indirect references to overflow records on different data pages. When many of these records are physically located out of sequence, performance suffers.

Consequently, a sufficient amount of free space can provide the following advantages during normal processing:

  • Data rows can remain clustered longer after data is reorganized or loaded because random inserts are not needed. Good clustering can improve buffer hit ratios and enables more use of dynamic prefetch. Without clustering, queries must rely on list prefetch for good performance. Start of changeData clustering might be less important if your storage hardware uses newer control units and solid state disks that can process list prefetch efficiently.End of change
  • Indirect references can be avoided. Indirect references are created when an update operation increases the size of a row so that it cannot fit on the original page that contained the row. DB2 stores the row in an overflow page and the original RID points to the overflow RID. Indirect references only occur for varying length rows. The most common cause of indirect references is the use of nullable VARCHAR columns, which initially contain null values and are later updated with non-null values. Indirect references are particularly problematic for queries that use random access or list prefetch. They cause additional CPU cost and more synchronous I/Os. DB2 cannot use list prefetch to read overflow records. You can monitor the NEARINDREF and FARINDREF columns in the SYSIBM.SYSTABLEPART catalog table to find how many rows have indirect references.
  • Fewer data rows are locked by a single page lock, reducing contention when page-level locking is used.

However, specifying too much free space also has disadvantages, including:

  • More disk space is used for the same amount of data.
  • Less information can be transferred by a single I/O operation
  • The same amount of data occupies more pages that must be scanned.
  • Buffer pools and storage controller cache cannot be used as efficiently
You might not need to reserve any free space in certain situations, including:
  • For read-only objects. If you do not plan to insert or update data in a table, no free space is needed for the table space.
  • The object is not read-only, but inserts are at the end, and updates that lengthen varying-length columns are few.

Procedure

To improve the use of free space, use the following approaches:

  • Use the PCTFREE clause in most situations. The PCTFREE clause specifies the percentage of each data page in a table space that is left free when loading or reorganizing the data.

    For example, the default value for table spaces PCTFREE 5, which means that 5% of each data page is kept free when you load or reorganize the data.

    DB2 reserves the specified amount of free space when data is loaded into the table or reorganized by utility operations. DB2 uses the free space later when you insert or update the data. Regardless of the specified PCTFREE values, at least one row is always inserted on each data page.

    Start of changeThe value of PCTFREE applies to the table spaces for hash-organized tables only when you invoke the REORG TABLESPACE utility and specify the AUTOESTSPACE(YES) option.End of change

  • If update activity on compressed data, which often results in longer rows, is heavy or insert volume is heavy, use a PCTFREE value greater than the default value.
  • If you know the number of rows that fit on a data page at their maximum size, use the MAXROWS clause to control the number of rows per page. However, a MAXROWS value that is too small is likely to waste disk space, and a MAXROWS value that is too large is unlikely to prevent indirect references. The MAXROWS clause has the advantage of maintaining the free space even when new data is inserted.
  • Use the FREEPAGE clause to specify how often DB2 leaves a full page of free space when loading data or when reorganizing data. For example, if you specify FREEPAGE 10, DB2 leaves every tenth page free. Use of the FREEPAGE clause is most appropriate in the following situations:
    • If MAXROWS is 1 or rows are larger than half a page. The PCTFREE clause has no impact in this case because DB2 cannot insert a second row on a page.
    • If additional free space is needed for catalog table spaces and indexes. The recommendation is to always use the default PCTFREE values for catalog objects.
  • You might use MAXROWS values to improve concurrency for small tables and shared table spaces that use page-level locking. This approach reduces the number of rows per page, which helps to avoid lock contention. However, the use of row-level locking is an alternative to this approach and might be preferred in many situations.