Changing an Index from AA Values to Composite Key

This section defines a set of guidelines for creating a composite key for the data set CUSTOMER, after data tables are populated using AA values as the index. For instance, this might be done when AA values are changed as a result of a garbage collection reorganization (ds_mode set to 12).

Note: This procedure is not compatible with the Client Configurator.

To define a composite key

1. Define the new keys.

   Using a relational database query tool, update the DATAITEMS table, and set item_key = n for each item that is to be part of the composite key. The value n is a positive integer that indicates the order of the columns in the index (it is recommended that you use values of 1, 2, 3... although the actual values are not relevant, as long as they are in the desired numeric order).

   The SQL statements to use for the items named key1, key2 and key3 are as follows:

   update DATAITEMS set item_key=1
   where item_name='key1' and table_name='customer'

   update DATAITEMS set item_key=2
   where item_name='key2' and table_name='customer'

   update DATAITEMS set item_key=3
   where item_name='key3' and table_name='customer'

2. Remove the my_aa column from the index.

   Using a relational database query tool, update the DATAITEMS table, and set item_key = 0 for the my_aa column using the following SQL statement:

   update DATAITEMS set item_key=0
   where item_name='my_aa' and table_name='customer'

3. Modify the DATASETS misc_flags column for the data set so that the DSFLG_Uses_AA_values bit (512) is not used.

   Using a relational database query tool, subtract 512 from the value displayed for misc_flags.

4. Set the DS_Needs_Generating bit for the data set.

   Using a relational database query tool, update the DATASETS table, set status_bits = 2, and set the ds_mode mode column back to 2 (it should be 12 after the garbage collection reorganization is detected), using the following SQL statement:

   update DATASETS set ds_mode=2, status_bits=2
   where dataset_name='CUSTOMER' and data_source ='CMDB'

5. Run the generate command to create new scripts as follows:

   dbutility generate CMDB

6. Run the refresh command to update the stored procedures.

   dbutility refresh CMDB CUSTOMER

7. Drop the old index.

   Using a relational database query tool, drop the old index (assuming that the index name in the column index_name of the DATATABLES is aa_set_customer), using the following SQL statement:

   drop index aa_set_customer

8. Create the new index.

   To create a new index, either run the script.index.customer script using a relational database query tool, or use the runscript command. After this step is complete, you can continue processing normally.

   Note: Remember that before you reclone this data set, you must change the set_name to user_set, and you must include the SQL statements to create the composite key by setting item_key for the keys in the script script.user_layout.customer as described earlier. You can update the script as soon as you are done using the procedure outlined above. Note that this script is only used by the define and redefine commands.

9. To ensure that these changes are not lost during future define and redefine operations, make the script changes described in Creating a Composite Key.