Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC65704 Status: Closed

INCORRECT GRANT STATEMENT WITH DB2LOOK WHEN TWO FUNCTIONS WITH SAME NAME
BUT DIFFERENT SCHEMA

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
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 
" ;
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* 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  " ;                                                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v9.5 fp6                                      * 
****************************************************************
Local Fix:
available fix packs:
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
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

Solution
Problem fixed in v9.5 fp6
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
20.01.2010
30.03.2010
30.03.2010
Problem solved at the following versions (IBM BugInfos)
9.5.FP6
Problem solved according to the fixlist(s) of the following version(s)
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.