|
Problem IC72599
|
Status: Closed |
POOR PERFORMANCE WHEN LIKE PREDICATE HAS MIXED GRAPHIC AND NON-GRAPHIC ARGUMENTS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
A LIKE predicate with an escape-expression does not use an
available index when the match-expression is a GRAPHIC or
VARGRAPHIC column, and both the pattern-expression and
escape-expression are CHAR or VARCHAR type. For example:
... WHERE graphiccol LIKE 'abc!_%' ESCAPE '!'
Any index on column graphiccol will not be exploited possibly
resulting in poor query performance. This only applies to a
unicode database, since mixing of graphic and non-graphic types
is not allowed otherwise. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Internally we treat LIKE just as any other function call, we *
* follow the function resolution rules to find the best fit *
* function. We have four (internal) versions of LIKE: *
* *
* LIKE(VC,VC) *
* LIKE(VC,VC,VC) *
* LIKE(VG,VG) *
* LIKE(VG,VG,VG) *
* *
* Following the documented function resolution rules, we map *
* these calls as follows: *
* *
* VG LIKE VC -> LIKE( VG, VARGRAPH(VC) ) best fit is *
* LIKE(VG,VG) *
* VG LIKE VC ESCAPE VC -> LIKE( VARCHAR(VG), VC, VC ) this is *
* the best fit as there is only one cast *
* *
* The behaviour makes sense if we think of LIKE as a function *
* call. This APAR will try to avoid a cast on the first *
* argument since that is the only argument that can reference *
* a column, and hence a cast will hide the column index. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 and Fix Pack 4 *
**************************************************************** |
| Local Fix: |
Cast the pattern-expression and/or escape-expression to graphic
type. For constants, a G prefix can be added:
... WHERE graphiccol LIKE G'abc!_%' ESCAPE G'!' |
| available fix packs: |
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 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in DB2 Version 9.7 and Fix Pack 4 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.11.2010
28.04.2011
28.04.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
