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
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. Data clustering might be less important if your storage hardware uses newer control units and solid state disks that can process list prefetch efficiently.
- 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
- 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: