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

Obtain the fix for this APAR.

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