Reorganization with pending definition changes

A REORG utility operation that materializes pending definition changes is equivalent to a data definition change that happens during an online transaction. REORG requires the same type of serialization that any data definition change requires in addition to the existing draining serialization it performs on the target object. Therefore, to avoid possible application outages, try to run REORG at a time when the data is not heavily accessed, which allows for the data definition change to be materialized.

To materialize a pending definition change by running REORG, specify SHRLEVEL REFERENCE or CHANGE and do not specify FASTSWITCH NO.

A REORG operation that materializes definition changes enables you to alter certain objects without significantly impacting availability. The data is unavailable for only a short time. This short time of unavailability happens during any REORG SHRLEVEL CHANGE operation.

However, a REORG operation that materializes pending definition changes does have a cost. REORG begins serialization with the concurrent application during the last log iteration in the LOG phase and changes schema definitions during the SWITCH phase. During this time, plans, packages, and the dynamic statement cache are quiesced and invalidated, and statistics and dynamic SQL are blocked. As a result, the DRAIN ALL behavior that REORG performs when it does not materialize pending definition changes is not sufficient. For example, assume that an agent sees a segmented (non-UTS) table space and claims and waits for REORG to release the drain. At the same time, REORG materializes the pending definition change and changes the table space from segmented to partition-by-growth. If REORG does not block the agent before its claim, after REORG releases the drain, the agent continues to access the table space with the attributes that it had before REORG ran. This situation can result in unpredictable behaviors. Application lock timeout errors are possible, because REORG was unable to acquire the locks on either the SYSIBM.SYSTABLESPACE record or on the plans or packages. The IRLM lock timeout value applies for these locks. REORG can hold these locks for longer than the IRLMRWT threshold because it must acquire multiple locks. (When REORG is not materializing definition changes, it needs only a single drain lock.) The value of the DRAIN_WAIT utility option does not apply to these lock timeouts.

Materialization of pending column alterations

The only way to materialize pending column alterations is to run REORG TABLESPACE with the SHRLEVEL REFERENCE or SHRLEVEL CHANGE option on the entire table space.

When REORG is run with the DISCARD option, discarded rows have the materialized format, with the column attributes that were specified in the pending alterations.

Materialization of MOVE TABLE operations

When you run REORG TABLESPACE to materialize one or more MOVE TABLE operations (an ALTER TABLESPACE statement with the MOVE TABLE clause), only those packages that are dependent on the moved tables are invalidated. Additionally, REORG does not collect any statistics for either the source or target table space. Existing statistics for affected tables and indexes remain unchanged.

When running REORG to materialize MOVE TABLE operations, use following guidance and restrictions when specifying REORG options:

If you specify COPYDDN or RECOVERYDDN, you must specify a template with the &TS. or &SN. variables and without the STACK YES option. This specification ensures that the data sets are dynamically allocated for multiple table spaces at run time.
If you specify FLASHCOPY YES, you must ensure that the volumes that are defined for the source and target table spaces are set up for FlashCopy® execution. If a FlashCopy image copy is the only recovery base requested and its creation fails during the REORG SWITCH phase for any object, the entire REORG fails without materializing any pending definition changes.
You cannot specify AUX YES.
KEEPDICTIONARY is ignored. REORG always constructs new compression dictionaries for both the source and target table spaces. If sufficient data is not available to build a new compression dictionary for any of the table spaces, those table spaces will not have compression dictionaries when REORG completes. If sufficient data is not available to build a compression dictionary for the moved table, the data will remain uncompressed until the next dictionary build.
STATISTICS is ignored.
If you specify DISCARD, the generated LOAD utility statements are based on the source table space. (These statements are generated in the data set that is specified by PUNCHDDN.) Before you run these LOAD statements, you must modify the statements for any tables that were moved to partition-by-growth table spaces.

Any specified REORG options apply to all source and target table spaces. For example, if you specify PREFORMAT, both the source and target table spaces that are involved in the move are preformatted. If the materializing REORG is enforcing reordered row format or extended page format, the utility enforces that attribute for all base table spaces that are involved in the REORG operation. The only exception is when the target table spaces remain undefined (with the DEFINE NO attribute). In this case, the definition of the target table space is not modified other than its association with the moved table.

If the table that is moved to a new table space remains empty after the REORG operation, the target table space also remains undefined (DEFINE NO) when REORG completes successfully.

If REORG fails before committing changes in the SWITCH phase, the target partition-by-growth table spaces remain with the DEFINE NO attribute but without data definition changes. The shadow data sets that were allocated for these undefined objects are deleted by REORG or by the TERM UTIL command.

Materialization of partitioning scheme changes

When you change the partitioning scheme of a table by issuing an ALTER TABLE statement with the ALTER PARTITIONING TO clause, you can materialize those changes by running REORG. The utility can materialize either of the following partitioning scheme changes:

FL 500 a change from partition-by-growth (PBG) to partition-by-range (PBR)
FL 507 a change from PBR to PBG

REORG can materialize other pending changes in the same operation depending on the change. For example, REORG can materialize a change from PBR to PBG and a subsequent change from PBG to PBR in the same REORG operation. (Note a change from PBG to PBR cannot be combined with a subsequent change from PBR to PBG; the initial change to PBR must be materialized first.) For details about changes that are restricted when pending changes exist, see Restrictions for pending data definition changes.

Use the following guidance when running REORG to materialize changes to the partitioning scheme:

Run REORG on the entire table space. You can run a partition-level REORG operation after the ALTER TABLE statement, but that REORG operation will not modify the table space type definition.
Take an inline image copy. This copy can be either a FlashCopy image copy or partition-level sequential image copies or both.
The following REORG utility options are ignored:

REBALANCE

When materializing a change to PBR, REORG uses the new limit key values that were specified in the ALTER statement for each converted PBR partition. When materializing a change to PBG, REBALANCE is not applicable.

KEEPDICTIONARY

REORG always constructs new compression dictionaries for each partition of the converted table space.
If the table space is converted to PBR, data records from each partition are used to build the compression dictionary. Therefore, partitions that are empty after the conversion to PBR might not have a compression dictionary.

If the table space is converted to PBG, REORG performs a single dictionary build during the data unload phase and propagates this same compression dictionary to all defined PBG partitions.

SORTDATA NO

The data records are always sorted based on the partitioning scheme, clustering order, or both.
You can optionally specify DISCARD criteria to discard qualifying records during the UNLOAD phase of the REORG operation.
You can optionally specify a STATISTICS clause. If you do not explicitly specify STATISTICS, REORG gathers inline statistics with TABLE(ALL) INDEX(ALL) on the entire table space. The utility updates the catalog statistics based on the new partitioning scheme.
When the resulting table space has fewer partitions than the original table space, the extra data partitions are deleted and the SYSCOPY and SYSLGRNX records for those extra partitions are also deleted.
When changing the partitioning scheme to PBG and the table space contains a table defined with the DATA CAPTURE CHANGES attribute, REORG ensures that the number of defined partitions after the REORG operation is not less than the number of PBR partitions before the conversion. The trailing PBG partitions can be empty if all of the data records fit in the earlier partitions.