DB2 free space: PCTFREE, FREEPAGE and PIECESIZE

What free space is for

A Db2 data page (or index page) has a fixed size: 4 KB, 8 KB, 16 KB, or 32 KB, chosen by the buffer pool. After headers, you have a limited number of bytes for rows or keys. Inserts and updates that make a row longer need leftover bytes on a nearby page. If none exist, Db2 may:

• Place a new row on a distant page (hurts clustering and sequential prefetch)
• Create an overflow pointer for an updated row that no longer fits (two I/Os instead of one)
• Split an index page, which is extra logging and extra levels of the tree later

Free space is the insurance policy. You pay for it as extra DASD and as fewer rows per GETPAGE. You get it back as fewer overflows, fewer index splits, and inserts that stay near the clustering key. Shops tune it after looking at REORG reports, RTS (REORGINSERTS, REORGDISORGLOB, overflow counts), and how volatile the table is.

Important timing rule from the SQL Reference: FREEPAGE and PCTFREE describe how space is left when the object is loaded or reorganized. For indexes, they also apply when CREATE INDEX builds keys for a table that already has rows. Changing the clauses with ALTER updates the catalog; the pages themselves wait for LOAD, REORG, or REBUILD.

PCTFREE

PCTFREE smallint is the percentage of each page to leave free when rows or index entries are placed by a utility (or by CREATE INDEX on existing data). smallint is in the range 0–99.

The first record on a page is loaded without the free-space restriction. After that, Db2 keeps at least smallint percent empty on that page. So a very large row can still occupy almost a whole page; PCTFREE does not refuse the first row.

Default for a table space is PCTFREE 5. Default for an index is also commonly 10 percent in many examples, but you should always spell the value you want. On indexes, if you specify more than 10 percent, IBM still leaves only 10 percent free on nonleaf pages; the extra applies to leaf pages.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLESPACE EMPTS
     IN HRDB
     USING STOGROUP HRSTO
     PRIQTY 720 SECQTY 720
     SEGSIZE 32
     MAXPARTITIONS 16
     PCTFREE 15
     FREEPAGE 8
     BUFFERPOOL BP2
     LOCKSIZE PAGE
     CLOSE NO;

That example leaves 15% of each data page empty and a whole empty page after every eight used pages (see FREEPAGE below). A volatile employee table that gets mid-key inserts benefits; a read-mostly code table might use PCTFREE 0 to pack pages and save GETPAGEs.

PCTFREE FOR UPDATE

Table spaces (not indexes) can add FOR UPDATE smallint. That slice of the page is reserved for later UPDATE operations that make rows longer, as opposed to brand-new INSERTs. The value is −1 to 99. The sum of PCTFREE and FOR UPDATE must be ≤ 99.

• FOR UPDATE 0 — do not reserve extra room specifically for updates
• FOR UPDATE n — keep n percent for expanding updates
• FOR UPDATE −1 — start at 5%, then let Db2 recalculate from real-time statistics (PCTFREE_UPD in SYSIBM.SYSTABLEPART)

The default FOR UPDATE value comes from the PCTFREE_UPD subsystem parameter, not from a hard-coded SQL default in every shop. An update is still allowed to use more than the reserved amount if the page has leftover bytes.

1
2
ALTER TABLESPACE HRDB.EMPTS
     PCTFREE 10 FOR UPDATE 10;

FREEPAGE

FREEPAGE integer says how often to leave a completely empty page during LOAD or REORG. integer is 0–255. FREEPAGE 0 (the default) leaves no empty pages. Otherwise Db2 leaves one free page after every n pages.

Why a whole empty page? Clustering inserts in the middle of a key range often need a new page near the current pages, not 15% of a full page. FREEPAGE plants empty pages in the stream so a nearby insert can take a whole page without jumping to the end of the table space.

On segmented and universal table spaces the free page must still fit in the segment. If you specify a FREEPAGE that is not less than SEGSIZE, Db2 uses SEGSIZE − 1. Example: SEGSIZE 32 and FREEPAGE 40 behaves like FREEPAGE 31.

Partition-level values: if you write FREEPAGE/PCTFREE on a PARTITION clause, that partition uses those numbers. Otherwise it uses the table-space-level free-block, then the defaults (FREEPAGE 0, PCTFREE 5).

1
2
3
4
5
6
7
CREATE INDEX HR.XEMP2
     ON HR.EMP (WORKDEPT, LASTNAME)
     USING STOGROUP HRSTO
     PRIQTY 120 SECQTY 120
     FREEPAGE 10
     PCTFREE 20
     CLOSE NO;

Index free space fights page splits. A unique employee-number index that only grows at the high end (always-increasing identity) often wants low PCTFREE because splits happen at the last leaf. A last-name index with inserts everywhere wants more leaf free space.

Where you must not specify them

IBM forbids FREEPAGE and PCTFREE on:

• LOB table spaces
• Table spaces implicitly created for an XML column
• Table spaces in a work file database
• Implicitly created XML indexes (for the index form of the clauses)

