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.

Saving What-If Index DDL changes

Use this procedure to save changes (except text changes) made on indexes during the session.

These changes can include DDL for adding or dropping indexes, or SQL statements to update index statistics. Changes are saved to a PDS that you can import to another statement that references the objects during a What-If Index operation, or that you can use in an environment where you want the actual changes to take effect.

These changes can include DDL for adding or dropping indexes, or SQL statements to update index statistics. Changes are saved to a PDS. During a What-If Index operation, you can import the PDS to another statement that references the objects. or to an environment where you want the changes to take effect.

Related topic

Using-the-What-If-Index-function


Important

Because What-If Index changes are cumulative and are saved at a given point in time, we recommend that you save your DDL after each change is made. Some changes might have negative effects on the access path. Saving your DLL at incremental points enables you to revert to an earlier version.

Before you begin

Perform the following procedures to create an index by using cloned objects:

The examples in this section are continued from the previous task, Updating-statistics-on-an-index-by-using-What-If-Index.

To save What-If Index DDL changes

  1. On the Explain Results panel, type SA in the space beside the clone for which you want to save the DDL, and press Enter.

    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
    U           PXB     myindex             -1          -1    20       2 0.80000000
       LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
       WI02     117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
       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
       WI02  344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
       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 *******************************

    When you specify an SA command on a cloned object, all changes (except text changes) made on indexes are saved for the session up to that point.

    The Save DDL panel is displayed.

    PSSPWD00 --------------------------- Save DDL ---------------------------------
    Command ===>
    Specify a partitioned (include member) or sequential data set for DDL.

    DDL Data Set . . . 'RDADAC.SQLX510.CNTL(DDL####)'
    Current Counter   47    (Replaces #### in member name, then incremented)

    Generate DDL . . . Y    (Y/N - Generate DDL, save in data set)  
    Browse Data Set    Y    (Y/N -Browse DDL data set)

  2. Specify a partitioned data set (including member) or sequential data set in which to save your DDL, specify Y (Yes) for the Generate DDL and Browse Data Set options, and press Enter.The DDL is saved to the data set that you specified, and the saved DDL is opened in browse mode (see Browse saved DDL data set).

    Important

    In DDL that is saved from this process, DROP INDEX statements are prefixed with an X. You must delete the X before you can execute the statement.

    ISRBROBA RDADAC.SQLX610.CNTL(DDL0047)       Line 0000000 DDL Complete
    Command ===>                                        Scroll ===> CSR
    ****************************** Top of Data **********************************

    -- DDL GENERATED ON Mon Mar 7 15:04:08 2017

     XDROP INDEX PXB.XEMP2 ;                                                        
     COMMIT ;                                                                       

     CREATE INDEX PXB.'myindex'                                                     
     ON      PXB.EMP(LASTNAME ASC) ;                                                
     COMMIT ;                                                                       

     UPDATE  SYSIBM.SYSINDEXES                                                      
     SET     FIRSTKEYCARDF = 100, FULLKEYCARDF = 100, CLUSTERRATIOF =               
             0.900000000000, NLEVELS = 2, NLEAF = 20, STATSTIME =                   
             CURRENT_TIMESTAMP                                                      
     WHERE   CREATOR = 'PXB'                                                        
       AND   NAME = 'myindex' ;                                                     
     COMMIT ;

     INSERT                                                                         
     INTO    SYSIBM.SYSCOLDIST                                                      
     VALUES  (0, CURRENT_TIMESTAMP, 'N', 'PXB', 'EMP', 'LASTNAME',                  
             X'C1D3D3C9E2E3C5D940404040404040', 'F', 0, '', 1,                      
             0.500000000000) ;                                                      
     INSERT                                                                         
     INTO    SYSIBM.SYSCOLDIST                                                      
     VALUES  (0, CURRENT_TIMESTAMP, 'N', 'PXB', 'EMP', 'LASTNAME',                  
             X'C2C1D2C5D940404040404040404040', 'F', 0, '', 1,                      
             0.300000000000) ;                                                      
     INSERT                                                                         
     INTO    SYSIBM.SYSCOLDIST                                                      
     VALUES  (0, CURRENT_TIMESTAMP, 'N', 'PXB', 'EMP', 'LASTNAME',                  
             X'C5D3C4D6D540404040404040404040', 'F', 0, '', 1,                      
             0.200000000000) ;                                                      
     COMMIT ;                                                                       

    ********************** Bottom of Data ********************************


 

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