Reorganizing the catalog and directory

You can run REORG TABLESPACE on the table spaces in the catalog database (DSNDB06) and on the SCT02, SPT01, DBD01, SYSLGRNX, SYSDBDXA, SYSSPUXA, and SYSSPUXB table spaces in the directory database (DSNDB01).

Important:

You must take a full image copy before and after reorganizing any catalog or directory object. Otherwise, you cannot recover any catalog or directory objects without the full image copies. When you reorganize the DSNDB06.SYSTSCPY table space with the LOG NO option and omit the COPYDDN option, Db2 places the table space in COPY-pending status. Take a full image copy of the table space to remove the COPY-pending status before continuing to reorganize the catalog or directory table spaces.

Running REORG LOG NO COPYDDN avoids the COPY-pending status, because an inline copy is taken during the REORG.

The FASTSWITCH YES option is ignored for catalog and directory objects.

When to run REORG on the catalog and directory

You do not need to run REORG TABLESPACE on the catalog and directory table spaces as often as you do on user table spaces. RUNSTATS collects statistics about user table spaces, which you use to determine whether a REORG is necessary. You can use the same statistics to determine whether a REORG is needed for catalog and directory table spaces. However, Db2 does not generate statistics for certain items in the directory, such as the SYSLGRNX table space and its corresponding indexes DSNLLX01 and DSNLLX02.

Reorganize the whole catalog before a catalog migration or once every couple of years. Reorganizing the catalog is useful for reducing the size of the catalog table spaces. To improve query performance, reorganize the indexes on the catalog tables.

When statistical information indicates that you need to reorganize any of the catalog table spaces that are listed in the following table, you should also reorganize the corresponding directory table space. If the inline LOB length has changed, you should also reorganize any associated LOB directory table spaces.

Table 1. Catalog table spaces and their corresponding directory table spaces
Catalog table space Corresponding directory table space Associated LOB directory table spaces
DSNDB06.SYSTSFAU
DSNDB06.SYSTSCOL
DSNDB06.SYSTSFLD
DSNDB06.SYSTSFOR
DSNDB06.SYSTSIXS
DSNDB06.SYSTSIPT
DSNDB06.SYSTSKEY
DSNDB06.SYSTSREL
DSNDB06.SYSTSSYN
DSNDB06.SYSTSTAU
DSNDB06.SYSTSTPT
DSNDB06.SYSTSTAB
DSNDB06.SYSTSTSP
 DSNDB01.DBD01 DSNDB01.SYSDBDXA
DSNDB06.SYSTSDBR
DSNDB06.SYSTSPLN
DSNDB06.SYSTSPLA
DSNDB06.SYSTSPLD
DSNDB06.SYSTSSTM
 DSNDB01.SCT02 None
DSNDB06.SYSTSPKG
DSNDB06.SYSTSPKA
DSNDB06.SYSTSPKD
DSNDB06.SYSTSPKL
DSNDB06.SYSTSPKS
DSNDB06.SYSTSPLY
 DSNDB01.SPT01
DSNDB01.SYSSPUXA
DSNDB01.SYSSPUXB

Associated directory table spaces

When certain catalog table spaces are reorganized, you should also reorganize the associated directory table space. The associated directory table spaces are listed in the previous table.

