DB2 FOR LUW / DB2FORLUW / 970 - DB2

There are two types of recursive dependencies that can be 
created and result in SQL20481N error 
 
A) reflexive dependency when SP depends on itself can be created 
when following characteristics exist 
1. SP1 modifies base table T1 
2. T1  has a trigger TRIG1 defined on it 
3. TRIG1 calls SP1 
 
and actions are performed 
 
1. drop SP1  once trigger has been created 
2. use 'create or replace' syntax to re-create the procedure 
 
At this point there is a reflexive dependency in catalogs of SP1 
-> SP1 and using 'create or replace ' syntax to recreate SP1 
will result in SQL20481N error 
 
B) Indirect recursive dependency where SP depends on other 
object and this object depends on SP can occur when SP's exhibit 
following characteristics 
 
1. SP1 modifies base table 
2. SP2 uses base table and calls SP1 
3. Trig calls SP2 
 
and following actions take place 
 
1. SP1 is dropped 
2. SP1 is created using 'create or replace' syntax 
3. alter on base table is performed to invalidate SP1 & SP2 
4. Call SP1 to trigger automatic revalidation 
 
This will result in SP1 -> SP2 -> SP1 indirect recursive 
dependency in catalogs and any subsequent attempts to use 
'create or replace' syntax to recreate SP1 or SP2 will fail with 
SQL20481N error

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fixpack 11.                       * 
****************************************************************

It is necessary to drop SP's and recreate them

First fixed in DB2 Version 9.7 Fixpack 11.

not known / see Local fix

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

28.05.2015
07.10.2015
07.10.2015

9.7.FP11