Oppolzer - Informatik / Blog

T.P. wrote:

Hello Bernd,

I can only answer for DB2 for z/OS.

To answer the question that you received - there is no reduced optimization for
dynamic SQL compared with static. I am not sure who the IBM specialist was - but
the only thing I can think they were referring to was the enhancements that came
in V8 to reduce the prepare overhead for queries with a larger number of tables.
The switch for these was zparm TABLES_JOINED_THRESHOLD, and there were other
zparms - MXQBCE (max query block cost entries - which didn't mean much to anyone
outside DB2 optimizer development), MAX_OPT_STOR (max storage a prepare was
allowed to consume), MAX_OPT_ELAP (max elapsed time for prepare), MAX_OPT_CPU
(max CPU used for prepare). These have been gradually phased out because DB2
development eventually found a good balance of "time to find the lowest cost
access path" vs "exhaustively searching all viable plans".

However, these zparms were applicable to static bind or dynamic prepare.

With regard to documentation - as I inferred above, these never were really
intended for customers to individually tweak these. So there was never any real
externalization of what was done.

I think many of the questions you have stem from the assumption that dynamic
prepare is reduced compared to static bind.

With regard to the "hidden" tables. These became unhidden in V8 with the advent
of visual explain, and the simplest way to get these tables is by using the free
data studio tool which will create the tables for you. Otherwise they are
documented in the V10 Managing Performance Guide, or V9 PMTG. The table you want
is DSN_PREDICAT_TABLE.

Regards

T.P.


In Reply to bernd oppolzer:

    Hello DB2 list,

    I just finished my DB2 performance classes (I am the teacher);
    I told the students about DB2 physics, DB2 access paths,
    the optimizer, statistic values in the catalog tables, and I did ca.
    100 SQL examples and analyzed the EXPLAIN results with the students -
    I looked at the effects of clustered vs. unclustered indexes etc. etc. and
    told them about the pros and cons of the different SQL variants.

    Worked very good so far. But there was some question, which I could not
    answer in detail:

    if a statement is prepped during dynamic SQL execution - is there the same
    "deep" analysis of all the access possibilities executed as in a static bind?
    I recall, that an IBM specialist told us some years ago, that the analysis of the
    possible access strategies is somehow reduced during dynamic prepare,
    because there is a tradeoff between accuracy of the access plan and
    time of the preparation - it is done during run time !!

    My question is: are there any details concerning this topic in IBM
    documentation?

    And: if I do EXPLAIN, i will sure get the access plan of the static bind.
    Is there a way to find out what the result of the "reduced" prepare during
    dynamic SQL preparation will be? Maybe a special option on EXPLAIN?
    I don't think so ...

    I know about the problems with the host variables and REOPT(VARS),
    but that is not the question ... the question is, even without host variables,
    if there are differences between static binds (during compile time etc.)
    and dynamic prepares - because DB2 doesn't spend the same time in
    both situations?

    Another question: I recall, that there is a - hidden - possibility, even in DB2
    for z/OS (not only in DB2 for VM/VSE), to get more tables populated by EXPLAIN,
    for example a table with filter factors. Is this true, and can somebody tell me
    the details?

    Kind regards

    Bernd