PI29723: ABEND04E RC00C90301 AT DSNXRUP2 OFFSET4650 WHEN ACCESS PATH IS LIST PREFETCH AND COLUMN UPDATED IS USE 14/11/21 PTF PECHANGE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00C90301 at dsnxrup2 offset4650 when access path is
  list prefetch and column updated is used in a unique index
  
  
  Additional symptoms and search keywords
  ABEND04E RC00C90301 SQLUPDATE
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of      *
  *                 UPDATE that initially violates the unique    *
  *                 index constraint and needs deferred key      *
  *                 processing.                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: After the application of APAR PI13190   *
  *                      (PTF UI19571/UI19572) an ABEND04E can   *
  *                      occur in DSNXGRDS DSNXRUP2 +4650 or in  *
  *                      DSNXGRDS DSNXRUP2 +299A during the      *
  *                      UPDATE statement that updates a column  *
  *                      that is part of a unique index key.     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The ABEND may occur during the UPDATE statement processing when
  the UPDATE initially violates the unique index constraint and
  needs deferred key processing.
  Additional conditions necessary for the ABEND to occur are:
  1. UPDATE is using one of the following RID accesses for
     qualifying and positioning of the rows to be updated:
       List prefetch (PLAN_TABLE PREFETCH column has value 'L')
       Multiple index access (PLAN_TABLE ACCESSTYPE column has
                              value 'M','MI', or 'MU').
  2. RID list overflow to a work file must occur.
  
  
  An example scenario is as follows:
  
  1. There exists a unique index on one of the columns.
     e.g.:  CREATE TABLE T1 (C1 INT, C2 INT);
            CREATE UNIQUE INDEX IX1 ON T1(C1);
  
  2. A searched UPDATE is issued which:
     a. Updates the unique index column so that it initially
        violates the uniqueness constraint.
     b. Uses List Prefetch as part of an access path.
     c. Qualifies large number of rows to cause RID overflow.
     d. RIDs are overflown to a work file.
  
     e.g.:   UPDATE T1
             SET C1 = C1+1
             WHERE C1 > 0;
  3. At the end of UPDATE processing, an ABEND may occur.
  

Problem conclusion

• DB2 code was modified to prevent the ABEND during the clean
  up of the deferred key UPDATE.
  
  Additional Keywords:  SQLUPDATE DSNXRUP2 OFFSET299A
  

Temporary fix

Comments

APAR Information

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

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

  UI24919 UI24920

Modules/Macros

•    DSNXREOJ DSNXRFF  DSNXRFN  DSNXROJ1 DSNXRSFN
  DSNXRSGB DSNXRT1J DSNXSBUC DSNXSIND DSNXSING
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI24919

     UP15/02/19 P F502 Ø

• RB10 PSY UI24920

     UP15/02/19 P F502 Ø

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.