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

FL 500 When you change the partitioning scheme of a table from partition-by-growth (PBG) to partition-by-range (PBR) by issuing ALTER TABLE ALTER PARTITIONING TO PARTITION BY RANGE, you can materialize those changes by running REORG. Use the following guidance when running REORG in this situation:

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

REORG uses the new limit key values that were specified in the ALTER statement for each converted PBR partition.

KEEPDICTIONARY

REORG always constructs new compression dictionaries for each partition of the converted PBR table space. Because the data records from each partition are used to build the compression dictionary, partitions that are empty after the conversion to PBR might not have a compression dictionary. This situation is different from PBG table spaces, where REORG propagates the same compression dictionary to all target 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 PBR definition.

When the resulting PBR table space has fewer partitions than the original PBG table space, the extra partitions are deleted and the SYSCOPY and SYSLGRNX records for these extra partitions are also deleted.