PM56904: ABEND04E RC00E2000C IN DSNXRSPL .DSNSVSVB +0AC2 AFTER EXCESSIVELY INVOKING CONDITION HANDLERS IN NATIVE SQLPL PROC

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E2000C IN DSNXRSPL .DSNSVSVB +0AC2 (STORAGE
  SHORTAGE OR/AND EXHAUSTION) AFTER EXCESSIVELY INVOKING CONDITION
  HANDLERS IN A NATIVE SQL STORED PROCEDURE
  DB2STGLK/K
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS and DB2 10 for z/OS       *
  *                 SQL procedure and SQL routine users.         *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND04E RC00E2000C in DSNSVSVB +0AC2   *
  *                      called by DSNXRSPL after excessively    *
  *                      invoking condition handlers in SQL      *
  *                      procedures.                             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  ABEND04E RC00E2000C in DSNSVSVB +0AC2 called by DSNXRSPL
  after excessively invoking condition handlers in SQL
  procedures.
  Here is a sample SQL procedure:
  CREATE PROCEDURE TEST (COUNT INT)
  BEGIN
    DECLARE SQLCODE  INTEGER  DEFAULT 0;
    DECLARE SQLSTATE CHAR(5)  DEFAULT '00000';
    DECLARE V1 INTEGER;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
      BEGIN  END;
    DECLARE CURSOR C1 FOR
      SELECT COL1 FROM T1;
    OPEN C1;
    WHILE COUNT > 0 DO
      FETCH C1 INTO V1;
      COUNT = COUNT-1;
    END WHILE ;
  END;
  If table T1 has few rows in it and the input parameter COUNT
  is large, the continue handler will be hit for many times. Each
  time DB2 will allocate large amount of storage to save the SQL
  conditions for each handler invocation. DB2 will hit storage
  shortage problem and abend.
  The problem can be avoided by rewriting the procedure. When
  NOT FOUND has been caught by CONTINUE handler, we can modify
  the procedure to exit the WHILE loop. In this way, we will not
  invoke same handler for too many times, hence will not hit
  the storage problem.
  

Problem conclusion

• DB2 has been fixed not to allocate storage for each
  handler invocation, but reuse the storage allocated.
  The storage shortage problem has been resolved and
  DB2 no longer gets abend on storage shortage.
  
  Additional keywords: SQLPROCEDURE SQLROUTINE SQLPL
                       SQLSTORAGE
  

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:

  UK78074 UK78075

Modules/Macros

• DSNXRPL9 DSNXRSPL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK78074

     UP12/05/23 P F205

• R910 PSY UK78075

     UP12/05/23 P F205

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.