Moving tables from multi-table table spaces to UTS

You can use Db2 Admin Tool to move tables from multi-table simple or segmented table spaces, which are deprecated, to partition-by-growth universal table spaces (UTS). Moving data to supported table spaces can help you take advantage of new Db2 functionality that operates only on UTS.

To move tables from multi-table table spaces to UTS:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, specify one of the following options and any filtering criteria at the bottom of the panel, and press Enter:

   S

   Specify this option if you want to select the tables to move based on table spaces.

   D

   Specify this option if you want to select the tables to move based on databases.

   Tip: If you specify S, also specify the following filtering criteria to limit the results to only those table spaces that contain more than one table:

        
   And/or other selection criteria (option xC shows you columns for option x)     
   Column  . . . NTABLES             > Operator  . . >      Value  . . 1

3. On the Table Spaces (ADB21S) panel or the Databases (ADB21D) panel, specify either the MOVETB line command or primary command.
   Use the line command to select a specific table space or database. Use the primary command to select all objects listed. When you specify the primary command, any eligible multi-table table spaces are selected.
4. On the DB2 Admin Move Table Options (ADB2MVT1) panel, specify options for the move operation and press Enter:

    ADB2MVT1                    DB2 Admin Move Table Options                  11:23
   Option ===>                                                                    
                                                                                  
   Enter options for moving the tables:                                           
                                                                                  
   New table space options:                                                       
     Prefix . . . . . . . . TBNAME(7) (Constant, TSNAME(n), TBNAME(n))            
     Suffix . . . . . . . .           (Optional: A - Alphanum  N - Numeric)       
     16KB buffer pool . . .           (Optional: Default - Current BP)            
     Storage group  . . . .           (Optional: Default - Current STOGROUP)      
     Locksize . . . . . . .           (Optional: Default - Current LOCKSIZE) 
     Max Partitions . . . .           (Optional: 1 - 4096, default 1)                
   Other options:                                                                 
     Tables per REORG . . . 100       (Optional: 1-500, default 20)              
     Prefix for data sets .           (Optional: Default TS5816)                  
     Work list name . . . . MOVETB    (Optional: Default MOVETB)                  
     DSN for generated job. TS5816.TEMP.TEST3                                     
                                      (Optional: Default TS5816.RS22.SPFTEMP3.CNTL
     Generate RUNSTATS  . .           (Optional: Default No)
     Generate REBINDs . . .           (Optional: Default No)                      
                                                                                  
   There are 3 TBs in 1 TSes. Max TBs/TS: 3. Max TBs/DB: 3                                    

   You must specify a prefix for the names of the new table spaces to which the tables are to be moved. You can either specify a constant value or a subset of characters from the database, table space, or table name. All other values are optional and are described in the help.

   Tip: REORG operations are used to materialize pending MOVE TABLE operations. When you specify the number of tables to include in a REORG, consider that the number of tables and size of these tables can affect the elapsed time of REORG and the storage that REORG requires for work files.
5. On the Move Tables to PBGs (ADB2MVT2) panel, confirm that the tables that you want to move are listed:

   ADB2MVT2  ------------------- DC1A Move Tables to PBGs ----- Row 1 to 11 of 47 
                                                                                  
   Commands: NEXT  BP  UO  TU  PACKAGES  CHKNTS  CHKPDC                           
   Line commands:  T - Table  S - Tablespace  D - Database  K - Packages          
    ?  Show all line commands                                                     
                                                   New PBG:                       
      Table    Table                      Table    Table    Buffer  Storage  L Err     
   S  Schema   Name              Database Space    Space    Pool    Group    S Stat    
      *        *                 *        *        *        *       *        * *    
   -- -------- ----------------- -------- -------- -------- ------- -------- - ----    
      AUOVR    SYSCOLAUTH        AUOVRDB  AUODBASE AUODB01  BP8K0   SYSDEFLT T    
      AUOVR    SYSCOLUMNS        AUOVRDB  AUODBASE AUODB02  BP8K0   SYSDEFLT T    
      AUOVR    SYSFIELDS         AUOVRDB  AUODBASE AUODB03  BP8K0   SYSDEFLT T    
      AUOVR    SYSFOREIGNKEYS    AUOVRDB  AUODBASE AUODB04  BP8K0   SYSDEFLT T    
      AUOVR    SYSINDEXES        AUOVRDB  AUODBASE AUODB05  BP8K0   SYSDEFLT T    
      AUOVR    SYSINDEXPART      AUOVRDB  AUODBASE AUODB06  BP8K0   SYSDEFLT T    
      AUOVR    SYSKEYS           AUOVRDB  AUODBASE AUODB07  BP8K0   SYSDEFLT T    
      AUOVR    SYSRELS           AUOVRDB  AUODBASE AUODB08  BP8K0   SYSDEFLT T    
      AUOVR    SYSSYNONYMS       AUOVRDB  AUODBASE AUODB09  BP8K0   SYSDEFLT T    
      AUOVR    SYSTABAUTH        AUOVRDB  AUODBASE AUODB10  BP8K0   SYSDEFLT T    
      AUOVR    SYSTABLEPART      AUOVRDB  AUODBASE AUODB11  BP8K0   SYSDEFLT T    

6. To check whether any of the new table spaces already exist, issue the CHKNTS command, and press Enter.

   If any of the new table spaces already exist, a list of those table space names is displayed. These existing table spaces will cause the generated move job to fail, because that job will attempt to create these table spaces. Therefore, in the generated job, the operations for these table spaces will be placed in comments and the tables will not be moved. To resolve this issue and ensure that the tables are moved, exit back to the Move Tables to PBGs (ADB2MVT2) panel and specify a new name in the New PBG: Table Space column for those table spaces that already exist. Then, issue CHKNTS again to clear the E status in the Err Stat column.

   The Err Stat column can have the following values:

   D

   The new table space name is a duplicate of a new table space in the list. To resolve this error, enter a different table space name.

   P

   The old table space already has a pending change that prevents additional ALTER operations. To resolve this error, first materialize or delete the existing pending change.

   E

   The new table space already exists in the Db2 catalog. To resolve this error, enter a different table space name.

7. To check whether any of the existing table spaces have pending changes, issue the CHKPDC command, and press Enter.

   If any pending changes exist, a list of the affected table spaces is displayed. Those pending changes will also cause the generated move job to fail, because Db2 does not allow you to move tables from a table space with pending changes. Therefore, in the generated job, the operations for these table spaces will be placed in comments and the tables will not be moved. To resolve this issue and ensure that the tables are moved, exit back to the Move Tables to PBGs (ADB2MVT2) panel and take the following actions for each table space with a pending change:

   1. Specify the S line command next to any row that includes the table space with pending changes.
   2. On the Table Spaces (ADB21S) panel, specify the PDC line command to view the pending changes.
   3. On the DB2 Pending Definition Changes (ADBPPDC) panel, evaluate the changes and either drop them or run REORG on the table space.

   Then, issue CHKPDC again to clear the P status in the Err Stat column.
8. Optional: Modify the REORG options by using the UO command.
9. Issue the NEXT command, and press Enter.
   The generated JCL job is displayed. This job performs the following actions:

   • Creates the new tables spaces
   • Runs the ALTER TABLESPACE statements with the MOVE TABLE clause and the required REORG operations to materialize these changes
   • Performs any requested rebind operations

   The number of tables that are moved and the number of table moves that are materialized in each REORG are listed at the top of the job, before the CREATE TABLESPACE statements.
10. Submit the generated JCL to move the tables.
    Because this job runs ADBTEP2, if it fails, you can resubmit it and ADBTEP2 will restart it.