Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see ALTER and BMC AMI Change Manager for Db2 13.1.

Creating auxiliary objects for a partitioned table space

Use the following procedures to create auxiliary objects for an existing base table in a partitioned table space.

The CAX action code is used to create the auxiliary table spaces, tables, and indexes that are required for a base table that contains a LOB column. In this task, name prefixing is used in a template to automatically and uniquely name the auxiliary objects.

In this task, you perform the following subtasks:

  1. Display the list of columns.
  2. Create the auxiliary table spaces.
  3. Create the auxiliary tables.
  4. Create the auxiliary indexes.
  5. Review the list of auxiliary objects.
  6. (Optional) Undo changes to the auxiliary objects.

Before you begin

If you want to increase the number of partitions in the partitioned table space, do so before you begin this task.

To display the list of columns

  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 partitioned table space (or use wildcard characters). Then, press Enter.
  4. On the Mixed List panel, type CO in the Act column adjacent to the base table (TBB) that contains a LOB column.
  5. On the Table Columns List panel, type CAX in the Act column adjacent to the LOB column name and press Enter.

To create the auxiliary table spaces

  1. On the Create Auxiliary Tablespace Template panel, select Use name prefixing.

    Note

    Name prefixing will also be used for the auxiliary tables and indexes.

  2. Specify the attributes for the table spaces.

    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 a prefix for the name of the table spaces. You can retain the first byte (L) and replace the three question mark (?) characters, or replace all four of the characters. Underscores are not allowed.

      Note

      If you use name prefixing, the prefix is limited to a maximum of four characters. If you do not use name prefixing, you must specify a name (up to eight characters) for the table spaces.

    2. Specify the owner of the table space.
    3. Specify the attributes for the table spaces. If you need to specify additional attributes, type S to select More Tablespace Attributes.
    4. Specify whether the parent database already exists in the Db2 catalog.
    5. Press END.The Auxiliary Tablespace List panel is displayed. A unique four-digit suffix is appended to the table space name prefix for each table space.

  3. If necessary, modify the names of the auxiliary table spaces. Then, press END.

    Note

    If you did not use name prefixing, you must make the names of the table spaces unique.

    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.

    The table spaces are saved in the CD tables.

To create the auxiliary tables

  1. On the Create Auxiliary Table Template panel, specify the attributes for the tables.

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

    2. Specify a prefix for the name of the tables.

      Note

      Because you chose to use name prefixing for the auxiliary table spaces, you must use name prefixing for the auxiliary tables.

      If you do not delete the underscores at the end of the name of the tables, the underscores are included as part of the name.

    3. Specify a label for the tables.

    4. Specify whether the parent (auxiliary) table space already exists in the Db2 catalog.

      Note

      In this example, the auxiliary table space does not exist.

    5. To specify a comment for the tables, type S to select Table Comment.

      Tip

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

    6. Press END.

  2. (If necessary) On the Auxiliary Table List panel, modify the names of the auxiliary tables. Then, press END.

    Note

    If you did not use name prefixing, you must make the names of the tables unique.

    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.

    The tables are saved in the CD tables.

To create the auxiliary indexes

  1. On the Create Auxiliary Index Template panel, specify the attributes for the indexes.

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

    2. Specify a prefix for the name of the indexes.

      Note

      Because you chose to use name prefixing for the auxiliary table spaces, you must use name prefixing for the auxiliary indexes.

      If you do not delete the underscores at the end of the name of the indexes, the underscores are included as part of the name.

    3. Specify values for the attributes. To specify additional attributes, type S to select More Attributes.

    4. Specify whether the parent (auxiliary) table already exists in the Db2 catalog.

      Note

      In this example, the auxiliary table does not exist.

    5. To specify a comment for the indexes, type S to select Comment.
    6. Press END.

  2. (if necessary) On the Auxiliary Index List panel, modify the names of the auxiliary indexes. Then, press END.

    Note

    If you did not use name prefixing, you must make the names of the indexes unique.

    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.

    The indexes are saved in the CD tables.

To review the list of auxiliary objects

  1. On the Table Columns List panel, type AX in the Act column adjacent to the LOB column name to review the list of auxiliary objects for the base table.

    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.

    The Auxiliary Objects List panel is displayed. The auxiliary table space, table, and index for each partition are listed by the LOB column and partition, as shown in the following figure.


    ALUSAXL ER   --------------------- Auxiliary Objects List ---------------------
    Command ===>                                                    Scroll. . CSR
                                                                                  
    WORKID . . . . .  RDACRJ.LOBTEST                              Commands: CANCEL
                                                                                 
    Base Table . . .  RDACRJ.T_L01PS                                              
                                                                                  
    Type action next to object and press Enter.                                   
       E=Edit  L=Like  D=Drop  U=Undo                                              
                                                             Objects 1 to 11 of 12
                                                                     More:     +  
    Act     Object-Type    Objects                            Column          Part
    ************************************ TOP **************************************
          L TS . . . . . . CJACML01   L01PSL1A                                    
          L   TB . . . . .   RDACRJ     T_L01PSL1A            XCLOB              1
          L     IX . . . .     RDACRJ     I_L01PSL1A                              
          L TS . . . . . . CJACML01   L01PSL1B                                    
          L   TB . . . . .   RDACRJ     T_L01PSL1B            XCLOB              2
          L     IX . . . .     RDACRJ     I_L01PSL1B                              
          L TS . . . . . . CJACML01   L01PSL1C                                    
          L   TB . . . . .   RDACRJ     T_L01PSL1C            XCLOB              3
          L     IX . . . .     RDACRJ     I_L01PSL1C                              
          L TS . . . . . . CJACML01   L01PSL1D                                    
          L   TB . . . . .   RDACRJ     T_L01PSL1D            XCLOB              4
  2. Press END until the Mixed List panel is displayed.The *@ marker adjacent to the base table (TBB) object indicates that auxiliary objects were created or modified for the base table.
  3. On the Mixed List panel, press END until the WORKID Action Menu is displayed.

To undo changes to auxiliary objects

If you wanted to undo the changes to the base table, you must undo the changes to the auxiliary objects.

  1. In the Mixed List panel, type AX in the Act column adjacent to the modified base table (TBB).

  2. In the Auxiliary Objects List panel, remove the requested changes to the auxiliary objects.

    • To undo changes to all of the objects listed, type UNDOALL on the Command line.
    • To undo changes to one or more of the objects listed, type U in the Act column adjacent to the auxiliary objects.

    Now that the auxiliary objects have been created, you can analyze a work ID and execute a worklist.

Related topics

Accomplishing-your-data-structure-modification-goals

Analyzing-an-alter-type-work-ID-and-generating-a-worklist-in-Change-Manager

Executing-an-alter-type-worklist

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*