PI15274: SQLCODE805 WITH CALL STORED PROC, AFTER ALTER PROCEDURE REGENERATE WAS EXECUTED

APAR status

• Closed as fixed if next.

Error description

• There is no serialization mechanism between Alter Procedure
  REGENERATE (DDL) and CALL stored procedure and that results in
  SQLCODE805
  
  This ApAR is opened to document problem, which development
  is going to fix in future release.
  

Local fix

• -STOP PROCEDURE xxx ACTION (REJECT) .....
  ALTER PROCEDURE xxx REGENERATE ...
  
  -START PROCEDURE xxx   ......
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 users of native SQL procedures and       *
  *                 ALTER PROCEDURE REGENERATE.                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -805 may be returned for        *
  *                      the CALL :HV statement for native       *
  *                      SQL procedures if ALTER                 *
  *                      PROCEDURE REGENERATE is executed        *
  *                      at the same time.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This is a timing issue. When DB2 processes the CALL statement,
  it retrieves the routine info from either the catalog table or
  a cache table. In the meantime, ALTER PROCEDURE REGENERATE
  executes and changes the routine info in the catalog table.
  So the routine info DB2 just retrieved and saved is
  out-of-date. When the stored procedure package
  is executing, the contoken DB2 saved for the routine doesn't
  match the one in the catalog and DB2 issues SQLCODE -805.
  A serialization machanism is needed between
  ALTER PROCEDURE REGENERATE and CALL stored procedure to
  eliminate the timing window.
  

Problem conclusion

Temporary fix

Comments

• The fix for this problem is too risky to deliver in an
  APAR. It will be fixed in a future release.
  
  Additional Keywords:
  SQLNATIVESQLPL, SQLALTER, SQLCODE805
  

APAR Information

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

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

Fix information

Applicable component levels

• RA10 PSN

     UP