DB2 - Problem description
Problem IT17452 | Status: Closed |
WRONG RESULT IN STORED PROCEDURE QUERY WHEN ADD/DROP CHECK CONSTRAINT | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
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. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * See Error Description. Please, install this fix. * **************************************************************** | |
Local 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)" | |
available fix packs: | |
DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows | |
Solution | |
See Error Description. Please, install this fix. | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 12.10.2016 10.10.2017 10.10.2017 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) | |
11.1.1.1 |
![]() |