Oppolzer - Informatik / Blog

T.P. wrote:

The question is - what is different between the static bind and the dynamic
explain? I will take a few guesses.......remember, these are guesses based upon
the information I have.

Difference in parallelism enablement between the static bind and the dynamic
explain. Bind parms show DEGREE(1) for the static, but the explain is dynamic,
and dynamic relies on zparm CDSSRDEF to determine parallelism enablement. I
doubt however that it is parallelism - but thought I would mention it as a
potential difference.

2nd - some access methods are not supported with multi-row fetch. So it is
possible that a static bind uses single row fetch, but a dynamic explain uses
MRF (DSNTEP4 or DSNTIAUL). I think this is less likely also.

Final (and most likely) reason - the static explain (from bind) relies upon the
host variable definitions to compare the datatype and length between the column
and the host variable for the predicate. If the host variable definitions are
longer than the column being compared, then V8 made this indexable for equal
predicates, but range predicates are still not indexable for character
datatypes. Thus any of the predicates below will be non-indexable if there is a
datatype/length mismatch, but indexable if they do a dynamic explain and use
"?" instead of the host variables, because "?" will use the datatype of the
column being compared - so not mismatch will be exposed.

AND D_CNTR_BEG <= :DCLAG2T0420-OFCSRVCPL.D-CNT-BEG
AND ( (:DCLAG2T0420-OFCSRVCPL.D-CNT-BEG < D_CNTR_CNCL )
OR ( D_CNTR_CNCL IS NULL ) ) AND
( ( :DCLAG2T0420-OFCSRVCPL.D-CNT-BEG < D_TRANSFER_AGENT )
OR ( D_TRANSFER_AGENT IS NULL ) )

Regards
Terry Purcell

In Reply to Mike Jessen:

    Steen - yes, there are host variables - here's the code taken from the
    program:
    SELECT 'Y' INTO :SERV-PLUS-PARM-AREA.WS-SP-IND FROM AG2T0640_M_OFTRMCN
    WHERE I_AGCY = :DCLAG2T0420-OFCSRVCPL.I-AGCY
    AND I_OFFICE = :DCLAG2T0420-OFCSRVCPL.I-OFFICE
    AND I_CNTROPT BETWEEN '10311' AND '10319'
    AND D_CNTR_BEG <= :DCLAG2T0420-OFCSRVCPL.D-CNT-BEG
    AND ( (:DCLAG2T0420-OFCSRVCPL.D-CNT-BEG < D_CNTR_CNCL )
    OR ( D_CNTR_CNCL IS NULL ) ) AND ( ( :DCLAG2T0420-OFCSRVCPL.D-CNT-BEG <
    D_TRANSFER_AGENT ) OR ( D_TRANSFER_AGENT IS NULL ) )
    WITH UR
    QUERYNO 12

    These are the rebind options we specify:
    CURRENTDATA(NO )
    VALIDATE(BIND) EXPLAIN(YES)
    ISOLATION(CS)
    DEGREE(1 )
    REOPT(NONE )
    KEEPDYNAMIC(NO )
    DBPROTOCOL(DRDA )
    ENCODING(EBCDIC )
    IMMEDWRITE(NO )
    FLAG(I);

    Anything stand out?
    Thanks!
    Mike