Converting a VARCHAR data type to a LOB data type

To convert the data type to a LOB data type, you must analyze and execute both work IDs and worklists. For more information about unloading data that is contained in LOB columns, see Unload-Unicode-LOB-and-XML-data.

In this procedure, you perform the following subtasks:

1. Specify the column.
2. Add a ROWID column and a unique index.
3. Convert the data type.

To specify the column

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 and select Create A NEW WORKID. Then, press Enter.
3. On the Create WORKID panel, specify the information for the work ID.
   1. Select Alter for the Type.
   2. (Optional) Specify a Comment to describe the work ID.
   3. Press Enter.
4. Press END.The WORKID Action Menu is displayed. A message displayed in the upper-right corner indicates that the work ID has been added.
5. 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.
6. On the Object Specification panel, specify the name of a table space (or use wildcard characters). Then, press Enter.
7. On the Mixed List panel, type CO in the Act column adjacent to the table that contains the column defined as a VARCHAR or LONG VARCHAR.

To add a ROWID column and unique index

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.
2. Specify the attributes for the ROWID column.
   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.

      Note

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

   5. Press Enter.
3. Press END.
4. From the Mixed List panel, type CIX in the Act column adjacent to the table to which you added the ROWID column.

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

6. On the Index Key Mixed List panel, 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. The index key column is displayed below the index key.

   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.

7. On the Mixed List panel, press END until the WORKID Action Menu is displayed.
8. Analyze the alter-type work ID (see Analyzing-an-alter-type-work-ID-and-generating-a-worklist-in-Change-Manager).
9. Execute the alter-type worklist (see Executing-an-alter-type-worklist).

To convert the data type

1. Repeat Step 1 through Step 4 to create a second alter-type work ID.
2. Type the name of the second alter-type 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 pane, specify the name of a table (or use wildcard characters). Then, press Enter.
4. On the Mixed List pane, type CO in the Act column adjacent to the table that contains the column defined as a VARCHAR or LONG VARCHAR.
5. On the Table Columns List panel, type E in the Act column adjacent to the name of the column that is defined as a VARCHAR. Then, press Enter.

6. On the Table Column Detail panel, change the attributes for the 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. (If necessary) Specify the schema name.
   3. Specify BLOB or CLOB as the data type for the column.
   4. Specify the length of the column.
   5. Specify whether null values are allowed.
   6. Specify whether the column has a default value.
   7. Press END until the Mixed List panel is displayed.The table is now a base table with an object type of TBB.

7. To create the auxiliary objects for the base table, perform one of the following tasks:

   • To create objects in a partitioned table space, see Creating-auxiliary-objects-for-a-partitioned-table-space.
   • To create objects in a nonpartitioned table space, see Creating-auxiliary-objects-for-a-nonpartitioned-table-space.

   Now that the data type has been converted, you can analyze the second work ID and execute the worklist.