
Moving tables from multi-table table spaces to partition-by-growth table spaces
You can move tables from multi-table simple or multi-table segmented (non-UTS) table spaces, which are deprecated, to partition-by-growth universal table spaces (UTS).
Before you begin
Use the ALTER TABLESPACE statement with the MOVE TABLE option to move tables from a source multi-table table space to target partition-by-growth table spaces. 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. For more information, see Pending data definition changes.
- Identify tables to move
- Use the following query to identify all multi-table table spaces and the number of tables in each table space:
Then use the following query to identify all tables in a multi-table table space:SELECT CURRENT SERVER AS DB2LOC, DBNAME, DBID, NAME AS TSNAME, NTABLES FROM SYSIBM.SYSTABLESPACE WHERE NTABLES > 1 ORDER BY DBNAME, TSNAME;
SELECT CURRENT SERVER AS DB2LOC, TSP.DBNAME, TSP.DBID, TSP.NAME as TSNAME, TSP.NTABLES, TAB.CREATOR AS TBCREATOR, TAB.NAME AS TBNAME, TAB.TYPE AS TBTYPE FROM SYSIBM.SYSTABLESPACE AS TSP, SYSIBM.SYSTABLES AS TAB WHERE TSP.NTABLES > 1 AND TSP.DBNAME = TAB.DBNAME AND TSP.NAME = TAB.TSNAME ORDER BY TSP.DBNAME, TSP.NAME, TAB.CREATOR, TAB.NAME;
- Create the target table space, if needed
- The target table space must already exist as a partition-by-growth UTS in the same database as the source multi-table 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
Remember: When you create a new table space, consider the possible impact on the number of open data sets (the DSMAX subsystem parameter), the DBD size, the EDM pool size, and so forth.Recommendation: If the target table space is defined with MAXPARTITIONS 1, it cannot grow a new partition during the materialization of a MOVE TABLE operation. Therefore, if you create a new target table space, choose a maximum partition size (DSSIZE) value that can contain all of the data in the tables that are being moved. Assuming that you create the target table space with the same space attributes (such as page size and SEGSIZE) as the source table space, you can choose one of the following recommended DSSIZE values:- 64 GB, which is the maximum size for the source table space. This DSSIZE value ensures that the target table space can contain all of the table data. However, consider the following implications of selecting a DSSIZE of 64 GB:
- The DFSMS extended addressability function must be enabled to use a DSSIZE value greater than 4 GB.
- A DSSIZE value of 64 GB limits the maximum number of partitions to which the table space can grow.
- Altering the DSSIZE value later is a pending definition change that requires running the REORG utility to materialize the entire table space.
- A DSSIZE value that is closer to the minimum size that is needed to contain all of the table data. If you collected statistics for the tables, use the following formula to calculate the amount of space that is required for the table data:
In this formula, table-size-in-KB indicates the amount of space occupied by the data in the tables that are being moved, in kilobytes. Choose the closest DSSIZE value that is greater than table-size-in-KB. The DSSIZE value must be a power-of-two value that is within the range 1 GB - 256 GB.SYSTABLES.NPAGESF × SYSTABLESPACE.PGSIZE = table-size-in-KB
- Determine the number of moved tables to materialize in a single REORG job
- 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. To help you decide how many tables to move and materialize in a single REORG, consider the following issues:
- Generally, the total processing time for a data definition statement that is a pending definition change for a table space includes the following constituent processing times:
- The processing time for that statement
- The time that it takes to process unmaterialized pending definition changes for the table space. All unmaterialized pending definition changes for the table space are processed, regardless of whether the changes were executed in a single unit of work or across multiple units of work.
Pending definition changes are reapplied during both the REORG UTILINIT phase and the REORG SWITCH phase to become materialized. You can use the following formula to calculate the time that it takes to reprocess all of the pending statements during the REORG:(n + 1) × t
where:2 × (n + 1) × t
- n
- Is the number of unmaterialized pending MOVE TABLE operations
- t
- Is the processing time for a single ALTER TABLESPACE MOVE TABLE statement
- Compared to a REORG on the non-partitioned source table space, this REORG creates one additional shadow data set for each moved table. Generally, moving a large number of tables in a single REORG results in the creation of more shadow data sets and an increase in the duration of the SWITCH phase.
- Regarding the total amount of sort space and data that is processed, this REORG is comparable to a REORG on the non-partitioned source table space that does not materialize any pending changes. The number of tables that are moved does not affect the total amount of sort space and data that is processed. If a table is moved, all data in the table space except XML and LOB data are processed and all indexes are reorganized.
- For each table that is moved during a REORG materialization, a sequential inline image copy is allocated for its respective target table space, which uses virtual storage below the 16-MB line. The more tables that are moved in a single REORG, the more below-the-line virtual storage that is used by the sequential image copy data sets during the REORG. For a REORG that creates only FlashCopy inline image copies (FCIC) without creating sequential inline image copies, this below-the-line memory consumption does not apply. The FCIC is created at the end of the SWITCH phase and does not affect mainline REORG executions during earlier phases.
- For general considerations for running a REORG to materialize pending definition changes, see Reorganization with pending definition changes.
- Generally, the total processing time for a data definition statement that is a pending definition change for a table space includes the following constituent processing times:
- Plan schema changes ahead of time
-
After you issue an ALTER TABLESPACE MOVE TABLE statement and before you run the REORG utility to materialize it, most data definition changes are restricted against any table in the source table space. If a data definition statement references a table in a table space that has an unmaterialized pending MOVE TABLE operation, all immediate definition changes and a subset of pending definition changes are restricted. For more information, see Restrictions for pending data definition changes.
If any tables in the table space need schema definition changes that can be executed as immediate changes, consider executing these changes before you move the tables.
- Plan rebind operations, if needed
- The REORG that materializes the ALTER TABLESPACE MOVE TABLE statement invalidates packages that depend on the moved tables. Before you move the tables, you can identify packages that will become invalid and prepare necessary rebind operations ahead of time.For a query that identifies packages that will be invalidated by a certain change, see Identifying invalidated packages. To identify packages that will be invalidated by materialization of a MOVE TABLE operation, run the query using the following values:
Table 1. Values to use in the query to identify invalidated packages from materialization of a MOVE TABLE operation Variable in the query Value object_qualifier The schema of the table that is being moved object_name The name of the table that is being moved object_type Use one of the following values: - T
- Normal table
- M
- Materialized query table
For more information about currently committed data access, see Accessing currently committed data to avoid lock contention.
Procedure
- Altering the target table space so that its attributes become invalid for a MOVE TABLE operation. In this case, use the ALTER TABLESPACE statement to alter any invalid attributes to be valid again.
- Dropping and re-creating the target table space, regardless of whether the table space attributes are valid. In this case, complete the following steps to re-execute the MOVE TABLE operation:
- Issue the ALTER TABLESPACE statement with the DROP PENDING CHANGES option to drop all pending definition changes for the target table space.
- Re-create the target table space with the desired attributes.
- Issue the ALTER TABLESPACE MOVE TABLE statement again.
Results
If the moved tables remain empty after materialization, the REORG leaves the target table space as DEFINE NO (that is, no VSAM data sets defined) but it updates the metadata definition of the target table space to reflect the linkage to the moved tables. REORG does not insert any SYSCOPY records for target table spaces that remain as DEFINE NO after materialization of pending MOVE TABLE operations. These target table spaces will not be involved in any future recovery situations because their VSAM data sets do not exist.
What to do next
- Run a stand-alone RUNSTATS job.
- When you rebind the invalidated packages, issue the REBIND command with the APREUSE(WARN) bind option to minimize access path change. If enabled, an autobind also issues APREUSE(WARN).
After the MOVE TABLE operations are materialized, you can increase the MAXPARTITIONS value of the target table space to any valid value as an immediate change by issuing an ALTER TABLESPACE statement.
If you create partition-level image copies of the target table spaces for backup and recovery, and you use a LISTDEF list with the PARTLEVEL option for the COPY utility, you must also use a LISTDEF list with the PARTLEVEL option for the RECOVER utility.
