|
Problem IC69298
|
Status: Closed |
REBIND_ROUTINE_PACKAGE MAY FAIL WITH ERROR -811 |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users on DB2 V9.7 *
****************************************************************
* PROBLEM DESCRIPTION: *
* 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 *
****************************************************************
* RECOMMENDATION: *
* Customers facing this problem can resolve it by moving to *
* V9.7 FixPack 3. *
**************************************************************** |
| Local Fix: |
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') |
| available fix packs: |
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 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a 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
|
| Solution |
Problem first fixed in DB2 V9.7 FixPack 3. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.06.2010
27.09.2010
27.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
