|
Problem IC67719
|
Status: Closed |
CATALOG VIEWS SYSCAT.MODULES AND SYSCAT.MODULEOBJECTS MAY HAVE COLUMN VALUES BLANK-PADDED WHEN VARCHAR2 COMPATIBILITY IS ON |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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). |
| Problem Summary: |
****************************************************************
* 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 *
**************************************************************** |
| Local Fix: |
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. |
| available fix packs: |
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
|
| Solution |
This was first fixed in version 9.7 FP3 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC67992
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.04.2010
13.09.2010
21.12.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
| 9.7.0.3
|
|
| 9.7.0.3
|
|
