Forum

Hallo zusammen,

vielleicht kann ich mit meinen Erkenntnissen zu Erhellung beitragen:

In der Library SDSNMACS(DSN6SPRM), der Vorlage also für den DSNTIJUZ (die DSNZPARM’s) habe ich folgendes gefunden:

[tt]xxxxxxxx.SDSNMACS(DSN6SPRM)          Line 00000589 Col
Command ===>                                                  Scroll =
*   PREDPRUNE – NO OR YES. IF YES, ENABLE PK30857 OPTIMIZER   PK55076*
*               ENHANCEMENTS. DEFAULT IS NO.                         * [/tt]

Eventuell läßt sich damit die beobachtete Verhaltensweise  erklären – PK055076 – Improved Pruning of ALWAYS TRUE and ALWAYS FALSE Predicates. (for v9 fix):

Slow performance for queries with OR predicates and an always false predicate within the OR.

Examples:

[tt]WHERE ‚ABC‘ IN (‚DEF‘,’HIJ‘)
OR INDEX_COL = 123;

WHERE INDEX_COL = 123 OR 54=23;

WHERE NOT_NULL_COL IS NULL
OR INDEX_COL = 123;[/tt]

Note that not all cases of always false predicates are handled, just all literal constants and not nullable columns IS NULL.

Problem conclusion: DB2 has been fixed to better handle always false predicates under an OR context.

A new zparm PREDPRUNE is added with this APAR to enable pruning of the following types of constructs in queries:

• Predicates containing literals under an OR context that always resolve to FALSE; and
• An IS NULL predicate on a column defined as NOT NULL.

The zparm is turned off by default. The recommended setting is to take the default.

Simply applying this PTF will not change DB2 behavior. However, if you wish to enable the pruning described above, set PREDPRUNE to YES.

Gruß
Gernot