ALTER TABLESPACE MOVE TABLE support

(SPE2104)

In Db2 Version 12 function level 508, IBM introduced ALTER TABLESPACE MOVE TABLE syntax to facilitate moving individual tables from multi-table non-UTS table spaces (simple or segmented) to UTS PBG table spaces. The REORG that effects the ALTER syntax establishes a new base recoverability point so that a point-in-time recovery before that REORG is not possible. 

Log Master provides the MOVE TABLE option to seamlessly process reasonable log activity before tables are moved using the ALTER TABLESPACE MOVE TABLE syntax. The option is particularly useful when working with an ONGOING PROCESS because it enables processing to continue without interruption during the movement.. 

If you specify MOVE TABLE NO, log records associated with a moved table before the effecting REORG are not processed.

Processing with MOVE TABLE YES specified incurs overhead because additional information about the movement is retrieved, analyzed, and applied to log records, as necessary.

The rest of this section describes processing with MOVE TABLE YES specified, unless otherwise indicated. 

Before moving a table

Tables contained in a non-UTS source table space are too critical to be dropped and re-created, so ALTER TABLESPACE MOVE TABLE syntax ensures that the processing before and immediately after the effecting REORG is as clean as possible.  

For example, prior to the move, avoid:

• Running LOAD LOG YES utilities.
• Versioning a table or otherwise altering the source table or table space.
• Performing activities that might create auto-compression dictionaries.
• Executing a mass delete on the table.

Although Log Master can handle these activities, their complexity can cause problems.

For information about dealing with objects involved in movement, see Filtering Considerations. Also, if you are generating logical log output, review any custom post-processing that could be affected by moving a table from one table space to another. Review Log Master processing that could be affected by ALTER TABLESPACE MOVE TABLE, paying particular attention to any ONGOING, REPOS UPDATE, and OVERTIME processes. 

After moving a table

After the REORG that effects a move, there is still catalog information associated with the source table space that is relevant to the moved tables. Therefore, after a move, do not perform the following actions until after the log and SYSCOPY information is no longer required::

• Drop the source table space, even if there are no tables remaining in it.
• Modify SYSCOPY information for the source table space that will be needed to process the logs of the moved tables.

After the log and SYSCOPY information is no longer required, which means there is no need to scan log prior to the move, then these caveats are removed. These calculations should include the log required for compression dictionaries and completion processing using image copies before the FROM point of a LOGSCAN.

Complete any required Log Master processing adjustments as determined during Before moving a table analysis including any required filtering changes.

Executing Log Master with moved tables and MOVE TABLE YES

After the REORG that effects the movement, all log records associated with a moved table are associated with the target space regardless of the table space, association when the log record was created or the log range was scanned, even if that range is exclusively before the movement. Therefore, Log Master outputs (for example, logical logs and reports) reflect the target table space for moved tables. Because the table name does not change, any generated DML SQL (INSERT, UPDATE, DELETE) is unaffected. 

Log Master generates the following messages when the move table processing is detected during SYSCOPY analysis:

• The following message indicates that you have run ALTER TABLESPACE MOVE TABLE. Registered on the source table space.

  BMC397063I ALTER (MOVE TABLE) RECORD FOUND IN SYSCOPY FOR srcTableSpace DSNUM n AT RBA X’nnnnnnnnnnnnnnnnnnnn'

• The following message indicates that you have run the REORG that effected the ALTER TABLESPACE MOVE TABLE. Registered on the source table space.

  BMC397063I REORG (MOVE TABLE SOURCE) RECORD FOUND IN SYSCOPY FOR srcTableSpace DSNUM n AT RBA X’nnnnnnnnnnnnnnnnnnnn' 

• The following message indicates that you have run the REORG that effected the ALTER TABLESPACE MOVE TABLE. Registered on the target table space.

  BMC397063I REORG (MOVE TABLE FROM srcTableSpace) RECORD FOUND IN SYSCOPY FOR trgTableSpace DSNUM n AT RBA X’nnnnnnnnnnnnnnnnnnnn' 

Overtime mode processing

When running in overtime mode, Log Master provides limited support for processing log activity for a table that has been moved and then dropped.

Before running an overtime log scan for a target space and table that has been moved and then dropped:

• Delete overtime entries in the Log Master repository table for the moved table and source space. If you do not, internal errors and abends might occur.
• Make sure that information in the repository for the moved table and source space is current and complete.

Filtering considerations

Filtering uses the current schema of objects in the Db2 catalog as its basis, so following the REORG that effects a movement, all log records associated with a moved table are associated with the target space, regardless of the table space association when the log record was created.

Some filters, that is WHERE clauses, which cite source table spaces involved in movement. You might need to adjust these filters to reflect one or more target table spaces. Alternatively, adjust the filter to reflect table names.

Example

Segmented table space TSS in database DB contains tables USER.TB1 and USER.TB2.

A row is inserted into USER.TB1 at I1.

A row is inserted into USER.TB2 at I2.

Before the move, LOGSCANs are run with various filters for a range that includes I1 and I2:

Then, USER.TB2 is moved to new UTS table space DB.TST and USER.TB1 remains in DB.TSS. 

After the move, the same LOGSCANs run with the same set of filters: