Latest versionsfixlist
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
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC98814 Status: Closed

ENHANCE QUERY PERFORMANCE THROUGH PUSHDOWN OF RANGE JOINS THROUGH UNION ALL
VIEW(S)

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
The DB2 query rewrite phase of optimization might be able to 
improve the performance of an SQL statement compiled using 
optimization class 5 if it includes a join with a view that is 
defined with a union all operation, such as 
 
CREATE VIEW view1 
AS 
SELECT ... FROM tabA ... 
UNION ALL 
SELECT ... FROM tabB ... 
 
and the statement references multiple columns from the view in a 
range join with a base table, such as 
 
SELECT 
... 
FROM view1, tabC ... 
WHERE tabC.x between view1.y and view1.z ... 
 
You can identify this scenario by capturing an EXPLAIN of the 
statement and verifying in the optimized statement section or 
access plan that the join is performed after the UNION 
operation. 
 
This enhancement is only available under registry variable 
control. To enable it, issue: 
db2set 
DB2_UNION_OPTIMIZATION="[<other-values>,]ENABLE_UA_RANGE_JPPD=ON 
[,<other-values>]" 
and restart the database manager for settings to take effect.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* all users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to v9.7 FP10 or higher                               * 
****************************************************************
Local Fix:
Use optimization class 7 or higher.
Solution
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
17.01.2014
09.12.2014
09.12.2014
Problem solved at the following versions (IBM BugInfos)
9.7.FP10
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.10 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.