Moving tables from multi-table table spaces to partition-by-growth table spaces using RC/Migrator

Products

• RC/Migrator for DB2 for z/OS

Issue/Introduction

Relocate tables into partition-by-growth tablespaces using RC/Migrator for Db2 for z/OS (RCM).

A database administrator (DBA) or application developer, you may want to move a table from one tablespace to another. Moving a table from a multi-table tablespace to a partition-by-growth (PBG) tablespace has the benefit of providing more space for the table. Manually moving tables can be very time consuming and subject to errors.

Resolution

This is the Tablespace structure to be used for this example:
TABLESPACE  TABLENAME           INDEXNAME
DSN8S12X
             CATALOG
                                 CATALOG_NAMEX
                                 I_DOCIDCATALOG
             CUSTOMER
                                 CUSTOMER_CIDX
                                 I_DOCIDCUSTOMER
             INVENTORY
                                 INVENTORY_PIDX
             PRODUCT

                                 I_DOCIDPRODUCT
                                 PROD_DETAIL_XMLIDX
                                 PROD_NAME_PIDX
                                 PROD_NAME_XMLIDX
             PRODUCTSUPPLIER
                                 PRODUCTSUPPLIER_PID_SI>
             PURCHASEORDER
                                 I_DOCIDPURCHASEORDER
                                 PURCHASEORDER_POIDX
             SUPPLIERS
                                 I_DOCIDSUPPLIERS
                                 SUPPLIERS_SIDX

Create an ALTERATION strategy and select the tables to be moved. For this example, two tables will be moved, INVENTORY  and PRODUCTSUPPLIER.        

-------------- RC/M Alteration Strategy Services -------------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Strategy ===> DSN8S12X        Description  ===>
Creator  ===> authid1         Share Option ===> U (U,Y,N,X,L) SSID ===> ssid
--------------------------------------------------------------------- authid1
Option =>    (A,C,T,D)          Object   =>    (SG,DB,TS,T,I,V,S,A,TG,SQ,PR,UF)
Name   => *                   > Creator  => *         > Where ==> N

CMD OBJECT TYPE NAME               CREATOR  PROCESS
_  TABLESPACE  DSN8S12E           authid1  CREATE
_  TABLE       INVENTORY          DSN81210 ALTER
_  TABLE       PRODUCTSUPPLIER    DSN81210 ALTER
******************************* BOTTOM OF DATA ********************************

Table INVENTORY has been edited in the strategy changing the existing tablespace to DSN8S12A.
Table PRODUCTSUPPLIER has been edited in the strategy changing the existing tablespace to DSN8S12E.

Tablespace DSN8S12E does not exist so it has been "CREATED" in the strategy above. It will be actually created in the analysis output before the tables are moved. It must have DEFINE NO. 
Tablespace DSN8S12A does already exist. It was previously created manually for this test to show how it is dealt with. Note that If the tablespace DSN8S12A was previously defined with DEFINE=Y, RC/Migrator generates a DROP and recreate of it with DEFINE=N before the move.

DB2 restrictions on the MOVE operation:

• Must be running at Db2 12 function level M508 (V12R1M508) or higher. 
• The databases must be the same.
• The CCSIDs must be the same.
• The LOGGED attributes must be the same.
• The buffer pool of the destination tablespace must have a page size that can accommodate the record length of the table being moved.
• The creator cannot be SYSIBM for either tablespace.
• The origin tablespace must have 0 parts and not reside in a work files database.
• The destination tablespace must be an empty PBG with MAXPARTITIONS 1.
• If the destination tablespace is defined with DEFINE=Y, it is dropped and recreated with DEFINE=N before the move.
• If a table is moved, all data in the table space except XML and LOB data are processed and all indexes are reorganized.

If these are not met then UNLOADS, DROP, CREATE and LOADS are generated instead of MOVES.

The Analysis output in this case generates:

1. CREATE TABLESPACE DSN8S12E
           IN DSN8D12X.........

2. Two ALTER statements.......

    ALTER TABLESPACE DSN8D12X.DSN8S12X
        MOVE TABLE DSN81210.PRODUCTSUPPLIER
            TO TABLESPACE DSN8D12X.DSN8S12E
    ;

.SYNC 10        'ALTER TABLESPACE DSN8D12X.DSN8S12X'

    ALTER TABLESPACE DSN8D12X.DSN8S12X
        MOVE TABLE DSN81210.INVENTORY
            TO TABLESPACE DSN8D12X.DSN8S12A
    ;

3. A REORG of the original tablespace DSN8D12X.DSN8S12X is generated to instantiate the moves of the tables

4. An IMAGE COPY and RUNSTATS of the original tablespace DSN8D12X.DSN8S12X

 

RC/Migrator COMPARE Strategies can also be used to produce the same type of generated analysis where either :

1. SOURCE DDL containing the required tablespace name changes

2. A SOURCE SSID where the tablespace name changes in the SOURCE SSID already exist

to be ported to the target SSID.

Additional Information

Moving tables from multi-table table spaces to partition-by-growth table spaces