14.3.2 Setting the percentage of unused space in a page

The percentage of unused space allocated in a page when a table or index is defined is called the percentage of unused space in a page. When an unused space value is set, the database load utility (pdload) and database reorganization utility (pdrorg) will not normally store data in the specified amount of space.

However, if the database load utility is executed with the -y option specified and no new page can be allocated, it will store data in the specified unused space.

The following figure provides an overview of the unused space in pages.

Figure 14-3 Overview of unused space in pages

[Figure]

Organization of this subsection
(1) Effects of setting a percentage of unused space in a page
(2) Criteria
(3) Specification
(4) Notes
(5) Obtaining the percentage of unused space in a page

(1) Effects of setting a percentage of unused space in a page

(2) Criteria

  1. You should specify a percentage of unused space in a page if rows will be added to a table for which a cluster key is specified.
  2. For a table with the FIX attribute, if data will be sorted in ascending order, you can improve the storage efficiency by setting the percentage of unused space in a page to 0.
  3. You should specify a percentage of unused space in a page if rows will become longer as a result of update processing.
  4. Rows become longer when the following types of update processing are executed:
    • The null value is updated to real data.
    • A column with the VARCHAR, NVARCHAR, MVARCHAR or BINARY data type is updated so that the value becomes longer.

(3) Specification

To specify the percentage of unused space in a page, use the PCTFREE option of the CREATE TABLE or CREATE INDEX definition SQL statement.

(4) Notes

If the set amount of unused space is too small and a row becomes longer as a result of update processing, the number of input/output operations increases because a single row spans multiple pages.

(5) Obtaining the percentage of unused space in a page