Those objects are not “row pages packed by LOAD” in the same way. LOB and XML pages have their own space management.

Choosing numbers (beginner heuristics)

There is no single correct PCTFREE. Start from how rows change:

• Read-only or append-only — PCTFREE 0, FREEPAGE 0. Pack pages. REORG rarely needed for disorganization.
• Stable length, mid-key inserts — PCTFREE 10–20 and a modest FREEPAGE (for example 8–16) so clustering survives until the next REORG.
• VARCHAR columns that grow — raise PCTFREE or FOR UPDATE so updates do not overflow.
• Always-increasing clustering key — free space in the middle of the table is wasted; keep PCTFREE low and accept that new pages are at the end.

After REORG, watch RTS and COPY/REORG reports. If NEARINDREF / far overflows climb quickly, you needed more in-page space. If inserts still land off-cluster immediately, consider FREEPAGE or a different clustering index, not only a higher percent.

MAXROWS (covered on the table-space design page) also limits how many rows Db2 will put on a page. LOAD and REORG honour PCTFREE before MAXROWS, so you can end up with fewer rows than MAXROWS if PCTFREE is high.

PIECESIZE

PIECESIZE is not a free-space percentage. It belongs on CREATE INDEX and ALTER INDEX for a non-partitioned index (NPI). It sets the maximum addressability of each data set (each “piece”) of that index.

A partitioned table can still have a non-partitioned secondary index: one logical index over all parts. That index can grow huge. VSAM linear data sets have size limits, so Db2 splits the NPI into multiple data sets. PIECESIZE is how you tell Db2 the cap per data set. The subsequent keyword is K, M, or G. The integer must be a power of two in the documented range (for example 1 M, 2 G, 4 G).

Defaults if you omit PIECESIZE: about 2 G when the table space was created without LARGE or DSSIZE; 4 G when LARGE or DSSIZE was used; 4 G for auxiliary (LOB) indexes. You cannot pick 8 G unless the underlying table space DSSIZE is at least 8 G, and so on up to 256 G.

1
2
3
4
5
6
7
CREATE UNIQUE INDEX HR.XEMP1
     ON HR.EMP (EMPNO)
     USING STOGROUP HRSTO
     PRIQTY 48 SECQTY 48
     PIECESIZE 2 G
     COPY YES
     CLOSE NO;

PIECESIZE does not allocate that much disk. PRIQTY and SECQTY still control extents. If PRIQTY is larger than PIECESIZE you waste space: the data set cannot grow past the piece cap. IBM recommends that primary plus secondaries divide evenly into PIECESIZE.

To size it: estimate total NPI size and divide by how many data sets you want. An 10 MB index that will not grow much can use PIECESIZE 2 M to aim at about five pieces. If the index will grow, pick a larger piece so you do not create dozens of tiny data sets. There is a limit on the number of pieces (32 unless the table space has DSSIZE 4 G or greater, then up to 254 in classic documentation).

ALTER INDEX … PIECESIZE puts the index in REBUILD-pending status; you must rebuild before the new piece size is real. Partitioned indexes use DSSIZE per partition instead of PIECESIZE. Do not specify PIECESIZE on a partitioned index.

Catalog and operations

Look at SYSIBM.SYSTABLEPART for table-space PCTFREE, PCTFREE_UPD, and FREEPAGE per partition, and SYSIBM.SYSINDEXPART for index free space. SYSIBM.SYSINDEXES.PIECESIZE stores the index piece size. DISPLAY and LISTCAT still show the actual VSAM extents.

After you change free space, schedule REORG TABLESPACE or REORG INDEX (or LOAD REPLACE) so the new layout exists on disk. Until then SQL runs against the old page map.

Explain It Like I'm Five

Imagine a bookshelf. PCTFREE means you do not cram every shelf edge-to-edge; you leave a gap so a thicker book can still slide in. FREEPAGE means you leave a whole empty shelf after every few full shelves so a new pile of books can sit nearby instead of in the attic. PIECESIZE is not a gap on the shelf — it is the rule “no single cardboard box of index cards may be bigger than this,” so when the card catalog grows you start a new box instead of one impossible giant box.

Exercises

1. Write CREATE TABLESPACE with PCTFREE 20, FREEPAGE 10, and SEGSIZE 32. Explain what happens to FREEPAGE if you had specified FREEPAGE 40 instead.
2. A table is append-only (always-increasing invoice numbers) and rows never grow. Recommend PCTFREE and FREEPAGE and justify the DASD vs I/O trade-off.
3. ALTER TABLESPACE PCTFREE 30. Will the next INSERT see 30% empty pages? What utility makes the change real?
4. Explain PCTFREE FOR UPDATE −1 versus FOR UPDATE 15 for a table whose VARCHAR comments grow over time.
5. An NPI is about 6 GB today on a PBR table space defined DSSIZE 4 G. Pick a legal PIECESIZE and say whether ALTER PIECESIZE requires REBUILD.