DB2 FOR LUW / DB2FORLUW / 970 - DB2

On a database with VARCHAR2 compatibility (e.g. set 
DB2_COMPATIBILITY_VECTOR=ORA), querying column DIALECT from 
SYSCAT.MODULES and column OBJECTTYPE from SYSCAT.MODULEOBJECTS 
may return values with extra trailing blanks up to the length of 
the column. When using the identified columns as part of a 
predicate in the WHERE clause, the value for comparison needs to 
be blank-padded up to the column length, otherwise, no records 
would be matched. 
 
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 have   * 
* a CASE expression wrapped by a CAST function in their        * 
* definition. The CASE expression, when evaluated under        * 
* VARCHAR2_COMPAT on, has a result type of CHAR(n) because     * 
* then literals are fixed chars so the column value will end   * 
* up with with length of n (the column length) by adding       * 
* trailing blanks even if the actual value is less than n.     * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 9.7 FP3                                   * 
****************************************************************

In the SQL statement referencing the identified columns in the 
WHERE clause, manually blank-pad the value being compared such 
that the total length equals to the column length.

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

This was first fixed in version 9.7 FP3

keiner bekannt / siehe Local-Fix

Vorgänger  : APAR is sysrouted TO one or more of the following: IC67992 
Nachfolger : 

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

08.04.2010
13.09.2010
21.12.2010

9.7.FP3