PM88442: IN DB2 10 CM OR NFM, RI UPDATE ATTEMPT OF THE PRIMARY KEY MAY RESULT IN SQLCODE531 FROM DSNXRUP2 13/05/06 PTF PECHANGE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • After applying APAR PM62834 (PTF UK90995), a Referential
    Integrity UPDATE attempt of the primary key in DB2 10 CM OR NFM
    RESULTS IN SQLCODE531 FROM DSNXRUP2 (DSNIRELU):
    .
    Recreation example:
    .
    CREATE TABLE PARENT_TB
      (
       C1 CHAR(10) NOT NULL,
       PRIMARY KEY (C1)
      )
    CREATE UNIQUE INDEX PARENT_IX ON PARENT_TB (C1)
    
    INSERT INTO PARENT_TB (C1) VALUES ('1')
    INSERT INTO PARENT_TB (C1) VALUES ('2')
    .
    CREATE TABLE DEPENDENT_TB
      (
       C1 CHAR(10) NOT NULL,
       FOREIGN KEY REL01 (C1)
       REFERENCES PARENT_TB ON DELETE CASCADE
      )
    .
    INSERT INTO DEPENDENT_TB (C1) VALUES ('1')
    INSERT INTO DEPENDENT_TB (C1) VALUES ('2')
    .
    UPDATE PARENT_TB
      SET C1 = (
                CASE C1
                  WHEN '1' THEN '2'
                  WHEN '2' THEN '1'
                  ELSE C1 END
               )
      WHERE C1 IN ('1', '2')
    .
    This SQL UPDATE failed with SQLCODE -531 when it should have
    been successful (by the end of the update, the parent key values
    being updated were still present).
    .
    The impact of having or not having PM62834/UK90995 applied
    are as follows:
    ---------------------------------------------------------------
    With the bad APAR PM62834/UK90995 applied, in DB2 10 CM or NFM,
    when a single SQL UPDATE statement updates several parent keys,
    if any dependent rows of those parent keys exists in a child
    table, the SQL UPDATE may get SQLCODE -531 when it should be
    successful.
    .
    Without the APAR PM62834/UK90995 applied, in DB2 10 CM, no
    impact (the APAR's INCLUDE column index functionality cannot be
    used until DB2 10 NFM), but in DB2 10 NFM, needless to say,
    usage of the INCLUDE functionality would be susceptible to the
    scenarios described in the PM62834/UK90995 closing text,
    including potentially leaving some child table rows orphaned, as
    explained in that closing text.
    ..
    Update for May 10, 2013:
    ------------------------
    Please note that this PE fixer will not supersede the bad
    APAR PM62834 (PTF UK90995), in order to avoid significant
    delay in shipment (it would have meant a significant basing
    chain otherwise).
    ..
    

Local fix

  • Until this fix is applied, the only known way to bypass this
    problem would be to temporarily create a grandparent table
    (parent to the parent table) - a repeated SQL UPDATE should then
    work. The relationship between the parent and grandparent tables
    can optionally be made non-enforced (e.g. ON DELETE CASCADE NOT
    ENFORCED), in which case the grandparent table can remain empty.
    After the fix is applied, the grandparent table can be dropped.
    .
    However, creating a grandparent table may not be fesible,
    depending on the business expectations for the affected set of
    tables.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 (CM or NFM) for z/OS users of     *
    *                 SQL UPDATE of parent key in a referential    *
    *                 integrity (RI) relationship                  *
    ****************************************************************
    * PROBLEM DESCRIPTION: After applying APAR PM62834 (UK90995),  *
    *                      SQLCODE531 -531 issued on SQL UPDATE of *
    *                      parent key in referential integrity     *
    *                      relationship, even though all rows in   *
    *                      dependent table still had valid parent  *
    *                      rows in parent table                    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    After applying APAR PM62834/PTF UK90995 in DB2 10 CM or NFM,
    when a single SQL UPDATE statement updated several parent keys,
    if the parent key values being updated had any dependent rows
    in a dependent table, SQLCODE -531 was issued even when by the
    end of the SQL UPDATE the parent keys were still present.
    
    Example:
    ----------------------------------------------------------------
    CREATE TABLE PARENT_TB
      (
       C1 CHAR(10) NOT NULL,
       PRIMARY KEY (C1)
      )
    
    CREATE UNIQUE INDEX PARENT_IX ON PARENT_TB (C1)
    
    INSERT INTO PARENT_TB (C1) VALUES ('1')
    INSERT INTO PARENT_TB (C1) VALUES ('2')
    
    
    CREATE TABLE DEPENDENT_TB
      (
       C1 CHAR(10) NOT NULL,
       FOREIGN KEY REL01 (C1)
       REFERENCES PARENT_TB ON DELETE CASCADE
      )
    
    INSERT INTO DEPENDENT_TB (C1) VALUES ('1')
    INSERT INTO DEPENDENT_TB (C1) VALUES ('2')
    
    
    UPDATE PARENT_TB
      SET C1 = (
                CASE C1
                  WHEN '1' THEN '2'
                  WHEN '2' THEN '1'
                  ELSE C1 END
               )
      WHERE C1 IN ('1', '2')
    
    This SQL UPDATE failed with SQLCODE -531 when it should have
    been successful (by the end of the update, the parent key values
    being updated were still present).
    ----------------------------------------------------------------
    
    It should be noted that even though the bad APAR PM62834 was
    documenting a fix for DB2 10 NFM's INCLUDE column index
    function, the scenario that exposes the APAR need not be related
    to that function at all and can happen in DB2 10 CM as well.
    
    The root cause of this problem is an incorrect assumption in the
    DB2 deferred key processing code, which bypassed the checking of
    parent key index that finds out if the pre-update parent key
    values are still present in the parent table. The incorrect code
    proceeded to check for dependents as if the pre-update parent
    key values were no longer present.
    

Problem conclusion

  • Code has been changed to properly check for the presence of
    old parent keys during SQL UPDATE with referential integrity
    implications.
    
    Additional keywords: SQLUPDATE
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM88442

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    YesPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-05-03

  • Closed date

    2013-06-10

  • Last modified date

    2013-07-02

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

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

    UK94997

Modules/Macros

  •    DSNIRICH
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK94997

       UP13/06/25 P F306 ®

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM88442

Modified date:

2013-07-02

Translate my page

Machine Translation

Content navigation