Oppolzer - Informatik / Blog

As the OP tells that he does the re-explain using a tool
which does the re-explain from the catalog, and the access path
shows index A without the re-explain and index B after the re-explain,
I guess the tool will do the things with the host variables and parameter markers right
(that is, replacing all host variables in the query by question marks before issuing
the explain).

IMO the access paths of REBIND and EXPLAIN should be the same, so the
only solution which comes to mind are things like other settings for DEGREE.
DEGREE of REBIND is 1 - as shown, and DEGREE for the EXPLAIN
comes from the CURRENT DEGREE special register, AFAIK. Maybe these
settings are different ?

Kind regards

Bernd



Am 25.06.2012 17:05, schrieb W.J.:
> S.
> You have to use parameter markers everywhere, where you have
> host-variables to get the same access path. But I don't know, if the
> literals are also replaced by parameter markers.
> But I wonder, why it's a singleton select. At most one row will
> qualify here?
> Maybe the clause FETCH FIRST 1 ROW ONLY should be added to the
> statement to get the same access path.
>
> Mit freundlichen Grüßen
> W.J.
>
>
>
>
> ------------------------------------------------------------------------
> *Von:* S.R.
> *Gesendet:* Montag, 25. Juni 2012 16:37
> *An:* db2-l@lists.idug.org
> *Betreff:* [DB2-L] - RE: [MF DB2 V10] Rebind vs Explain - access path
> differences?
>
> So when you do the EXPLAIN natively, you are replacing the
> host-variables with "?" -- which means (AFAIR) that DB2 will use a
> default filter factor, and this is different from REBIND.
>
> You can look at REASON in DSN_STATEMNT_TABLE to verify this too. This
> could be one of the cases where REOPT is useful.
>
> *From:*M.J.
> *Sent:* Monday, June 25, 2012 9:26 AM
> *To:* db2-l@lists.idug.org
> *Subject:* [DB2-L] - RE: [MF DB2 V10] Rebind vs Explain - access path
> differences?
>
> S. - 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!
> M.
>
>
>
>
> From: D.S.
> To: db2-l@lists.idug.org <mailto:db2-l@lists.idug.org>
> Date: 06/25/2012 08:21 AM
> Subject: [DB2-L] - RE: [MF DB2 V10] Rebind vs Explain - access path
> differences?
>
> ------------------------------------------------------------------------
>
> Look at bind options like isolation and degree. Are they the same as
> their corresponding special registers when the explain is done?
>
>
>
>
> On Jun 25, 2012, at 9:12 AM, M.J. wrote:
>
> Has anyone ever encountered a situation where an Explain returns a
> different access path than a Rebind w/Explain?
>
> We have a query within a COBOL program that is doing exactly that...
> and can't figure out why...
> The cost of both access paths are very close.
>
> Using information in the explain tables (from the rebind w/explain),
> we see IndexA being used.
> When issuing an Explain on the stmt in question, we see IndexB being
> used.
> A rebind won't use IndexB... yet a new explain always choses Index B.
>
> We've used multiple tools and always get the same results (for example
> - using data studio - generating the access path from the catalog
> without doing a re-explain - shows IndexA being used, but generating
> the access path using the re-explain option shows IndexB).
>
> Our solution in this case is to create a new index which a much better
> access path than either of existing paths... but the question remains
> - why would a Rebind consistently chose a different access path from
> an explain of the exact same stmt?
>
> Thanks!
> Mike
>
> -----End Original Message-----
>
> -----End Original Message-----