|
Problem IT02259
|
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 / A50 - 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 10.5 Fix Pack 3 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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 |
First fixed in DB2 Version 10.5 Fix Pack 3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.06.2014
15.09.2014
15.09.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.4
|
 |
