Space announcement This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Adding an expression to a What-If Index

This procedure describes how to add an index on an expression in your What-If Index analysis.

Related topic

Using-the-What-If-Index-function

Important

You can access the What-If Index from multiple points within the product. This example shows you accessing the What-If Index functions by accessing the Ad Hoc SQL option on the Explain Specification panel.

  1. At the Explain Object Specification panel, select Ad Hoc SQL and press Enter.An editing panel (see Explain Edit panel) is displayed.

    EDIT       EXPLAIN                                       Columns 00001 00080
    Command ===>                                                 Scroll ===> CSR
    ****** ********************************* Top of Data**********************  
    ==MSG> -Warning- The UNDO command is not available until you change         
    ==MSG>           your edit profile using the command RECOVERY ON.           
    000001  SELECT  EMPNO                                                       
    000002  FROM    PSS.EMP                                                    
    000003  WHERE   UPPER(LASTNAME, 'EN_US') = 'JOE'                           
    000004    AND   UPPER(FIRSTNME, 'EN_US') = 'JOHN'

  2. Enter the SQL statements that you want to use and press F3.The Explain or Execute Parameters panel is displayed.

    PSSPA117 ----------------- Explain or Execute Parameters ---------------------
    Command ===>                                                                  
                                                                                 
    Specify the options below and press ENTER to continue.                        
                                                                                 
    Option . . . . 1    1. Explain                                                
                        2. Execute                                                
                        3. Edit                                                   
                                                                                 
    Qualifier Name SYSIBM

  3. At the Explain or Execute Parameters panel, select Explain and press Enter.The Explain Results panel (see Explain Results panel showing an ad hoc query) is displayed.

    FILE  COMMANDS  OPTIONS  HELP
    -------------------------------------------------------------------------------
    DEDR                    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       0    126.728729 SELECT EMPNO FROM PSS.EMP WHERE UPPER(LASTNAME, '
             COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME             IXNAME
       XD01  126.72872  1  1   0 SELECT    0 I      2 N  EMP                UPPER_E

  4. To create a clone of the object upon which you want to perform the What-If analysis, type C in the space beside the object, and press Enter.The Explain Results panel (see Explain Results panel showing the clone) shows the clone.

    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 PSS47404                                           
       STMTNO SQL-STATEMENT                                                          
            0 SELECT EMPNO FROM PSS.EMP WHERE UPPER(LASTNAME, 'EN_US') = 'JOE' AND U
             TBNAME            CARD    NPAGES COMP  NACTIVE PARENT CHILD BPOOL DATAB
     W       EMP                 -1        -1   -1       -1      0     0 BP0   PSSQA
                IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRATIO CLUS   
                EMPFULLNAM          -1          -1    -1      -1 0.0000000000 N      
                TESTIXEMPN          -1          -1    -1      -1 0.0000000000 N      
                TOTAL_COMP          -1          -1    -1      -1 0.0000000000 N
  5. To specify a What-If Index, type W in the space beside the table and press Enter.The Specify Index Attributes panel is displayed.
  6. Specify attributes for a new index to be added to the table, select Specify key columns, and press Enter.The Select Key Columns panel is displayed. If the table already had some indexed expressions, those expressions are displayed on the panel.

  7. To add an expression to the key columns list, perform the following steps:

    1. In the SEQ field next to the column that you want to include in the expression, type E and press Enter.

    2. In the editing panel (see Explain Edit panel showing an expression), enter the expression upon which you want to create an index.

      ISREDDE2       EXPLAIN                          Columns 00001 00080
      Command ===>                                    Scroll ===> CSR
      ************************ Top of Data ******************************

      000001  SALARY + BONUS                                
      ****** **************************Bottom of Data *******************
    3. Press F3 to return to the Select Key Columns panel.

    The Select Key Columns panel (see Select Key Columns showing an expression) displays the expression that you created.

    PSSPW300             Select Key Columns                                        
    Command ===>                                                  Scroll ===> CSR  
                                                                     More:       >
       Actions: S H E                                                              
              STMTNO SQL-STATEMENT                                                 
                   0 SELECT EMPNO FROM PSS.EMP WHERE UPPER(LASTNAME, 'EN_US') = 'JO
                                                                                  
              For each column of the key, enter the key column sequence number     
              and the order (Ascending, Descending, Random, or blank).             
                                                                                  
    SEQ ORDER EXPRESSION                                                           
     1  A     SALARY + BONUS                                                       
                                                                                  
    SEQ ORDER COLNO COLUMN NAME               CARD TYPE      LEN HI2KEY           L
    2   A         1 EMPNO                       -1 CHAR        6                   
        A         2 FIRSTNME                    -1 VARCHAR    12                   
        A         3 MIDINIT                     -1 CHAR        1                   
     3  A         4 LASTNAME                    -1 VARCHAR    15                   
        A         5 WORKDEPT                    -1 CHAR        3                   
        A         6 PHONENO                     -1 CHAR        4                   
        A         7 HIREDATE                    -1 DATE        4                   
        A         8 JOB                         -1 CHAR        8                   
        A         9 EDLEVEL                     -1 SMALLINT    2                   
        A        10 SEX                         -1 CHAR        1                   
        A        11 BIRTHDATE                   -1 DATE        4                   
        A        12 SALARY                      -1 DECIMAL     9                   
        A        13 BONUS                       -1 DECIMAL     9                   
        A        14 COMM                        -1 DECIMAL     9                   

  8. To select the key columns for the index, specify sequence numbers (SEQ) and order (ascending (A) or descending (D)).

    Important

    You can also 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.

  9. Press F3 to return to the Explain Results panel.The new index is displayed in the list. At this point, the index has not yet been created; it is created when you perform the Explain.
  10. 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 visually identify the differences between the Explains.

  11. To show the cloned objects, type S in the space beside the cloned object and press Enter. Then, type S next to the index and press Enter.The Explain Results panel (see Explain Results panel showing an index on expression) shows the detailed information.

    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 PSS44781                                                 
       STMTNO SQL-STATEMENT                                                                
            0 SELECT EMPNO FROM PSS.EMP WHERE UPPER(LASTNAME, 'EN_US') = 'JOE' AND UPPER(FI
             TBNAME            CARD    NPAGES COMP  NACTIVE PARENT CHILD BPOOL DATABASE LOC
             EMP                 -1        -1   -1       -1      0     0 BP0   PSSQADB  T  
                IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRATIO CLUSTERING CL
                EMPFULLNAM          -1          -1    -1      -1 0.0000000000 N          N
                TESTIXEMPN          -1          -1    -1      -1 0.0000000000 N          N
                TOTAL_COMP          -1          -1    -1      -1 0.0000000000 N          N
                UPPER_EMPN          -1          -1    -1      -1 0.0000000000 N          N
                XEMP2               -1          -1    -1      -1 0.0000000000 N          N
                myindex2             0           0    20       2 0.8000000000 N            
                   KEY COLUMN         COLNO ORDERING                                       
                   SALARY + BONUS         0 A                                              
                   LASTNAME               4 A                                              
       LBL   STMTNO     COST*RATE SQL-STATEMENT                                            
       XD01       0    126.728729 SELECT EMPNO FROM PSS.EMP WHERE UPPER(LASTNAME, 'EN_US')
             COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME             IXNAME         
       XD01  126.72872  1  1   0 SELECT    0 I      2 N  EMP                UPPER_EMPNAME


 

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