|
Problem IC67728
|
Status: Closed |
SQL0727N REPORTED AFTER CREATE OR REPLACE FUNCTION OF COMPILED SQL FUNCTION |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 |
| Problem description: |
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 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* 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 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.8 Fix Pack 3. See LOCAL FIX for *
* additional workarounds. *
**************************************************************** |
| Local Fix: |
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. |
| available fix packs: |
DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
This issue is corrected in DB2 Version 9.8 Fix Pack 3. |
| Workaround |
See LOCAL FIX. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.04.2010
20.12.2010
20.12.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.3
|
 |
