Adding an index by using What-If Index

This task explains how to use the What-If Index feature to simulate the effects of adding an index.

Before you begin

Follow the instructions in Preparing-to-use-What-If-Index to specify options in preparation for your analysis.
Follow the instructions in Identifying-What-If-Index-candidates to locate a table that might benefit from the addition of an index.

To add an index

On the Statement Text report, type X (for Explain Stmt) over the plus sign (+) beside the SQL statement text to be Explained, and press Enter.
ASQEQRPW/I                      View a Report                      LINE 1 OF 5
Command ====> _______________________________________________ Scroll ===> CSR_

BMCSftwr.IODSTXDH    --         STATEMENT TEXT         --       11/15 14:48:23
Source : DOMS-ACTIVE   Intvl : 11/15 14:39 - UNLIMITED          More:       >
-------------------------------------------------------------------------------
   Actions for '+': X-Explain Stmt   T-Table/Index Breakdown

   Logical DB2 Name: DEBF
   Plan: ICT741DM Program: ACTJSELE Section:     1 Stmt Type: DYNAMIC Call Typ

  Statement Text
  ----------------------------------------------------------------------------
X SELECT *
   FROM    PXB.EMP
   WHERE   LASTNAME = 'BAKER'
The Explain Results panel is displayed.
FILE COMMANDS OPTIONS HELP
-------------------------------------------------------------------------------
PSSPW200      Explain Results for SQLTEXT
Command ===>                                                  Scroll ===> CSR
                                                                 More:       >
   Actions: S H R RS RW RI XD XS XP W P T C D U IM SA
   LBL   STMTNO     COST*RATE SQL-STATEMENT
C XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
         COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME   IXNAME   NU J O G
   XD01 1012.2565 1 1   0 SELECT    0 R      0 N EMP               N N N N
******************************** Bottom of Data *******************************
To clone the objects for that statement, type C in the space beside the statement and press Enter.The cloned objects appear on the Explain Results panel with cloned objects. Note that the objects are created in database PSSnnnnn (where nnnnn is a generated random number). The product creates the objects when you perform an Explain.
Important
The SEGSIZE of the clone that you create defaults to 4.
FILE COMMANDS OPTIONS HELP
-------------------------------------------------------------------------------
PSSPW200      Explain Results for SQLTEXT
Command ===>                                                  Scroll ===> CSR
                                                                 More:       >
   Actions: S H R RS RW RI XD XS XP W P T C D U IM SA
   CLONE Objects in database PSS60364
   STMTNO SQL-STATEMENT
      117 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
         CREATOR TBNAME          CARD    NPAGES COMP NACTIVE PARENT CHILD BPOO
W        PXB     EMP               -1        -1   -1       -1      0     0 BP0
            CREATOR IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRAT
            PXB     XEMP2               -1          -1    -1      -1 0.00000000
   LBL   STMTNO     COST*RATE SQL-STATEMENT
   XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
         COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME   IXNAME   NU J O G
   XD01 1012.2565 1 1   0 SELECT    0 R      0 N EMP               N N N N
******************************** Bottom of Data *******************************
To specify a What-If Index, type W in the space beside the table and press Enter.The Specify Index Attributes panel is displayed. Specifying a What-If Index (W) on the table displays default values for the new index as a starting point. Specifying W on an existing index in the clone uses that index definition as a starting point for your new index.
PSSPWIXA              Specify Index Attributes
Command ===>

Specify attributes and press ENTER to process selections.
Note: Name is case sensitive.

Name . . . . . . myindex
Uniquerule . . . D              (U/D/N) - N for UNIQUE WHERE NOT NULL
Clustering . . . N              (Y/N)
Partitioned . . N              (Y/N)
Padded . . . . . N              (Y/N)

