DB2 FOR LUW / DB2FORLUW / 970 - DB2

DB2 may not apply local predicates with BIGINT, INTEGER, 
SMALLINT on varying-length char strings in an optimal way or 
may return incorrect results with LENGTH function on 
varying-length char strings 
 
There are two problems: 
 
(1) DB2 may not push down local predicates containing an 
expression of the form <function>(<expr>) where 
 
<function> is BIGINT, INTEGER or SMALLINT and 
<expr> is an expression of the varying-length character strings 
(such as VARCHAR, VARGRAPHIC, CLOB, etc.) 
 
to lower operations. This may result in performance degradation 
when upgrading DB2 from earlier releases to Version 9.7. 
 
(2) DB2 may return incorrect results when running the SQL 
statement containing predicates containing an expression of the 
form 
LENGTH(<expr>) where 
 
<expr> is an expression of the varying-length character strings 
of type VARCHAR, VARGRAPHIC, CLOB, NCLOB. 
 
and the predicates are applied above Group By operation. 
 
For example: 
 
db2 => create table t1(c1 varchar(10), c2 int, c3 int); 
db2 => insert into t1 values ('C1', 1, -1), ('C1 ', 2, -2), ('C1 
', -1, 1); 
db2 => select * from t1; 
 
C1         C2          C3 
---------- ----------- ----------- 
C1                   1          -1 
C1                   2          -2 
C1                  -1           1 
 
  3 record(s) selected. 
 
-- Correct result 
db2 => select c1,sum(c2),sum(c3) from t1 group by c1 having 
length(c1) = 2; 
 
C1         2           3 
---------- ----------- ----------- 
C1                   2          -2 
 
  1 record(s) selected. 
 
-- Incorrect result because LENGTH(c1) is applied before Group 
By 
db2 => select * from (select c1,sum(c2),sum(c3) from t1 group by 
c1 ) as q where length(c1)=2; 
 
C1         2           3 
---------- ----------- ----------- 
C1                   1          -1 
 
  1 record(s) selected. 
 
 
This problem occurs in DB2 Version 9.7 GA and Fix Packs 1,2, and 
3

Users Effected: 
All user using V9.7 
 
Problem Description: 
DB2 may not apply local predicates with BIGINT, INTEGER, 
SMALLINT on varying-length char strings in an optimal way or 
may return incorrect results with LENGTH function on 
varying-length char strings 
 
There are two problems: 
 
(1) DB2 may not push down local predicates containing an 
expression of the form <function>(<expr>) where 
 
<function> is BIGINT, INTEGER or SMALLINT and 
<expr> is an expression of the varying-length character strings 
(such as VARCHAR, VARGRAPHIC, CLOB, etc.) 
 
to lower operations. This may result in performance degradation 
when upgrading DB2 from earlier releases to Version 9.7. 
 
(2) DB2 may return incorrect results when running the SQL 
statement containing predicates containing an expression of the 
form 
LENGTH(<expr>) where 
 
<expr> is an expression of the varying-length character strings 
of type VARCHAR, VARGRAPHIC, CLOB, NCLOB. 
 
and the predicates are applied above Group By operation. 
 
For example: 
 
db2 => create table t1(c1 varchar(10), c2 int, c3 int); 
db2 => insert into t1 values ('C1', 1, -1), ('C1 ', 2, -2), ('C1 
', -1, 1); 
db2 => select * from t1; 
 
C1         C2          C3 
---------- ----------- ----------- 
C1                   1          -1 
C1                   2          -2 
C1                  -1           1 
 
  3 record(s) selected. 
 
-- Correct result 
db2 => select c1,sum(c2),sum(c3) from t1 group by c1 having 
length(c1) = 2; 
 
C1         2           3 
---------- ----------- ----------- 
C1                   2          -2 
 
  1 record(s) selected. 
 
-- Incorrect result because LENGTH(c1) is applied before Group 
By 
db2 => select * from (select c1,sum(c2),sum(c3) from t1 group by 
c1 ) as q where length(c1)=2; 
 
C1         2           3 
---------- ----------- ----------- 
C1                   1          -1 
 
  1 record(s) selected. 
 
 
This problem occurs in DB2 Version 9.7 GA and Fix Packs 1,2, and 
3 
 
Problem Summary: 
DB2 MAY NOT APPLY LOCAL PREDICATE WITH BIGINT, INTEGER, SMALLINT 
IN AN OPTIMAL WAY MAY RETURN INCORRECT RESULT ON LENGTH FUNCTION

Upgraded to DB2 Version 9.7 Fix Pack 4

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
 

defect  wsdbu00823817 
CSD     First fixed in DB2 UDB Version 9.7, FixPak 4 
module  engn_sqnr

Upgrade to DB2 Version 9.7, FixPak 4

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

15.03.2011
02.05.2011
02.05.2011

9.7.