|
Problem IC88630
|
Status: Closed |
SLOWER QUERY PLAN CAN BE MADE ON VARCHAR2 COMPABILITY DB IF EQUA LITY JOIN PREDICATES ON CHAR OR GRAPHIC COLS WITH HOST VARIABLES |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Databases that are created with Varchar2 Compability enabled
may experience poorly performing query access plans for queries
that have equality join predicates on CHAR or GRAPHIC columns
and the same columns are used in an equal predicate with a
VARCHAR or VARGRAPHIC host variable or parameter marker.
Your environment will need to be configured to use Deferred
Prepare via the registry variable
DB2_DEFERRED_PREPARE_SEMANTICS=YES or DB2_COMPATIBILITY_VECTOR
to be impacted.
To verify is you are impacted by this problem, obtain the
access plan and look for a FILTER operation above NLJOIN, MSJOIN
or HSJOIN operation. For example
0.178997
FILTER
( 25)
79.6035
NA
|
111.873
NLJOIN
( 26)
79.5749
NA
and where the operator details for the FILTER contains
residual predicates referencing $Cx columns. For example:
71) Residual Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04
Predicate Text:
--------------
($C2 = $C1) |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Deferred Prepare user *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to db2 Version 9.7 FixPack 8 *
**************************************************************** |
| Local Fix: |
Turn off Deferred Prepare or explicitly cast the host
variables / parameter markers to the type of the column used in
the predicates. |
| available fix packs: |
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 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.7 FixPack 8 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC91273
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.11.2012
08.04.2013
08.04.2013 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP8
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.8
|
 |
