Converting a nonpartitioned table space to an index-controlled partitioned table space

In this example, a base table exists in the nonpartitioned table space. This task also provides instructions for creating a clustering index for an existing base table and creating a partitioning index.

In this task, you perform the following subtasks:

1. Display the list of table space partitions.
2. Specify the additional partitions.
3. Create the clustering, partitioning index for the base table.

To display the list of table space partitions

1. On the BMC AMI Change Manager for Db2 Main Menu, select WORKID, and press Enter.
2. On the WORKID Action Menu, type the name of the WORKID or type a wildcard pattern to display a list of work IDs. Then, select Specify Db2 definitions and press Enter.
3. On the Object Specification panel, specify the name of a nonpartitioned table space (or use wildcard characters). Then, press Enter.
4. On the Mixed List panel, type PT in the Act column adjacent to the nonpartitioned table space that you want to modify.

To specify the additional partitions

1. On the Tablespace Parts List panel, specify whether the table space will be a regular partitioned table space or a large partitioned table space.
2. In the Dssize field, specify the maximum size for each partition or data set.
3. In the Segsize field, type 0.
4. For VCAT-defined table spaces, specify whether the table space should use a variable control interval size (CISIZE):
   • Type Y to use a CISIZE that is equal to the page size of the table space.
   • Type N to use a CISIZE of 4 KB, regardless of the page size.
   • Leave the Variable CISIZE field blank to have the product use the value of the Db2 subsystem parameter DSNZPARM to create the VSAM data sets.

5.  Type I in the Act column adjacent to the existing partition. Then, press Enter.A blank line is inserted after the line on which you typed I.

   Warning

   Important

   You can also type L in the Act column to duplicate an existing partition definition. You can then edit the attributes of the partition by typing over them or by typing E in the ACT column adjacent to the partition that you want to modify.

6.  Type the name of the Stogroup or VCAT and specify values for the following fields:

   
   

   Field	Description
   Volume	The first volume upon which a VCAT partition resides
   PriQty	The primary quantity for the partition
   SecQty	The secondary quantity for the partition
   Aloc Unit	The allocation unit for the partition
   Free Page	The page increment for inserting a free page
   Pct Free	The percentage of free space to leave on each nonleaf page
   Gbp Cach	The group buffer pool cache setting for the partition
   Comp	The compression attribute for the partition
   SMS Storclass	For VCAT partitions, the SMS storage class for the partition
   SMS Dataclass	For VCAT partitions, the SMS data class for the partition
   SMS Mgmtclass	For VCAT partitions, the SMS management class for the partition
   Track Mod	The indicator for tracking page modifications in the space map pages

7. Repeat Step 4 and Step 5 for each partition that you want to create.
8. Press END.

To create the clustering, partitioning index for the base table

Before you create the clustering, partitioning index for the base table, determine if such an index already exists for the table. You can have only one clustering, partitioning index for the table. If a clustering, partitioning index exists, perform one of the following actions:

1. On the Mixed List panel, type CIX in the Act column adjacent to the base table. Then, press Enter.

2. On the Create Index panel, specify the attributes for the index.

   Success

   Tip

   Some object names might be too long to be displayed on a panel. To view a long object name, position the cursor on the object name and press the ZOOM (F4) key.

   1. Specify the owner of the index.
   2. Specify the name of the index.
   3. Type Y to indicate that the index is a clustering index.
   4. Type S to select Keys and TB Cols.

   5. Press Enter.The Index Key Mixed List panel is displayed, as shown in the following figure.

      ALUSIXKX R   ---------------------- Index Key Mixed List ----------------------
      Command ===>                                                    Scroll. .
      
      WORKID  . . . . : RDACRJ.ALT01                            Commands: CANCEL END
      
      (C)opy columns from the list under TABLE COLUMNS up to make a list under
      INDEX KEY.  Use (A)fter/(B)efore to position columns under INDEX KEY.
                                                                Objects 1 to 3 of 3
                                                                       More:
      Act         Colname                          Column Type      UDT Length Nl FP
      ************************************ TOP **************************************
      A    INDEX KEY . . . : RDACRJ     IX_PART_02
           TABLE COLUMNS . : RDACRJ     T_L13SS
                  ROW_ID                           ROWID                        N  N  
      C           OTRACOL                          CHAR                      5  N  N  
                  CLOB                             CLOB                         Y  N  
      *********************************** BOTTOM ************************************

