DB2 FOR LUW / DB2FORLUW / 970 - 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

****************************************************************
* USERS AFFECTED:                                              *
* All                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description and use Local fix, or upgrade to       *
* release and fixpak that has this fix.                        *
****************************************************************
* RECOMMENDATION:                                              *
* See Error Description and use Local fix, or upgrade to       *
* release and fixpak that has this fix.                        *
****************************************************************

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)"

not known / see Local fix

forerunner  : IT17311 
follow-up : 

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

12.10.2016
30.05.2017
30.05.2017