DB2 FOR LUW / DB2FORLUW / A10 - DB2

DB2 query in Stored Procedure may produce wrong result if the 
following conditions are met: 
 
1)There is a table having inequality/IN/OR check constraint 
predicate 
2)The stored procedure uses that table in a query 
3)After compilation of Stored Procedure, the check constraint is 
dropped and/or added new constraint 
 
To reproduce the issue: 
 
db2 -v "create db testdb" 
db2 -v "connect to testdb" 
db2 -v "create table t1 (x int)" 
db2 -v "alter table t1 add constraint c1 check (x between 0 and 
100)" 
db2 -v "create procedure sp1 ( IN x1 INTEGER, IN x2 INTEGER ) 
language sql begin declare curs CURSOR WITH RETURN FOR select * 
from t1 where x between x1 and x2 order by x; open curs; end" 
db2 -v "alter table t1 drop constraint c1" 
db2 -v "alter table t1 add constraint c1 check (x between 101 
and 200)" 
db2 -v "insert into t1 values (150)" 
db2 -v "call sp1 (140, 160)" 
db2 -v "disconnect testdb" 
 
 
The call to sp1 is expected to return the newly inserted row 
with value 150, but instead returns no results. An Explain shows 
that the dropped constraint is still present in the optimized 
statement: 
 
Original Statement: 
------------------ 
DECLARE CURS CURSOR 
     WITH RETURN 
     FOR 
        select * 
        from T1 
        where X between :HV00008 :HI00008 and :HV00009 :HI00009 
        order by X 
 
 
Optimized Statement: 
------------------- 
SELECT Q3.X 
FROM 
   (SELECT Q1.$C0 
   FROM (VALUES 0) AS Q1 
   WHERE (:HV00008 <= :HV00009 SELECTIVITY 1.000000) AND 
(:HV00008 <= 100 
           SELECTIVITY 1.000000) AND (0 <= :HV00009 SELECTIVITY 
1.000000)) AS 
        Q2, ALEXCHEU.T1 AS Q3 
WHERE (Q3.X <= :HV00009) AND (:HV00008 <= Q3.X) 
ORDER BY Q3.X 
 
 
Checking the catalog, it appears that the procedure and package 
are not invalidated as part of the drop constraint, since 
following "alter table t1 drop constraint c1" we have: 
 
> db2 "select substr(ROUTINENAME,1,11) as ROUTINENAME, 
substr(VALID,1,1) as VALID from SYSIBM.SYSROUTINES where 
ROUTINENAME = 'SP1'" 
 
ROUTINENAME VALID 
----------- ----- 
SP1         Y 
 
  1 record(s) selected. 
 
> db2 "select substr(NAME,1,11) as NAME, substr(VALID,1,1) as 
VALID from SYSIBM.SYSPLAN where NAME = 'P1362264217'" 
 
NAME        VALID 
----------- ----- 
P1362264217 Y 
 
  1 record(s) selected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 10.1 Fix Pack 6                               * 
****************************************************************

Recompiling the procedure will resolve this. 
 Steps for the example given in APAR text: 
  db2 -v "drop procedure sp1" 
  db2 -v "create procedure sp1 ( IN x1 INTEGER, IN x2 INTEGER ) 
          language sql begin declare curs CURSOR WITH RETURN FOR 
select * 
          from t1 where x between x1 and x2 order by x; open 
curs; end" 
  db2 -v "call sp1 (140, 160)"

First fixed in DB2 10.1 Fix Pack 6

not known / see Local fix

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

12.10.2016
02.03.2017
02.03.2017