Dyn. SQL programs benefit from REOPT BIND option

Ansicht von 3 Beiträgen - 1 bis 3 (von insgesamt 3)
  • Author
    Posts
  • 10. August 2011 um 17:34 Uhr #4130

    Anonym
    Inaktiv

    Hi folks,

    most of you will know that SQL programs in certain situations may benefit from REOPT (RE)BIND option. Often ignored or just forgotten is the fact that dynamic SQL programs also benefit from that option!

    The most prominent representatives are DSNTEP2, DSNTIAUL, DSNREXX, DSNTEP4 and other IBM DB2 (sample) programs.

    The three options REOPT(ONCE), REOPT(NONE), and REOPT(ALWAYS) can be specified in the BIND and REBIND commands for plans and packages. REOPT(NONE) is the default option.

    The REOPT(ONCE) (V8) bind option tries to combine the benefits of REOPT(ALWAYS) and dynamic statement caching. The idea of REOPT(ONCE) is to re-optimize the access path only once (using the first set of input variable values) no matter how many times the same statement is executed.

    If you specify the new REOPT(AUTO) (V9) bind option, DB2 automatically determines whether a new access path is required to further optimize the performance of a statement for each execution. REOPT(AUTO) or REOPT(ONCE) only applies to dynamic statements that can be cached. If dynamic statement caching is turned off and DB2 executes a statement that is bound with REOPT(AUTO) or REOPT(ONCE), no reoptimization occurs.

    REOPT specifies whether DB2 determines access paths at bind time and again at execution time for statements that contain:

    • Input host variables
    • Parameter markers
    • [highlight]Special registers[/highlight]

    And what is so special about the thing? Some statements, specially those using CURRENT DATE, CURRENT USER, CURRENT TIME[STAMP] or an other special register may tremendously profit from re-optimized acces path.

    Example:
    SELECT * FROM BOOKINGS WHERE EFFECTIVE_DATE > CURRENT DATE

    I currently see only few small drawbacks:

    • DSC dynamic statement cache must be active
    • a large number of small and short-running SQL may raise re-optimization overhead
    • DSNTEP2 ends with return code if SQL produces a warning like SQLCODE +100 and others, the PREPWARN NO option shows no effect
    • EXPLAIN/PLAN_TABLE possibly shows different access path as chosen after re-optimization

    Try it! Give some feedback!

    Regards
    Gernot

    13. August 2011 um 18:56 Uhr #4278

    Anonym
    Inaktiv

    Appendix (1)

    Invalidating statements in the dynamic statement cache

    DB2 invalidates statements in the dynamic statement cache when you run RUNSTATS on objects to which those statements refer.
    In a data sharing environment, the relevant statements are also invalidated in the cache of other members in the group. DB2 invalidates the cached statements to ensure that the next invocations of those statements are fully prepared and that they use the latest access path changes.
    To invalidate statements in the dynamic statement cache without collecting statistics by: Specify the options UPDATE NONE and REPORT NO in the RUNSTATS utility control statement.

    The following control statement specifies that RUNSTATS is to invalidate statements in the dynamic statement cache for table space DSN8D81A.DSN8S81E.
    However, RUNSTATS is not to collect or report statistics or update the catalog.
    [tt]RUNSTATS TABLESPACE DSN8D81A.DSN8S81E
    REPORT NO
    UPDATE NONE[/tt]

    Or you may apply the other, the SQL method:[list bull-blackarrow][*][tt]ALTER TABLE …  AUDIT NONE[/tt][*][tt]RENAME used! INDEX[/tt][/list]

    15. August 2011 um 19:41 Uhr #4382

    Anonym
    Inaktiv

    Appendix (2)

    Original DSNTEP2 BIND statement as supplied by IBM in installation library SDSNSAMP(DSNTEJ1L:

    [tt]BIND PACKAGE (DSNTEP2) MEMBER(DSN§EP2L) +                           
         CURRENTDATA(NO) ACT(REP) ISO(CS) ENCODING(EBCDIC)               
    BIND PLAN(DSNTEPÜÜ)  PKLIST(DSNTEP2.*) +                            
         CURRENTDATA(NO) ACT(REP) ISO(CS) ENCODING(EBCDIC) SQLRULES(DB2)
    [/tt]
    which yields in …

    [tt]BIND  PLAN(DSNTEP2)         OWNER(id)         QUALIFIER(id)+   
         NODEFER(PREPARE)       VALIDATE(RUN)         ISOLATION(CS)+      
         CACHESIZE(3072)        CURRENTDATA(NO)       DEGREE(1)+          
         SQLRULES(DB2)          ACQUIRE(USE)          RELEASE(COMMIT)+    
         EXPLAIN(NO)            REOPT(NONE)           KEEPDYNAMIC(NO)+    
         IMMEDWRITE(NO)         DBPROTOCOL(DRDA)      ENCODING(273)+      
         ROUNDING(HALFEVEN)     DISCONNECT(EXPLICIT)+                      
         PKLIST(*.DSNTEP2.*)+                                              
         ENABLE(*)+                                                         
         ACTION(REPLACE)        RETAIN                                    
    END                                                                     
    BIND PACKAGE(DSNTEP2)       MEMBER(DSN§EP2L)+                            
         LIBRARY(‚libname‘)+                                  
         OWNER(id)              QUALIFIER(id)     SQLERROR(NOPACKAGE)+    
         VALIDATE(RUN)          ISOLATION(CS)         CURRENTDATA(NO)+         
         DEGREE(1)              EXPLAIN(NO)           REOPT(NONE)+            
         KEEPDYNAMIC(NO)        DBPROTOCOL(DRDA)      IMMEDWRITE(NO)+         
         ENCODING(273)          ROUNDING(HALFEVEN)+                           
         ENABLE(*)+                                                          
         ACTION(REPLACE)        REPLVER(V9R1.PM17349 or other version)                        
    END   [/tt]

    You should BIND DSNTEP2, DSNTEP4, DSNTIAL, DSNREXX and other programs with REOPT(ONCE|ALWAYS|AUTO) to enable reoptimization of access path for dynamic SQL statements. This helps for example to optimize access of statements which use special registers, e.g. CURRENT DATE.

    [tt]BIND PACKAGE(MYCOLLTEP2)       MEMBER(DSN§EP2L)+                           
         LIBRARY(‚libname‘)+                                 
         OWNER(id)              QUALIFIER(id)   SQLERROR(NOPACKAGE)+   
         DEFER(PREPARE)         VALIDATE(RUN)         ISOLATION(CS)+         
         CURRENTDATA(NO)        DEGREE(1)             EXPLAIN(NO)+          
         REOPT(ONCE)            KEEPDYNAMIC(NO)       DBPROTOCOL(DRDA)+    
         IMMEDWRITE(NO)         ENCODING(273)         ROUNDING(HALFEVEN)+   
         ENABLE(*)+                                                         
         ACTION(ADD)                     
    END
    BIND  PLAN(MYTEP2)          OWNER(id)       QUALIFIER(id)+    
         DEFER(PREPARE)         VALIDATE(RUN)         ISOLATION(CS)+          
         CACHESIZE(3072)        CURRENTDATA(NO)       DEGREE(1)+               
         SQLRULES(DB2)          ACQUIRE(USE)          RELEASE(COMMIT)+         
         EXPLAIN(NO)            REOPT(ONCE)           KEEPDYNAMIC(NO)+         
         IMMEDWRITE(NO)         DBPROTOCOL(DRDA)      ENCODING(273)+          
         ROUNDING(HALFEVEN)     DISCONNECT(EXPLICIT)+                           
         PKLIST(*.MYCOLLTEP2.*)+                                                   
         ENABLE(*)+                                                            
         ACTION(ADD)
    END [/tt]

  • Author
    Posts
Ansicht von 3 Beiträgen - 1 bis 3 (von insgesamt 3)

You must be logged in to reply to this topic.