PM00860: AB04E DSNXXXXX . DSNSVBK +0994 AFTER ALLOCATE CURSOR ACCESS OF WITH HOLD STORED PROC RESULT SETS FOR REPEATED INVOCATIONS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• After repeated invocations of a stored procedure where the
  stored procedure result sets are not closed, the ALLOCATE
  CURSOR statement is used to access WITH HOLD result set,
  a COMMIT is issued then later followed by an SQL ROLLBACK,
  the following or similar abend can occur when ROLLBACK is done:
   ABEND04E RC00E20005 at DSNXxxxx . DSNSVBK +0994
  This abend may be followed by forced DB2 termination.
  Other abend symptoms are possible
  The abend resulted from a 1 bit overlay in the DB2 AGL
  storage for the currently running DB2 thread when using many
  multiple stored procedure instances that result from not
  closing stored procedure result sets between CALLs to that
  stored procedure.
  additional symtpom:
  1. DSNV086E  DB2 ABNORMAL TERMINATION REASON 00D94001 RC00D94001
  Abend0c4 pic4 DSNSVBK +10A8 OFFSET10A8 at UK43267
  DSNXRPUF called DSNSVBK to free a block. The current block size
  of a freed PF block was overlaid by one bit. x'40' bit.
  DB2STGLK/K
  

Local fix

• Avoid ROLLBACK if possible.  Be sure to CLOSE stored procedure
  WITH HOLD result sets before re-invoking the stored procedure.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users of ALLOCATE CURSOR to access   *
  *                 stored procedure WITH HOLD result sets,      *
  *                 followed by COMMIT then ROLLBACK             *
  ****************************************************************
  * PROBLEM DESCRIPTION: After an application repeatedly invoked *
  *                      the same stored procedure and used      *
  *                      ALLOCATE CURSOR statement to access     *
  *                      the stored procedure WITH HOLD result   *
  *                      sets, subsequently requesting COMMIT    *
  *                      followed later by a ROLLBACK,           *
  *                      the following or similar abends may     *
  *                      occur during the SQL ROLLBACK:          *
  *                       AB04E RC00E20005 DSNSVBK + 0994        *
  *                       AB0C4 RC00000004 DSNSLD1 DSNSVBK 10A8  *
  *                                                            . *
  *                      This abend may be followed by forced    *
  *                      DB2 termination.  Other abend symptoms  *
  *                      and offsets possible. These abends are  *
  *                      result of 1-bit overlay of DB2          *
  *                      internal storage.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An application scenario did all of the following:
   (1) repeatedly invoked the same stored procedure within nested
       stored procedure scenario (nested CALLs)
   (2) used ALLOCATE CURSOR statement to access the stored proc's
       WITH HOLD result sets
   (3) calling application did not consistently CLOSE
       the WITH HOLD result sets after EACH call to the stored
       proc that returns the result sets, as IBM has strongly
       recommended starting in DB2 V8.  This results in
       inconsistent result set management and persistent multiple
       active stored procedure and result set instances with each
       CALL.
       (Note: waiting to close the result sets until after ALL
       multiple CALLs to the same stored proc are completed is not
       the same as CLOSEing after "each" CALL instance).
   (4) requested COMMITs on return from the outer-most stored proc
       then later issued an SQL ROLLBACK (either explicitly
       requested by the application scenario or implicitly
       requested via the user chosen DB2 thread/connection/
       transaction management interface)
  .
  During the SQL ROLLBACK, the following or similar abends later
  occurred during the SQL ROLLBACK:
   ABEND04E RC00E20005 at DSNXxxxx . DSNSVBK + 0994
   ABEND0C4 RC00000004 at DSNSLD1 . DSNSVBK + 10A8
  .
  This abend may be followed by forced DB2 termination.
  The abend rarely occurred even when the described scenario is
  repeated many times over long periods of time (months, weeks).
  However, once the abend is first hit in a specific workload
  environment, then it is likely to occur more than once.
  .
  The abend was the result of a previous 1-bit overlay of the DB2
  Agent Local ( AGL ) storage of the same DB2 thread that was
  running the described scenario.  The overlay did not occur
  on a different DB2 thread's AGL storage.
  .
  DB2 Development determined that the 1-bit overlay occurred as
  a result of residual pointers to procedure instances storage
  unexpectedly & previously freed before the ROLLBACK flow that
  abended.
  .
  

Problem conclusion

• DB2 code was changed to clear the specific DB2 internal ptrs to
  program structures for stored procedures and their calling
  programs before these related storage blocks are freed,
  particularly for the instances cases.
  This prevents residual stg usage of other related blocks, not
  only prevent the 1-bit overlay, but also prevent accessing
  other freed storage blocks associated with the stored proc and
  result set instances.
  .
  IBM recommends apar PM00860 even for non-nested stored proc
  scenarios.
  .
  Additional search keywords:  1bit SMCOVERLAY
  

Temporary fix

• AM00860
  

Comments

APAR Information

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

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

  UK52863 UK52864

Modules/Macros

• DSNXECLC DSNXECW  DSNXECWA DSNXECWC DSNXECWU
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK52863

     UP09/12/30 P F912

• R910 PSY UK52864

     UP09/12/30 P F912

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.