Choosing data page sizes for LOB data

Choosing a data page size for LOBs (in the LOB table space) is a trade-off between minimizing the number of getpage operations (maximizing performance) and not wasting space.

About this task

With LOB table spaces, no more than one LOB value is ever stored in any single data page in a LOB table space. Space that is not used by the LOB value in the last page that is occupied by the LOB remains unused. DB2® also uses additional space for control information. The smaller the LOB value, the greater the proportion of space for this "non-data" is used.

For example, if you have a 17-KB LOB, the 4-KB page size is the most efficient for storage. A 17-KB LOB requires five 4-KB pages for a total of 20 KB of storage space. Pages that are 8 KB, 16 KB, and 32 KB in size waste more space, because they require 24 KB, 32 KB, and 32 KB, respectively, for the LOB.

To optimize the use of space by LOB data, use the following approaches:

• If not all LOB values are the same size, use the following formula to estimate the size:

  LOB size = (average LOB length) 
  ×
   1.05

  The following table contains some suggested page sizes for LOBs with the intent to reduce the number of I/O operations (getpages).

  Table 1. Suggested page sizes based on average LOB length

  Average LOB size (n)	Suggested page size
  n ≤ 4 KB	4 KB
  4 KB < n ≤ 8 KB	8 KB
  8 KB < n ≤ 16 KB	16 KB
  16 KB < n	32 KB

• If all LOB values are the same size, use the values in the following table to choose an appropriate page size:

  Table 2. Suggested page sizes when LOBs are the same size

  LOB size (y)	Suggested page size
  y ≤ 4 KB	4 KB
  4 KB < y ≤ 8 KB	8 KB
  8 KB < y ≤ 12 KB	4 KB
  12 KB < y ≤ 16 KB	16 KB
  16 KB < y ≤ 24 KB	8 KB
  24 KB < y ≤ 32 KB	32 KB
  32 KB < y ≤ 48 KB	16 KB
  48 KB < y	32 KB