Saving What-If Index DDL changes

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.

Before you begin

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).

   Note

   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 ********************************