DB2 FOR LUW / DB2FORLUW / 950 - DB2

REBIND_ROUTINE_PACKAGE may fail with error -811 in a case where 
the routine's package name is the same as another package 
belonging to another routine in a different schema. 
 
For Example : 
------------- 
 
On calling the REBIND_ROUTINE_PACKAGE, it may return : 
 
SQL0443N  Routine "SYSPROC.REBIND_ROUTINE_PACKAGE" (specific 
name "REBIND_ROUTINE") has returned an error SQLSTATE with 
diagnostic text "-811, 21000, ".  SQLSTATE=38000 
ie. a -811 token is received inside an SQL0443 message

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users on DB2 V9.5 Below FixPack 7                            * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* REBIND_ROUTINE_PACKAGE may fail with error -811 in a         * 
* casewherethe routine's package name is the same as another   * 
* packagebelonging to another routine in a different           * 
* schema.For Example :-------------On calling the              * 
* REBIND_ROUTINE_PACKAGE, it may return :SQL0443N  Routine     * 
* "SYSPROC.REBIND_ROUTINE_PACKAGE" (specificname               * 
* "REBIND_ROUTINE") has returned an error SQLSTATE             * 
* withdiagnostic text "-811, 21000, ".  SQLSTATE=38000ie. a    * 
* -811 token is received inside an SQL0443 message             * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V9.5 FixPack 7                                * 
****************************************************************

The customer can use the workaround of dropping and recompiling 
conflicting routines until they have unique package names.  To 
find routines in conflict, they can use the following stored 
procedure: 
 
create procedure same_package_name(in schema varchar(128), in 
name varchar(128)) 
dynamic result sets 1 
begin 
  declare stmt_text varchar(512); 
  declare S1 statement; 
  declare C1 cursor with return to caller for S1; 
 
  set stmt_text = 
    'select trim(r.routineschema) || ''.'' || 
trim(r.routinename) ' || 
    'from syscat.routines r, sysibm.sysdependencies d where 
d.bname = ' || 
    '(select d.bname from syscat.routines r, 
sysibm.sysdependencies d ' || 
    'where r.routinename = ? and r.routineschema = ? ' || 
    'and d.btype = ''K'' and d.dtype = ''F'' ' || 
    'and d.dname = r.specificname and d.dschema = 
r.routineschema) ' || 
    'and d.dname = r.specificname'; 
 
  prepare S1 from stmt_text; 
  open C1 using name, schema; 
end @ 
 
The procedure is called with the schema and name of the routine 
failing in REBIND_ROUTINE_PACKAGE; it returns a list of routines 
that share the same package name. 
 
Example : call same_package_name('MYSCHEMA', 'PROC1')

DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem First Fixed in DB2 V9.5 FixPAck 7

not known / see Local fix

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

31.05.2010
15.11.2010
15.11.2010

9.5.FP7