Reorganization of a LOB table space

Run the REORG TABLESPACE utility on a LOB table space to help increase the effectiveness of prefetch processing. When you run REORG on a LOB table space, the utility removes embedded free space, attempts to make LOB pages contiguous, and reclaims physical space.

You can reorganize a LOB table space separately from the base table space or you can reorganize the base and LOB table spaces together. To reorganize them together, specify the base table space name in the REORG statement and the AUX YES option. For restrictions, see AUX YES.

To reorganize a LOB table space separately from the base table space, specify the LOB table space name in the REORG statement. REORG unloads LOBs to a shadow data set and reclaims any physical space.

For LOB table spaces, certain REORG statement options are not applicable, including the following options:

  • AUTOESTSPACE
  • AUX YES
  • DISCARD
  • DISCARDDN
  • INDREFLIMIT
  • NOPAD
  • NOSYSREC
  • OFFPOSLIMIT
  • PART
  • PREFORMAT
  • PUNCHDDN
  • REBALANCE
  • REPORTONLY
  • REUSE
  • ROWFORMAT
  • SAMPLE
  • SHRLEVEL NONE
  • SORTDATA
  • SORTDEVT
  • STATISTICS
  • UNLOAD ONLY
  • UNLOAD EXTERNAL
  • UNLOAD PAUSE
  • KEEPDICTIONARY

When you reorganize a LOB table space, you cannot specify SHRLEVEL NONE. You must specify SHRLEVEL REFERENCE or CHANGE. You must also specify LOG NO and take an inline image copy.

REORG SHRLEVEL CHANGE processes a LOB table space the same as REORG SHRLEVEL REFERENCE except that the mapping table is ignored. REORG SHRLEVEL CHANGE uses shadow data sets and includes a LOG phase.

Start of change

Reorganization of a compressed LOB table space

Unlike data compression for a base table space, no compression dictionary is built for a compressed LOB table space. If you specify the KEEPDICTIONARY option in the REORG statement for a LOB table space, that option is ignored.

Materializing COMPRESS NO to COMPRESS YES
If you use REORG TABLESPACE to materialize a pending alter that changes uncompressed LOB data to compressed LOB data, the original LOB data is unloaded in its uncompressed format. Db2 attempts to compress the LOB data by using the zEDC hardware before inserting the LOB data into the shadow page set.

Some LOB data does not compress well. (When the length of the compressed format is greater than or equal to the length of the uncompressed format, the data has an inferior compression ratio.) If the LOB data does not compress well, it is inserted in its uncompressed format. Therefore, a LOB table space that is specified to use compression could have uncompressed LOB data.

Materializing COMPRESS YES to COMPRESS NO
If you use REORG TABLESPACE to materialize a pending alter that changes compressed LOB data to uncompressed LOB data, REORG uncompresses the LOB data before it is inserted into the shadow page set.
COMPRESS attribute is not changed
If the compression attribute of the LOB table space is not changed, REORG reorganizes the data with minimal decompression or re-compression.

For a LOB table space that is defined with COMPRESS NO, REORG decompresses any compressed LOB data before inserting it into the shadow page set. This action ensures that after a successful REORG, only uncompressed LOB data remains in the table space.

For a LOB table space that is defined with COMPRESS YES, REORG unloads and copies the compressed LOB data into the shadow data set. For any uncompressed LOB data that does not have an inferior compression ratio, REORG attempts to compress the LOB data.

The following factors can affect whether REORG needs to decompress and re-compress all LOB data in a compressed LOB table space:

  • REORG AUX YES is run on the base table to materialize a change to the INLINE LENGTH value of the LOB column.
  • REORG is run on a LOB table space with pending page size changes.
End of change