DB2 FOR LUW / DB2FORLUW / 980 - DB2

On a database created with Oracle compatibilty (e.g. 
DB2_COMPATIBILITY_VECTOR=ORA), query against catalog view 
SYSCAT.MODULES may return unexpected result set if any predicate 
references the column DIALECT. In the case where the length of 
the DIALECT value is less than 10, the view text definition 
causes the value to be blank-padded in the end. 
 
For example, 
 
db2 "select  DIALECT from SYSCAT.MODULES where 
MODULENAME='PACKAGE2' AND DIALECT = 'PL/SQL'" 
DIALECT 
---------- 
  0 record(s) selected. 
 
db2 "select  DIALECT from SYSCAT.MODULES where 
MODULENAME='PACKAGE2' AND DIALECT = 'PL/SQL    '" 
 
 
DIALECT 
---------- 
PL/SQL 
 
  1 record(s) selected. 
 
Some side-effects include hitting SQL0204N error when deploying 
a PL/SQL package after copying it from the data source explorer 
to data project explorer on Optim Development Studio (ODS).

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Catalog views SYSCAT.MODULES and SYSCAT.MODULEOBJECTS havea  * 
* CASE expression wrapped by a CAST function intheir           * 
* definition. The CASE expression, when evaluatedunder         * 
* VARCHAR2_COMPAT on, has a result type of CHAR(n)because the  * 
* literals are fixed chars so the column valuewill end up with * 
* with length of n (the column length) byadding trailing       * 
* blanks even if the actual value is less thann.               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 9.8 fixpack 3                             * 
****************************************************************

Manually blank-pad the string being compared to column DIALECT 
until the length is 10.

DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

This was first fixed in V98 FP3

not known / see Local fix

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

18.04.2010
10.01.2011
10.01.2011

9.8.FP3