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.
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:
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.