DB2 FOR LUW / DB2FORLUW / 970 - DB2

You may obtain error SQL0727N after performing CREATE OR REPLACE 
of an existing SQL function.  The following conditions must 
exist for the error to appear; however, the error might not 
appear, even if all of the conditions are met: 
 
- The database contains multiple occurrences of a function F. 
- Each occurrence of F is LANGUAGE SQL. 
- Each occurrence of F is a compiled function.  That is, the 
body the function starts with the BEGIN keyword, and not with 
BEGIN ATOMIC. 
- Each occurrence of F has the same schema, same function name, 
and same number of parameters.  The types of the parameters will 
vary from occurrence to occurrence; meaning, each occurrence of 
the function will have a different signature. 
- One of the occurrences of F has been invalidated. 
 
- CREATE OR REPLACE FUNCTION G, which calls one of the 
occurrences of F that has been invalidated, forcing F to be 
implicitly revalidated.  The CREATE OR REPLACE statement may 
subsequently return with SQL0727N, similar to the following: 
 
SQL0727N  An error occurred during implicit system action type 
"3". 
Information returned for the error includes SQLCODE "-440", 
SQLSTATE "42884" 
and message tokens "G|FUNCTION".  LINE NUMBER=4.  SQLSTATE=56098

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* You may obtain error SQL0727N after performing CREATE        * 
* ORREPLACEof an existing SQL function.  The following         * 
* conditions mustexist for the error to appear; however, the   * 
* error might notappear, even if all of the conditions are     * 
* met:- The database contains multiple occurrences of a        * 
* functionF.- Each occurrence of F is LANGUAGE SQL.- Each      * 
* occurrence of F is a compiled function.  That is, thebody    * 
* the function starts with the BEGIN keyword, and notwithBEGIN * 
* ATOMIC.- Each occurrence of F has the same schema, same      * 
* functionname,and same number of parameters.  The types of    * 
* the parameterswillvary from occurrence to occurrence;        * 
* meaning, each occurrenceofthe function will have a different * 
* signature.- One of the occurrences of F has been             * 
* invalidated.- CREATE OR REPLACE FUNCTION G, which calls one  * 
* of theoccurrences of F that has been invalidated, forcing F  * 
* to beimplicitly revalidated.  The CREATE OR REPLACE          * 
* statement maysubsequently return with SQL0727N, similar to   * 
* the following:SQL0727N  An error occurred during implicit    * 
* system actiontype"3".Information returned for the error      * 
* includes SQLCODE "-440",SQLSTATE "42884"and message tokens   * 
* "G|FUNCTION".  LINE NUMBER=4.SQLSTATE=56098                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 2.                       * 
****************************************************************

Drop and recreate the dependent functions.  Use DROP and CREATE 
FUNCTION for this; do not use CREATE OR REPLACE FUNCTION.  Using 
the error description scenario as a reference, that would mean 
dropping and recreating G, as well as all of the occurrences of 
F.

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

This problem is first fixed in DB2 Version 9.7 Fix Pack 2.

See LOCAL FIX.

forerunner  : APAR is sysrouted TO one or more of the following: IC67728 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

07.04.2010
22.06.2010
22.06.2010

9.7.FP2