/ Specify key columns
/ Update index and key column statistics
Specify attributes for a new index to be added to the table, select Specify key columns and Update index and key column statistics, and press Enter.The Select Key Columns panel is displayed.
Tip
Type S beside the statement on this panel, and press Enter to show nicely formatted text. Doing so is helpful when selecting index columns if you have a long statement and want to review the contents of the predicates.
Type S and press Enter again to hide this information.
PSSPW300             Select Key Columns
Command ===>                                                  Scroll ===> CSR
                                                                 More:       >
   Actions: S H E
          STMTNO SQL-STATEMENT
               0 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'

          For each column of the key, enter the key column sequence number
          and the order (Ascending, Descending, Random, or blank).

SEQ ORDER COLNO COLUMN NAME               CARD TYPE      LEN HI2KEY
    A         1 EMPNO                       -1 CHAR        6 4040404040404040
    A         2 FIRSTNME                    -1 VARCHAR    12 4040404040404040
    A         3 MIDINIT                     -1 CHAR        1 4040404040404040
1   A         4 LASTNAME                    -1 VARCHAR    15 4040404040404040
    A         5 WORKDEPT                    -1 CHAR        3 4040404040404040
    A         6 PHONENO                     -1 CHAR        4 4040404040404040
    A         7 HIREDATE                    -1 DATE        4 4040404040404040
    A         8 JOB                         -1 CHAR        8 4040404040404040
    A         9 EDLEVEL                     -1 SMALLINT    2 4040404040404040
    A        10 SEX                         -1 CHAR        1 4040404040404040
    A        11 BIRTHDATE                   -1 DATE        4 4040404040404040
    A        12 SALARY                      -1 DECIMAL     9 4040404040404040
    A        13 BONUS                       -1 DECIMAL     9 4040404040404040
    A        14 COMM                        -1 DECIMAL     9 4040404040404040
To select the key columns for the index, specify sequence numbers (SEQ) and order (ascending (A) or descending (D)).
Important
You have the following additional options:
- You can specify random (R) next to a column in the Order field. Doing so defines a random key column in a similar way to using the RANDOM option with the CREATE INDEX and ALTER INDEX statements.
- If you specify " " (a blank character) in the input field of the ORDER column, you can generate the INCLUDE column.
- You can add indexes on expressions on the Select Key Columns panel. For more information, see Adding-an-expression-to-a-What-If-Index.
When you finish entering key column information, press Enter to display the Update Index Access Path Statistics panel.Some of the values on this panel are gleaned from existing statistics.
PSSPWIXS             Update Index Access Path Statistics
Command ===>

Specify statistics and press END to continue

Name . . . . . . myindex

First Key Card   -1
Full Key Card    -1
Cluster Ratio    80              (0-100)
Leaf Pages . . . 20
Number of Levels 2

/ Update first key column statistics
Update index and key column statistics:
1. (optional) On the Update Index Access Path Statistics panel, specify statistical values as needed.
2. Press Enter to display the Update First Key Column Statistics panel.
  PSSPWED    UPDATE_FIRST_KEY_COLUMN_STATISTICS              Columns 00001 00031
  Command ===>                                                  Scroll ===> CSR
          FREQUENCY      VALUE   - VARCHAR 15 -
  ****** ***************************** Top of Data ******************************
  000001 0.500000000000 CCCCCCCCCCCCCCC
  ****** **************************** Bottom of Data ****************************
3. Edit the default values as needed (as in Edited first key column statistics).
  PSSPWED    UPDATE_FIRST_KEY_COLUMN_STATISTICS              Columns 00001 00031
  Command ===>                                                  Scroll ===> CSR
          FREQUENCY      VALUE   - VARCHAR 15 -
  ****** ***************************** Top of Data ******************************
  000001 0.500000000000 ALLISTER
  000002 0.300000000000 BAKER
  000003 0.200000000000 ELDON
  ****** **************************** Bottom of Data ****************************
