Forum
Anonym
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]