DB2 FOR LUW / DB2FORLUW / 970 - DB2

Hi askDB2Look, 
 
I'm working on a line item for V97FP2 which relies heavily on 
DB2LOOK, and have come across the following bug involving two 
functions with the same name, but in different schemas. When I 
use db2look with the -x option to generate the DDL for the GRANT 
statements on the function, one of them is incorrect. 
 
Here is an example. 
 
Repro Steps 
1) create table s1.tab1(c1 smallint NOT NULL, c2 int, c3 bigint) 
2) create function s1.func1() specific func1 returns integer 
language sql reads sql data deterministic no external action 
begin declare i integer; set i = (select c3 from s1.tab1 where 
c2 = 22);return i;end 
3) grant execute on function s1.func1 to user ZURBIE 
4) create function s2.func1(x int) specific func1 returns 
integer fenced language java parameter style java external name 
'Functions!squire' no external action called on null input 
deterministic no sql 
5) grant execute on function s2.func1 to user zurbie 
 
6) db2look -d <dbname> -l -e -x 
 
Here is a snippet from the db2look output ... 
 
----------------------------------------------------- 
-- Authorization Statements on User Defined Functions 
----------------------------------------------------- 
GRANT EXECUTE ON FUNCTION "S1      "."FUNC1"(INTEGER) TO USER 
"ZURBIE  " ; 
GRANT EXECUTE ON FUNCTION "S1      "."FUNC1"(INTEGER) TO USER 
"ZURBIE  " ; 
GRANT EXECUTE ON FUNCTION "S2      "."FUNC1"(INTEGER) TO USER 
"ZURBIE  " ; 
GRANT EXECUTE ON FUNCTION "S2      "."FUNC1"(INTEGER) TO USER 
"ZURBIE  " ; 
 
If I try to execute the grant statement on s1.func1 as provided 
by db2look, I get the following error: 
 
GRANT EXECUTE ON FUNCTION "S1      "."FUNC1"(INTEGER) TO USER 
"ZURBIE  " 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it returned: 
SQL0458N  In a reference to routine "S1.FUNC1" by signature, a 
matching routine could not be found.  SQLSTATE=42883 
 
I believe that the GRANT statement is created incorrectly, as 
there should be no (INTEGER). If you drop function s2.func1, 
then run db2look again, you will get the correct GRANT DDL stmt 
of 
GRANT EXECUTE ON FUNCTION "S1      "."FUNC1"() TO USER "ZURBIE 
" ; 
 
Has this issue already been discovered? 
 
Thanks, 
Ram

**************************************************************** 
* USERS AFFECTED:                                              * 
* Software                                                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Incorrect GRANT statement with db2look when two functions    * 
* with same name but different schema                          * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* move to v97 fp1                                              * 
****************************************************************

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 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
 

fixed in v97 fp1

not known / see Local fix

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

05.10.2009
05.02.2010
05.02.2010

9.7.FP1