4. Press F3 until the Explain Results panel is displayed .Note that the list displays the new index . However, the index is not created until you perform the Explain.
  Important
  You can make any number of changes before you perform the Explain. The What-If Index process is cumulative, so performing incremental changes followed by Explains lets you see the effects of each change. When you perform a dynamic Explain on a clone, the Db2 optimizer uses the cloned objects as they exist at the time that you perform the dynamic Explain. The Index Component compares each successive What-If Index Explain to the previous Explain.
  FILE COMMANDS OPTIONS HELP
  -------------------------------------------------------------------------------
  PSSPW200      Explain Results for SQLTEXT
  Command ===>                                                  Scroll ===> CSR
                                                                   More:       >
     Actions: S H R RS RW RI XD XS XP W P T C D U IM SA
  XD CLONE Objects in database PSS60364
     STMTNO SQL-STATEMENT
        117 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
           CREATOR TBNAME          CARD    NPAGES COMP NACTIVE PARENT CHILD BPOO
           PXB     EMP               -1        -1   -1       -1      0     0 BP0
              CREATOR IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRAT
              PXB     XEMP2               -1          -1    -1      -1 0.00000000
              PXB     myindex             -1          -1    20       2 0.80000000
     LBL   STMTNO     COST*RATE SQL-STATEMENT
     XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
           COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME   IXNAME   NU J O G
     XD01 1012.2565 1 1   0 SELECT    0 R      0 N EMP               N N N N
  ******************************** Bottom of Data *******************************
To perform a dynamic Explain, type XD in the space beside the clone and press Enter.The results of the Explain are displayed. The WI01 label represents the Explain on the statement for the cloned objects. The asterisk and highlighting make it easier to see the differences between the Explains.
FILE COMMANDS OPTIONS HELP
-------------------------------------------------------------------------------
PSSPW200      Explain Results for SQLTEXT
Command ===>                                                  Scroll ===> CSR
                                                                 More:       >
   Actions: S H R RS RW RI XD XS XP W P T C D U IM SA
S CLONE Objects in database PSS60364
   LBL   STMTNO     COST*RATE SQL-STATEMENT
   WI01*    117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
   XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
         COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME   IXNAME   NU J O G
   WI01* 344.63818 1 1   0 SELECT    0 I      1 N EMP      myindex N N N N
   XD01 1012.2565 1 1   0 SELECT    0 R      0 N EMP               N N N N
******************************** Bottom of Data ******************************
To show the cloned objects, type S in the space beside the clone and press Enter.
The cloned objects are displayed.
FILE COMMANDS OPTIONS HELP
-------------------------------------------------------------------------------
PSSPW200      Explain Results for SQLTEXT
Command ===>                                                  Scroll ===> CSR
                                                                 More:       >
   Actions: S H R RS RW RI XD XS XP W P T C D U IM SA
   CLONE Objects in database PSS60364
   STMTNO SQL-STATEMENT
      117 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
         CREATOR TBNAME          CARD    NPAGES COMP NACTIVE PARENT CHILD BPOO
         PXB     EMP               -1        -1   -1       -1      0     0 BP0
            CREATOR IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRAT
            PXB     XEMP2               -1          -1    -1      -1 0.00000000
            PXB     myindex             -1          -1    20       2 0.80000000
   LBL   STMTNO     COST*RATE SQL-STATEMENT
   WI01*    117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
   XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'
         COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME   IXNAME   NU J O G
   WI01* 344.63818 1 1   0 SELECT    0 I      1 N EMP      myindex N N N N
   XD01 1012.2565 1 1   0 SELECT    0 R      0 N EMP               N N N N
******************************** Bottom of Data *******************************
After showing the cloned objects, you can make additional changes to the clone and perform additional What-If Index Explains. To see the effects of one or more changes, type XD in the space beside the clone, and press Enter.The Explain Results panel displays the results of your changes. You can then perform additional What-If Index operations (such as adding another index, dropping an index, updating statistics for an index, and so on).

Adding an index by using What-If Index

Before you begin

To add an index

On this page