|
Problem IC97864
|
Status: Closed |
FORCING APPLICATION CAN TAKE A LONG TIME TO TAKE EFFECT WHEN OPTIMIZER COMPILES A COMPLEX QUERY USING GREEDY JOIN ENUMERATION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
"FORCE APPLICATION" command can take a long time to take effect
when DB2 optimizer is compiling a complex query using greedy
join enumeration method. Complex queries refer to queries that
have a lot of table joins (for example, more than 30 tables) in
one query block.
If "FORCE APPLICATION" command doesn't take effect for a long
time (for example, more than 5 minutes) for an application, you
can dump the associated DB2 agent's stack. If the stack contains
function "sqlno_crule_join", then DB2 optimizer is in the
process of join enumeration (either dynamic programming or
greedy).
The following are some common scenarios of when greedy join
enumeration is used. Note that this is not a complete list.
- when optimization class is 0, 1, or 2
- when stmtheap size is too small to complete dynamic
programming join enumeration
- when DB2_REDUCED_OPTIMIZATION registry variable is set to a
number and some additional conditions are satisfied. Please
refer to DB2 InfoCentre for the conditions. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 10 or higher *
**************************************************************** |
| Local Fix: |
|
| Solution |
First fixed in Version 9.7 Fix Pack 10 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.11.2013
08.12.2014
08.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
|
 |
