home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IT04520 Status: Geschlossen

INCORRECT RESULTS MIGHT BE PRODUCED IF HSJOIN HAS 2 NLJNs ON THE PROBE SIDE
AND HSJN PROBE PUSH DOWN INTO THE SECOND NLJN DONE

Produkt:
DB2 FOR LUW / DB2FORLUW / A10 - DB2
Problembeschreibung:
An SQL might produce incorrect results if all the following 
conditions are met: 
 
(a) The statement leverages a hash join (HSJOIN). 
(b) The plan has 2 nested loop joins (NLJOIN); one on the inner 
of another, and the other on the probe side of the HSJOIN. 
(c) An internal optimization on the probe side of the HSJN 
called a "push down" is implemented. The DB2 trace example below 
shows sqlrihsjnProbePD, which identifies this optimization 
taking place. 
 
We can use the EXPLAIN statement to see if the given SQL 
satisfies the conditions (a) and (b). 
 
The plan would have a graph like as below: 
 
                  HSJOIN 
                 /           \ 
          NLJOIN (x) 
          /         \ 
                     NLJOIN  (y) 
 
Please note that this issue is likely to occur if NLJN(x) outer 
is not a real table, but a TABFNC: SYSIBM.GENROW 
 
Also, we can take db2trc to see if the HSJOIN is running into 
the condition (c), like as below: 
 
1. Turn on the trace. 
db2trc on -f trc.dmp 
 
2. Run the query. 
 
3. Turn off the trace, then format it. 
db2trc off 
db2trc flw trc.dmp trc.flw 
 
4. Search one sqlrihsjnProbePD that immediately returns with 
rc=1 inside sqlriFetch. 
grep -e sqlrihsjnProbePD -e sqlriFetch trc.flw 
59255   | | | sqlriFetch entry 
59417   | | | | | | | | | | | sqlrihsjnProbePD entry 
59420   | | | | | | | | | | | sqlrihsjnProbePD exit [rc = 1] 
50526   | | | sqlriFetch exit 
 
Note that the result might vary depending on the order and 
volume of the data.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.1.0.5.                             * 
****************************************************************
Local-Fix:
Users can disable all the push down by using the following 
setting. 
 
db2set DB2_TCG_DEFAULT_OPTIONS="set disable_pushdown on" 
 
Note that disabling push down can impact on query performance.
Lösung
The problem is first fixed in DB2 version 10.1.0.5.
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
21.09.2014
10.07.2015
10.07.2015
Problem behoben ab folgender Versionen (IBM BugInfos)
Problem behoben lt. FixList in der Version
10.1.0.5 FixList