DB2 FOR LUW / DB2FORLUW / 970 - DB2

In a VARCHAR2 enabled database, a multiple row, multiple column 
VALUES clause may return CHAR instead of the expected VARCHAR. 
For example: 
 
  describe VALUES ('abc', 1),('longer', 2) 
 
   Column Information 
 
   Number of columns: 2 
 
   SQL type              Type length  Column name 
Name length 
   --------------------  ----------- 
------------------------------  ----------- 
   448   CHAR                         6  1 
1 
   496   INTEGER                   4  2 
1 
 
The result type of the first column should be VARCHAR(6) since 
the two CHAR literals 'abc' and 'longer' have different lengths. 
 
When a multiple row VALUES clause is used in an IN predicate, 
wrong results may be returned, e.g.: 
 
create table T ( c1 varchar(10), c2 int ) 
insert into T values ('abc', 1) 
 
select * from T where (c1,c2) IN ( VALUES ('abc',1),('longer',2) 
) 
 
C1         C2 
---------- ----------- 
  0 record(s) selected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 fix pack 11                       * 
****************************************************************

Use UNION ALL instead of a multiple row VALUES clase, e.g.: 
 
  select 'abc', 1 from dual union all select 'longer', 2 from 
dual

First fixed in DB2 version 9.7 fix pack 11

not known / see Local fix

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

04.12.2014
12.10.2015
12.10.2015

9.7.FP11