|
Problem IC64042
|
Status: Closed |
OPTIMIZER MAY STILL GENERATE A SORT FOR JOINS WHEN SETTING EITHE R NO_SORT_NLJOIN OR NO_SORT_MGJOIN FOR DB2_REDUCED_OPTIMIZATION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When the DB2_REDUCED_OPTIMIZATION registry variable is set to
NO_SORT_NLJOIN, the optimizer may still generate query plans
that force sorts for nested loop joins (NLJN). Likewise, when
the DB2_REDUCED_OPTIMIZATION registry variable is set to
NO_SORT_MGJOIN, the optimizer may still generate query plans
that force sorts for merge scan joins (MSJN).
.
The DB2 Information Center contains futher information about the
DB2_REDUCED_OPTIMIZATION registry variable:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?t
opic=/com.ibm.db2.luw.admin.regvars.doc/doc/r0005664.html
.
This problem does not occur if both NO_SORT_NLJOIN and
NO_SORT_MGJOIN are set.
. |
| Problem Summary: |
When the DB2_REDUCED_OPTIMIZATION registry variable is set to
NO_SORT_NLJOIN, the optimizer may still generate query plans
that force sorts for nested loop joins (NLJN). Likewise, when
the DB2_REDUCED_OPTIMIZATION registry variable is set to
NO_SORT_MGJOIN, the optimizer may still generate query plans
that force sorts for merge scan joins (MSJN).
.
The DB2 Information Center contains futher information about the
DB2_REDUCED_OPTIMIZATION registry variable:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?t
opic=/com.ibm.db2.luw.admin.regvars.doc/doc/r0005664.html
.
This problem does not occur if both NO_SORT_NLJOIN and
NO_SORT_MGJOIN are set. |
| Local Fix: |
In order to workaround the problem, you can set the registry
setting to both, eg:
.
db2set DB2_REDUCED_OPTIMIZATION=NO_SORT_NLJOIN,NO_SORT_MSJOIN
.
These types of sorts can be useful for improving performance.
Care must be take when considering the use of the NO_SORT_NLJOIN
and NO_SORT_MGJOIN options, as performance can be severely
impacted. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
module engn_sqno
Fixed >= v97 fpk1 |
| Workaround |
In order to workaround the problem, you can set the registry
setting to both, eg:
.
db2set DB2_REDUCED_OPTIMIZATION=NO_SORT_NLJOIN,NO_SORT_MSJOIN
.
These types of sorts can be useful for improving performance.
Care must be take when considering the use of the NO_SORT_NLJOIN
and NO_SORT_MGJOIN options, as performance can be severely
impacted. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
27.01.2010
27.01.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FPk1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