Limitations for reorganizing the catalog and directory

  • You cannot reorganize DSNDB01.SYSUTILX.
  • If SHRLEVEL NONE is specified, the UNLOAD ONLY or UNLOAD EXTERNAL and LOG YES options are not allowed for catalog and directory table spaces. However, LOG YES is required if SHRLEVEL NONE is specified for the catalog LOB table spaces. If SHRLEVEL REFERENCE is specified, LOG NO must be specified.
  • The SORTDEVT and SORTNUM options are ignored for the following catalog and directory table spaces:
    • DSNDB06.SYSTSFAU
    • DSNDB06.SYSTSCOL
    • DSNDB06.SYSTSFLD
    • DSNDB06.SYSTSFOR
    • DSNDB06.SYSTSIXS
    • DSNDB06.SYSTSIPT
    • DSNDB06.SYSTSKEY
    • DSNDB06.SYSTSREL
    • DSNDB06.SYSTSSYN
    • DSNDB06.SYSTSTAU
    • DSNDB06.SYSTSTPT
    • DSNDB06.SYSTSTAB
    • DSNDB06.SYSTSTSP
    • DSNDB06.SYSTSDBA
    • DSNDB06.SYSTSDBU
    • DSNDB06.SYSTSSTG
    • DSNDB06.SYSTSVOL
    • DSNDB06.SYSTSDBR
    • DSNDB06.SYSTSPLN
    • DSNDB06.SYSTSPLA
    • DSNDB06.SYSTSPLD
    • DSNDB06.SYSTSSTM
    • DSNDB06.SYSTSVWD
    • DSNDB06.SYSTSVEW
    • DSNDB01.DBD01
    • Any LOB table spaces, such as DSNDB01.SYSDBDXA (For more information about restricted REORG options for LOB table spaces, see Reorganization of a LOB table space.)
    The COPYDDN and RECOVERYDDN options are valid for the preceding catalog and directory tables if SHRLEVEL REFERENCE is also specified.
  • REORG TABLESPACE with STATISTICS cannot collect inline statistics on the following catalog and directory table spaces:
    • DSNDB06.SYSTSFAU
    • DSNDB06.SYSTSCOL
    • DSNDB06.SYSTSFLD
    • DSNDB06.SYSTSFOR
    • DSNDB06.SYSTSIXS
    • DSNDB06.SYSTSIPT
    • DSNDB06.SYSTSKEY
    • DSNDB06.SYSTSREL
    • DSNDB06.SYSTSSYN
    • DSNDB06.SYSTSTAU
    • DSNDB06.SYSTSTPT
    • DSNDB06.SYSTSTAB
    • DSNDB06.SYSTSTSP
    • DSNDB06.SYSTSDBA
    • DSNDB06.SYSTSDBU
    • DSNDB06.SYSTSSTG
    • DSNDB06.SYSTSVOL
    • DSNDB06.SYSTSDBR
    • DSNDB06.SYSTSPLN
    • DSNDB06.SYSTSPLA
    • DSNDB06.SYSTSPLD
    • DSNDB06.SYSTSSTM
    • DSNDB06.SYSTSVWD
    • DSNDB06.SYSTSVEW
    • DSNDB06.SYSSTATS
    • DSNDB06.SYSHIST
    • DSNDB01.DBD01
    • Any LOB table spaces, such as DSNDB01.SYSDBDXA (For more information about restricted REORG options for LOB table spaces, see Reorganization of a LOB table space.)

Phases for reorganizing the catalog and directory

REORG TABLESPACE processes certain catalog and directory table spaces differently from other table spaces; it does not execute the BUILD and SORT phases for the following table spaces:

  • DSNDB06.SYSTSFAU
  • DSNDB06.SYSTSCOL
  • DSNDB06.SYSTSFLD
  • DSNDB06.SYSTSFOR
  • DSNDB06.SYSTSIXS
  • DSNDB06.SYSTSIPT
  • DSNDB06.SYSTSKEY
  • DSNDB06.SYSTSREL
  • DSNDB06.SYSTSSYN
  • DSNDB06.SYSTSTAU
  • DSNDB06.SYSTSTPT
  • DSNDB06.SYSTSTAB
  • DSNDB06.SYSTSTSP
  • DSNDB06.SYSTSDBA
  • DSNDB06.SYSTSDBU
  • DSNDB06.SYSTSSTG
  • DSNDB06.SYSTSVOL
  • DSNDB06.SYSTSDBR
  • DSNDB06.SYSTSPLN
  • DSNDB06.SYSTSPLA
  • DSNDB06.SYSTSPLD
  • DSNDB06.SYSTSSTM
  • DSNDB06.SYSTSVWD
  • DSNDB06.SYSTSVEW
  • DSNDB01.DBD01

For these table spaces, REORG TABLESPACE reloads the indexes (in addition to the table space) during the RELOAD phase, rather than storing the index keys in a work data set for sorting.

For all other catalog and directory table spaces, Db2 uses index build parallelism.