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 BMC AMI Command Center for Db2 13.2

Adding an index to a What-If scenario

Use the following procedure to add a What-If index to a selected table and evaluate how the change would affect the SQL statement's performance.

To add a new index

New console
  1. Select the table as described in Creating-and-editing-a-What-If-index-scenario.
  2. Click Add.
  3. In the Index Attributes tab, define the new index:
    1. At Index name, enter the name that you want to use.
    2. At Unique rule, accept the default (Duplicate) or select Unique or Unique unless Null.
    3. Select the corresponding check boxes if you want the index to be clustering, partitioned, or padded.
    4. Click Next.
  4. In the Key Columns tab, define the key columns for the index:
    1. Add columns or remove available columns from the Selected Columns pane.
    2. If you want to reorder a column, select it and use the Up NW_arrow_up_circle.png or Down NW_arrow_down_circle.png button to change the column's position.

    3. If you want to add an index on expression, click NW_plus_circle.png and enter an expression. Then, click OK.You can enter any valid standard expression that SQL supports.

      Tip

      To remove an index on expression, click NW_minus_circle.png .

    4. Click NW_pencil_adapt.png to sort the columns in ascending, descending, or random order.
    5. Click Next.
  5. In the Index Statistics tab, define the statistics for this index:
    1. At First key cardinality, enter a positive integer (default 0) or -1 (indicating that no statistics have been gathered) .
    2. At Full key cardinality , enter a positive integer (default 0) or -1 (indicating that no statistics have been gathered).
    3. At Cluster ratio , enter a number between 0 and 1 (default 0.8) or -2 (indicating that no statistics have been gathered).
    4. At Leaf pages, enter a positive integer or -1 (the default, indicating that no statistics have been gathered) .
    5. At Number of Levels, enter the number of required levels (default -1).
    6. Click Finish.The wizard closes, and the results pane shows the new index with NW_plus_circle_green.png beside it.
Classic console
  1. Select the table as described in Creating-a-What-If-scenario.
  2. Click Add.
  3. In the Index Attributes dialog box, define the new index:
    1. At Index name, enter the name that you want to use.
    2. At Unique rule, accept the default (Duplicate) or select Unique or Unique unless Null.
    3. Select the corresponding check boxes if you want the index to be clustering, partitioned, or padded.
    4. Click Next.
  4. In the Key Columns dialog box, define the key columns for the index:
    1. Add columns or remove available columns from the Selected Columns pane.
    2. If you want to reorder a column, select it and use the Up MoveUp-16.png or Down MoveDown-16.png button to change the column's position.

    3. If you want to add an index on expression, click Add-16.png and enter an expression. Then, click OK.You can enter any valid standard expression that SQL supports.

      Tip

      To remove an index on expression, click Delete-16.png .

    4. Click the arrow that indicates to sort the columns in ascending, descending, or random order.
    5. Click Next.
  5. In the Index Statistics dialog box, define the statistics for this index:
    1. At First key cardinality, enter a positive integer (default 0) or -1 (indicating that no statistics have been gathered) .
    2. At Full key cardinality , enter a positive integer (default 0) or -1 (indicating that no statistics have been gathered).
    3. At Cluster ratio , enter a number between 0 and 1 (default 0.8) or -2 (indicating that no statistics have been gathered).
    4. At Leaf pages, enter a positive integer or -1 (the default, indicating that no statistics have been gathered) .
    5. At Number of Levels, enter the number of required levels (default -1).
    6. Click Finish.The wizard closes, and the results pane shows the new index with Add-16.png beside it.


Where to go from here

You can now Explain and compare the new scenario.

Related topic

Creating-and-editing-a-What-If-index-scenario


 

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