|
Problem IC95387
|
Status: Closed |
DB2 MIGHT TRAP WHILE COMPILING AN SQL STATEMENT ELIGIBLE FOR STAR JOIN |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
You might encounter a trap when a compiling an SQL statement
that satisfies the following conditions:
1. the statement qualifies the starjoin eligibility criteria,
and
2. one or more tables participating in the starjoin apply
subquery
predicates, and
3. an optimization guideline is applied to the statement
In a trap file, you might see the stack traces as following:
...
sqlno_crule_nljn
sqlno_crule_join_choices
sqlno_crule_join_order
sqlno_crule_join_root
sqlno_crule_join
sqlno_starjn
...
Following is an example of a query that satisfies conditions 1
and 2, assuming F is identified as the fact table of the star
join, and D1 and D2 are the dimension tables:
select F.D1, F.D2, F.D3, F.D4, M, substr(D1.C,1,4)
from F, D1, D2
where F.D1=D1.key AND D1.attr<80 AND D1.keya<40 AND
F.D2=D2.key AND D2.attr<80
AND D1.Y not in (select Y from D3 where Y > 10 order by Y); |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* You might encounter a trap when a compiling an SQL statement *
* that satisfies the following conditions: *
* *
* 1. the statement qualifies the starjoin eligibility *
* criteria, *
* and *
* 2. one or more tables participating in the starjoin apply *
* subquery *
* predicates, and *
* 3. an optimization guideline is applied to the statement *
* *
* In a trap file, you might see the stack traces as following: *
* *
* ... *
* sqlno_crule_nljn *
* sqlno_crule_join_choices *
* sqlno_crule_join_order *
* sqlno_crule_join_root *
* sqlno_crule_join *
* sqlno_starjn *
* ... *
* *
* Following is an example of a query that satisfies conditions *
* 1 *
* and 2, assuming F is identified as the fact table of the *
* star *
* join, and D1 and D2 are the dimension tables: *
* *
* select F.D1, F.D2, F.D3, F.D4, M, substr(D1.C,1,4) *
* from F, D1, D2 *
* where F.D1=D1.key AND D1.attr<80 AND D1.keya<40 AND *
* F.D2=D2.key AND D2.attr<80 *
* AND D1.Y not in (select Y from D3 where Y > 10 order by Y); *
****************************************************************
* RECOMMENDATION: *
* Update to 10.5.0.3 *
**************************************************************** |
| Local Fix: |
Avoid applying the optimization guideline. |
| available fix packs: |
DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
Problem Fixed In 10.5.0.3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.08.2013
03.03.2014
03.03.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.3
|
 |
| 10.5.0.3
|
|
