Fixes are available
DB2 Version 9.1 Fix Pack 4a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 4 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
APAR status
Closed as program error.
Error description
SQL0901N on query run.
Local fix
n/a
Problem summary
This problem creates a cyclic corelation QGM because correlated columns could not map to the new destination box properly. To illustrate the problem, let's consider the QGM below: ____ S_____ uaQunp | | pdQunp standing push down UA UA / \ / \ S1 S2 S S correlated to uaQunp After push down, S uaQunp | standing UA / \ S1 S2 \_____\ | push down UA / \ S S <-- still correlated to uaQunp The correlation should have been moved down to the quns under S1 and S2 but as the push down UA is a CSE at this stage it could not resolve to new column until the CSE is broken off later. The strategy is to swap the standing UA and push down UA. That will automatically resolve the correlation issue. Bob has fixed a similar problem in defect 370298. In that problem, the correlation in push down UA is to a table joins to the standing UA (table BT). Bob has fixed the problem by reordering the push down sequence. He called sqlnr_sort_sips_order to order the quns according to correlation dependency. The dependent of the correlation will push down first (in this case, the correlated push down UA) before the parent of the correlation (table BT.) Bob's work does not solve the problem found in this defect. In addition, Bob's work creates a new problem. The quantifiers collected to do the join push down is done sqlnr_test_join_predicate in two passes. The first half of the quantifier array (BTqunpset, renamed to joinQunpList in v9) , collected in the first pass, contains the quantifiers of direct joins to standing UA. The second half contains the quantifiers of indirect joins. By reordering the quantifiers in Bob's work, it may happen that an indirect join attempts to push down to standing UA. This push down attempt will fail because there is no join between the indirect join qun and the standing UA. It requires the 'link table' that joins directly to the standing UA to push down first. The fix in this defect does the following 1. In sqlnr_test_join_predicate, move push down UA to the front of BTqunpset (or joinQunpList in v9) array in the first pass 2. In sqlnr_joinppd_magic_detail (or sqlnr_joinppd_rewrite in v9), remove the call to sqlnr_sort_sips_order 3. In sqlnr_joinppd_rewrite, reverse the walk of joinQunpList from end-to-beginning back to from beginning-to-end 4. In sqlnr_joinppd_rewrite, add checking that if push down UA is correlated to standing UA, swap the UA in the next attempt of joinppd
Problem conclusion
This was fixed in FP4.
Temporary fix
Comments
APAR Information
APAR number
JR26737
Reported component name
DB2 UDB WSE WIN
Reported component ID
5765F3501
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2007-06-01
Closed date
2008-01-02
Last modified date
2008-01-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 UDB WSE WIN
Fixed component ID
5765F3501
Applicable component levels
R910 PSY
UP
Document Information
Modified date:
08 January 2022