PK71579: INCORRECT SQLCODE -501 FOR USERS OF STORED PROCEDURES WITH RESULT SETS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Scenario is where a stored procedure (A) is called, then calls
  another stored procedure (B) which returns a result set back to
  A, then A terminates but then A is called again to continue
  processing the result set from B.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 for z/OS V8 Users of DB2 Stored          *
  *                 Procedures that return Result Sets           *
  ****************************************************************
  * PROBLEM DESCRIPTION: Application received SQLCODE -501 when  *
  *                      attempting to CLOSE an ALLOCATED cursor *
  *                      for a stored procedure result set       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An DB2 internal control block pointer that is used for stored
  procedure package structures and related to cursor statements
  was incorrectly set by DB2 in a specific stored procedure
  scenario while processing an ALLOCATED CURSOR associated with
  a result set returned from a stored procedure.
                                                                .
  On a subsequent CLOSE CURSOR statement issued for that
  ALLOCATED CURSOR by the calling application, DB2 then failed
  to use the correct DB2 control block and DB2 issued the
  SQLCODE501 from DB2 csect DSNXERT.
                                                                .
  This specific SQLCODE -501 occurred in the following stored
  procedure scenario:
                                                                .
    MAIN
      // open result set and perform first fetch
      SQL CALL SP1()
        SQL CALL SP2()
          DECLARE C1 CURSOR WITH RETURN FOR ...
          OPEN C1
          RETURN
        END SP2
        ASSOCIATE LOCATOR (:rsl) WITH PROCEDURE SP2
        ALLOCATE C1 CURSOR FOR RESULT SET :rsl
        FETCH FROM C1 INTO :hv
        RETURN
      END SP1
      RETURN
      // SP1 does not completely consume or close SP2's
      // result set before returning to the caller (MAIN),
      // and on subsequent MAIN CALLs to SP1, SP1 attempts
      // to continue FETCHing SP2's result set "without"
      // re-invoking SP2 again (i.e., no next CALL SP2)
      // to build a new result set.  Later when SP1 attempts
      // to CLOSE the ALLOCATED cursor for SP2's result set,
      // the -501 occurred. Other negative SQLCODEs are
      // possible for other SQL requests using that
      // ALLOCATED cursor in this nested scenario.
                                                                .
  IBM strongly warns against using this non-standard programming
  approach for re-accessing inner SQL nested stored procedure
  levels from a CALLer without re-invoking the stored procedures
  at each of these nested SQL levels when the outer SQL level has
  been created again via a new CALL.
                                                                .
  IBM does not support this method because the results are
  unpredictable and the stored procedure result sets from
  the nested SQL levels are not guaranteed to persist or be
  protected once the outer SQL level is created again.
  Moreover, there are cases where this will not and can not be
  be supported in NFM where multiple stored procedure instances
  could be created on multiple calls to the same stored procedure
  where result sets are returned by any stored procedure in
  the nested sequence.
                                                                .
  IBM strongly warns users to re-invoke the stored procedures
  at the nested SQL levels whenever an outer SQL level has been
  re-created. IBM also warns users to have the calling
  applications completely consume or CLOSE result sets upon
  returning from a stored procedure and not depend upon those
  same result sets persisting across repeated calls to the
  stored procedure.
                                                                .
                                                                .
  

Problem conclusion

• DB2 code has been modified to correctly set the DB2 internal
  control block pointer in the context of processing an
  ALLOCATED CURSOR.
                                                                .
  Additional Keywords
  SQLCODE501
  sqlsp
  SQLSTOREDPROC
  ALLOCATE
  

Temporary fix

• AK71579
  

Comments

APAR Information

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

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

  UK40998

Modules/Macros

• DSNXERT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK40998

     UP08/11/07 P F811

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.