DB2 FOR LUW / DB2FORLUW / 980 - DB2

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.

**************************************************************** 
* 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.8 and Fix Pack 0                    * 
****************************************************************

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'!'

Problem was first fixed in DB2 Version 9.8 and Fix Pack 0

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

16.11.2010
19.05.2011
19.05.2011

9.8.