|
Problem IC91273
|
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 / A10 - 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: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 10.1 fixpack 3 *
**************************************************************** |
| 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 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in DB2 version 10.1 fixpack 3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 03.04.2013
03.10.2013
03.10.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.3
|
 |
| 10.1.0.3
|
|
