DB2 FOR LUW / DB2FORLUW / 970 - DB2

With VARCHAR2 compatibility mode, the result of a DECODE 
function or a CASE expression may have unexpected trailing 
blanks.  When the length of two result values are different and 
the shorter value is returned, it will be padded with trailing 
blanks up to the length of the longer value. 
 
Here is an example: 
 
db2set DB2_COMPATIBILITY_VECTOR=20 
db2stop 
db2start 
db2 create db sample 
 
db2 connect to sample 
db2 create table t1 (c1 char(13)) 
db2 insert into t1 values('ABCDEFG') 
 
db2 "SELECT '---'||DECODE('1','1','ABC','ABCDEFG')||'---'  FROM 
t1" 
   output:    ---ABC    --- 
 
db2 "SELECT '---'|| Case When '1'='1' Then 'ABC' Else 'ABCDEFG' 
End ||'---' FROM t1" 
   output:    ---ABC    --- 
 
Oracle gives output: ---ABC--- in both cases. 
 
db2 "SELECT '---'||DECODE('1','2','ABCDEFG','EFG')||'---' FROM 
t1" 
   output:     ---EFG    --- 
 
In all cases, the longer length of the decode/case values is 
picked up.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* With VARCHAR2 compatibility mode, the result of a DECODE     * 
* function or a CASE expression may have unexpected trailing   * 
* blanks.  When the length of two result values are different  * 
* and the shorter value is returned, it will be padded with    * 
* trailing blanks up to the length of the longer value.        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 9.7 FixPack4                              * 
****************************************************************

Cast any of the result expression to VARCHAR.

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
 

This was first fixed by V97 FP4

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC69431 
follow-up : 

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

21.05.2010
09.05.2011
09.05.2011

9.7.,
9.7.FP4