Db2 for z/OS and its ecosystem

 View Only

Migrate data from multi-table deprecated table spaces to UTS in function level 508

By Jennie Chang posted Fri October 16, 2020 04:54 PM


By Jennie Chang, Paul McWilliams, and Regina Liu. 

You can activate function level 508 in Db2 12 for z/OS, after you apply the PTF for APAR PH29392. Function level 508 introduces the capability to move data from multi-table simple or multi-table segmented (non-UTS) table spaces, which are deprecated, to universal table spaces (UTS). 

Starting in function level 508, you can now use the new MOVE TABLE option in an ALTER TABLESPACE statement to move tables from a source multi-table table space to target partition-by-growth UTSs. This new capability provides an efficient way to migrate existing data from deprecated table spaces to supported table spaces to take advantage of new functionality. 

If the data sets of the source table space are already created, a MOVE TABLE operation becomes a pending definition change that must be materialized by running the REORG utility on the source table space. You can move only one table per ALTER TABLESPACE MOVE TABLE statement, but you can materialize multiple pending MOVE TABLE operations in a single REORG. 

Before moving a table, the target table space must already exist as a partition-by-growth UTS in the same database as the source table space and be defined with the following attributes: 

  • DEFINE NO attribute 
  • MAXPARTITIONS value of 1 
  • LOGGED or NOT LOGGED attribute that matches the source table space 
  • CCSID values that match the source table space 

One option for using this capability is to first use the ALTER TABLESPACE MOVE TABLE statement to move all tables from the source table space to partition-by-growth UTSs:

Then run the REORG utility on the source table space to materialize all pending definition changes, including moving the tables:

After the MOVE TABLE operations have been materialized, drop the source table space by issuing the DROP statement.

An alternative option is to move all but one table from the source table space to partition-by-growth table spaces, and then convert the source table space into a partition-by-growth table space by issuing the ALTER TABLESPACE statement with MAXPARTITIONS 1. This conversion is a pending definition change that can be materialized with any previous pending definition changes for the source table space, including the pending MOVE TABLE operations. 

For more details on how to prepare for and issue MOVE TABLE operations, see Moving tables from multi-table table spaces to partition-by-growth table spaces. 

After tables are moved, historical data from the tables can be accessed from image copies of the source table space that were created before materialization of the MOVE TABLE operations. For information on creating image copies of the source table space and accessing table data from those image copies, see Accessing historical data from moved tables by using image copies. 


Jennie Chang and Paul McWilliams are information developers for Db2 for z/OS documentation. Regina Liu is a Senior Software Engineer for Db2 for z/OS RDS development.

Sign in and subscribe to always get the latest news about Db2 for z/OS from the IBM lab: http://ibm.biz/db2znews-subscribe