3. Specify the index key.

   Success

   Tip

   Some object names might be too long to be displayed on a panel. To view a long object name, position the cursor on the object name and press the ZOOM ( F4) key.

   1. Type C in the Act column adjacent to the column name.
   2. Type A in the Act column adjacent to the Index Key.

   3. Press Enter.The Index Key Mixed List panel is displayed as shown in the following figure. The index key column is displayed below the index key.

      ALUSIXKX R   ---------------------- Index Key Mixed List ----------------------
      Command ===>                                                    Scroll. .
      
      WORKID  . . . . : RDACRJ.ALT01                            Commands: CANCEL END
      
      (C)opy columns from the list under TABLE COLUMNS up to make a list under
      INDEX KEY.  Use (A)fter/(B)efore to position columns under INDEX KEY.
                                                                Objects 1 to 4 of 4
                                                                       More:
      Act         Colname                          Column Type      UDT Length Nl FP
      ************************************ TOP **************************************
           INDEX KEY . . . : RDACRJ     IX_PART_02
          L   1   OTRACOL                          A
           TABLE COLUMNS . : RDACRJ     T_L13SS
                  ROW_ID                           ROWID                        N  N  
          L       OTRACOL                          CHAR                      5  N  N  
                  CLOB                             CLOB                         Y  N  
      *********************************** BOTTOM ************************************
   4. Press END.
4. On the Create Index panel, type S to select Partitions.
5. On the Index Parts List panel, select Partitioned for the partitioning status.

6. Type I in the Act column adjacent to a partition. Then, press Enter.A blank line is inserted after the line on which you typed I.

   Warning

   Important

   You can also type L in the Act column to duplicate an existing partition definition. You can then edit the attributes of the partition by typing over them or by typing E in the ACT column adjacent to the partition that you want to modify.

7. Type the name of the Stogroup or VCAT and specify values for the following fields:

   Field	Description
   Volume	The first volume upon which a VCAT partition resides
   PriQty	The primary quantity for the partition
   SecQty	The secondary quantity for the partition
   Aloc Unit	The allocation unit for the partition
   Free Page	The page increment for inserting a free page
   Pct Free	The percentage of free space to leave in each nonleaf page
   Gbp Cach	The group buffer pool cache setting for the partition
   SMS Storclass	For VCAT partitions, the SMS storage class for the partition
   SMS Dataclass	For VCAT partitions, the SMS data class for the partition
   SMS Mgmtclass	For VCAT partitions, the SMS management class for the partition
   Limitkey	The limit key value for the partition

   Warning

   Important

   In the Limitkey field, which is displayed by typing the LK action code in the Act column, you must specify nonprintable and nonviewable characters, such as null and control characters, as external hexadecimal strings. For more information, see Nonprintable-or-nonviewable-characters.

8. Press END.The Create Index panel is displayed. The panel indicates the number of partitions.

   Warning

   Note

   Now that you have increased the number of partitions in the table space, you need to create auxiliary objects for the base table for each of the new partitions. If the base table in the table space already contains auxiliary objects, we recommend that you drop the existing auxiliary objects on the Auxiliary Objects List panel and then create the new auxiliary objects. (If you LIKEd the existing objects, you would need to go through the time-consuming and error-prone processes of ensuring that the names and locations of the parent table spaces are correct.) For information about creating auxiliary objects for a partitioned table space, see Creating-auxiliary-objects-for-a-partitioned-table-space.

9. Press END until the WORKID Action Menu is displayed.

Where to go from here

After you modify the table space, perform the tasks in the following table to analyze a work ID and execute a worklist.