PI62794: STORED PROCEDURE DOES NOT RETURN RESULT SET

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• After migrating from V10 to V11, no result set was returned
  after calling a stored procedure (NATIVE or EXTERNAL) with a
  DYNAMIC SQL and WITH RETURN TO CLIENT on the DECLARE CURSOR.
  
  In V10, calling the stored procedure succeeded with the expected
  result set.
  
  After migrating to V11, calling the same stored procedure
  succeeded with SQLCODE 0 but with no result set returned.
  
  Additional Keywords;
  SQLSP
  SQLSTOREDPROC
  SQLNATIVESQLPL
  DB2INCORR/K
  INCORROUT
  SQLINCORR
  SQLINCORROUT
  SQLCODE466
  

Local fix

• ALTER PROCEDURE REGENERATE the stored procedure package in
  V11. This workaround does not apply when running in datasharing
  coexistence mode with V10 and V11 CM.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 Users who use WITH RETURN TO      *
  *                 CLIENT cursor in a package bound in          *
  *                 DB2 10.                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: Stored procedures that have WITH RETURN *
  *                      TO CLIENT result sets bound in DB2 10   *
  *                      may no longer return the result sets    *
  *                      after the system is migrated to DB2 11. *
  *                                                              *
  *                      After migrating from DB2 10 to DB2 11,  *
  *                      no result set was returned after        *
  *                      calling a stored procedure that had a   *
  *                      cursor declared WITH RETURN TO CLIENT   *
  *                      for a dynamically prepared query. In    *
  *                      DB2 10, calling the stored procedure    *
  *                      succeeded with SQLCODE +466 and the     *
  *                      expected result set was returned. After *
  *                      migrating to DB2 11, calling the same   *
  *                      stored procedure succeeded with         *
  *                      SQLCODE 0 with no result set returned.  *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 11 codes incorrectly read the saved indicator for RETURN TO
  CLIENT cursor for dynamically prepared statement in DB2 10
  packages. This results in the indicator not being set during
  execution in DB2 11 system.
  

Problem conclusion

• DB2 11 code is modified to read DB2 10 package's indicator for
  WITH RETURN TO CLIENT cursor.
  
  Additional Keywords: SQLSP SQLSTOREDPROC DB2INCORR/K INCORROUT
                       SQLINCORR SQLINCORROUT
  

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:

  UI40341

Modules/Macros

• DSNXEDP  DSNXEDSC DSNXELX
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI40341

     UP16/09/09 P F609

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.