IBM Support

IT28782: ALTER TABLE DROP CONSTRAINT DOES NOT INVALIDATE THE DEPENDENT PACKAGE AND CAUSES WRONG RESULTS TO BE RETURNED

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • When there is a constraint defined on a table and a query makes
    use of the constraint for data partition elimination, a
    dependency
    on the constraint should be recorded for the query package.  Db2
    failed to record the dependency.  The result is that a row
    inserted
    after dropping the constraint, that would have violated this
    constraint,
    may not be returned by the query.  For example:
    
        CREATE TABLE T ( C1 INT NOT NULL, C2 INT NOT NULL )
                 PARTITION BY RANGE(C2)
                 (PART "P01" STARTING(1) ENDING(10),
                  PART "P02" STARTING(11) ENDING(20) );
    
        CREATE INDEX I ON T ( C2 ASC, C1 ASC) PARTITIONED;
    
        ALTER TABLE T
          ADD CONSTRAINT C CHECK (C2 BETWEEN 1 AND 10)
          ENFORCED ENABLE QUERY OPTIMIZATION;
    
        -- Returns 0 rows as expected since table is empty.  Query
        -- package is stored in the dynamic package cache
        SELECT * FROM T WHERE C1 = 99;
    
        C1          C2
        ----------- -----------
          0 record(s) selected.
    
        -- Drop constraint, this should invalidate dynamic package
        ALTER TABLE T DROP CONSTRAINT C;
    
        -- Insert value outside of dropped constraint;
        INSERT INTO T VALUES ( 99, 11);
    
        -- Same query as before, now it should return the newly
        -- inserted row, but does not because Db2 failed to
        -- record dependency on the constraint
        SELECT * FROM T WHERE C1 = 99;
    
        C1          C2
        ----------- -----------
          0 record(s) selected.
    

Local fix

  • For static queries (e.g. embedded C or SQL procedures), manually
    rebind the corresponding package(s).  For dynamic queries,
    either flush the dynamic package cache (FLUSH PACKAGE CACHE
    DYNAMIC), or close and re-establish database connection.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher                *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT28782

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-04-12

  • Closed date

    2020-01-16

  • Last modified date

    2020-01-16

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

  • 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

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 January 2020