IBM Support

IT17452: WRONG RESULT IN STORED PROCEDURE QUERY WHEN ADD/DROP CHECK CONSTRAINT

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error 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.
    

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

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * See Error Description. Please, install this fix.             *
    ****************************************************************
    

Problem conclusion

  • See Error Description. Please, install this fix.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT17452

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-10-12

  • Closed date

    2017-10-10

  • Last modified date

    2017-10-10

  • APAR is sysrouted FROM one or more of the following:

    IT17311

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020