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.

Adding a ROWID and a LOB column to create a base table

Use the following procedure to add a ROWID column and a LOB column to an existing table to create a base table. You can also create a unique index for a ROWID column that is defined as GENERATED BY DEFAULT. A base table must exist before you can create auxiliary objects.

In this task, you perform the following subtasks:

  1. Display the list of columns.
  2. Create a ROWID column.
  3. Create a LOB column.
  4. Create a unique index for the ROWID column.
  5. Create the auxiliary objects for the base table.

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 table space (or use wildcard characters). Then, press Enter.
  4. On the Mixed List panel, type CO in the Act column adjacent to a table.

To create a ROWID column

  1. On the Table Columns List panel, type I in the Act column adjacent to a column name. Then, press Enter.A blank line is inserted after the line on which you typed I.

    Note

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

  2. Specify the attributes for the ROWID column.

    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 name of the ROWID column.
    2. Specify ROWID as the column type.
    3. Type N in the Nl column to indicate that a null value is not allowed.
    4. Type D in the Df column to indicate that the column is defined as GENERATED BY DEFAULT.
    5. Press Enter.

To create a LOB column

  1. On the Table Columns List panel, type I in the Act column adjacent to a column name. Then, press Enter.A blank line is inserted after the line on which you typed I.

    Note

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

  2. Specify the attributes for the LOB column.

    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 name of the LOB column.
    2. Specify BLOB, CLOB, or DBCLOB as the column type.
    3. Specify the length of the column.
    4. Specify whether null values are allowed.
    5. Specify whether the column has a default value.
    6. Press Enter.
  3. Press END.The Mixed List panel is displayed. The table is now a base table with an object type of TBB.

To create a unique index for the ROWID column

If the ROWID column is defined as GENERATED BY DEFAULT, a unique index is required for the column.

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

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

    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 U to indicate that the index is unique.
    4. Specify whether the parent table already exists in the Db2 catalog.
    5. Type S to select Keys and TB Cols.
    6. 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     IXG_FOR_ROWID
         TABLE COLUMNS . : RDACRJ     TEMP2
    C   L       ROW_ID                           ROWID                        N  N  
                OTRACOL                          CHAR                      5  N  N  
        L       CLOB                             CLOB                         Y  N  
    *********************************** BOTTOM ************************************

  3. Specify the index key.

    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 ROWID 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     IXG_FOR_ROWID
          L   1   ROW_ID                           A
           TABLE COLUMNS . : RDACRJ     TEMP2
          L       ROW_ID                           ROWID                        N  N  
                  OTRACOL                          CHAR                      5  N  N  
          L       CLOB                             CLOB                         Y  N  
      *********************************** BOTTOM ************************************

    4. Press END until the Mixed List panel is displayed.

      Note

      The object type for the new index is displayed as IXU. After you execute the worklist, the object type for the index is displayed as IXG.

To create the auxiliary objects for the base table

Perform one of the following tasks:

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

 

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