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.

Procedure

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
  • Start of changeConsider whether to compress your LOB data.
    The following conditions are required for LOB compression in Db2 for z/OS®:
    • The zEDC hardware and software must be available and configured in the z/OS system. For more information, see Requirements for zEnterprise Data Compression.

      In data sharing, system performance can degrade dramatically if a member accesses compressed LOB data and the zEDC hardware and software are not configured in z/OS.

    • The LOB table space must be associated with a base table that is in a universal table space.
    • The total length of the entire LOB must be larger than the defined data page size, otherwise the LOB is not compressed.

    For more information, see Compressing LOB data.

    End of change