Reorganizing objects with pending definition changes

• Any pending changes on a LOB table space or on a base table space that contains a LOB column
• A pending limit key change when all of the following conditions exist:
  • You are reorganizing more than one but not all partitions of a table space.
  • The pending limit key change is the only pending change.
• A pending MAXPARTITIONS change when either of the following conditions exists:
  • Pending changes existed on the object before the MAXPARTITIONS change was requested.
  • The MAXPARTITIONS change is not the first change in a particular request.
  • To avoid invoking DSNUTILB, we recommend that you alter MAXPARTITIONS before any other alters on the object that would result in pending changes.
• (Pre-SPE2401) Pending changes to the table are due to ALTER COLUMN that contains SET DATA TYPE keywords when ZPARM DDL_MATERIALIZATION=ALWAYS_PENDING.
• (BMC.DB2.SPE2401)Certain pending column alters require DSNUTILB. For more information, see Additional features that require DSNUTILB.
• REORG PLUS invokes DSNUTILB when reorganizing an object that has been recovered to a point in time that precedes previously materialized pending DDL.
• Pending changes from ALTERING the index COMPRESSION.
• Pending changes from ALTER ADD partition.
• When a table space is altered from PBG to PBR.
• When a multi-table table space is converted to an individual table space or table via ALTER TABLE MOVE alter.

Requirements

If the last partition in the table space is included in a pending ALTER of a limit key, you must include an SYSARC data set in your job. For more information about this data set, see SYSARC-data-sets-in-REORG-PLUS.

Restrictions

The following restrictions apply to pending definition changes:

• REORG PLUS does not materialize pending definition changes but reorganizes the objects when any of the following conditions exist:
  • You are running a partial reorganization. If the only pending change on the table space is a limit key change and you are reorganizing more than one partition, REORG PLUS invokes DSNUTILB.
  • You specify SHRLEVEL NONE or FASTSWITCH NO.
  • You are reorganizing an index that has pending definition changes, but pending definition changes also exist on the associated table space.
• REORG PLUS terminates when any of the following conditions exist:
  • The values for the pending DSSIZE or SEGSIZE definitions are insufficient for the existing user data.
  • The pending DSSIZE value is greater than 4 GB, but the device is not EA-enabled.
  • An alter occurs during the reorganization that creates a pending definition change. In this case, you cannot restart the reorganization. Objects are left in their original status.
  • A pending alter exists on a limit key, and ORDER NO and UNLOAD RELOAD is in effect.

• REORG PLUS changes or ignores the following command installation options:

   

  Option	REORG PLUS action
  CPYRFAIL=TERM	Changes to COPYPEND
  FSFALLBACK	Ignores
  FSTHRESHOLD	Ignores
  INVALIDATECACHE=NO	Changes to YESREORG PLUS always update Db2 statistics after materializing pending definition changes.
  KEEPDICTIONARY YES	Changes to NO when any of the following definition changes are pending on the table space: An alter of the table space buffer pool or segment size A table alter that changes the limit key
  UPDATEDB2STATS NO	Changes to YESREORG PLUS always update Db2 statistics after materializing pending definition changes. This restriction also applies if you have a TERMEXIT user exit that changes UPDATEDB2STATS to NO.

Additional considerations

When multiple limit key change requests exist for a reorganization, REORG PLUS materializes only the first changes that it finds based on the following hierarchy:

• Pending limit key alters on the object
• ALTER statements in a DDLIN data set included in your JCL
• Limit key changes as a result of the REBALANCE option specified on your REORG command