Problems executing a stored procedure

itsonlyme44 · #1 (**permalink**) 05-08-09, 13:29

DB2 Version 9.5 on windows

I have a user who reported this error to me:

Warning: Cannot create stored procedure(s), resulting in non-optimal performance. To improve performance, you must authorize
user <OPETLL70> to CREATE, DROP, and EXECUTE stored procedures. DB2 data source <%1> warning message for operation <%3>: <%4>,

He is trying to execute a stored procedure when he receives the error.

The user OPETLL70 has Full authority in the database and full priviledges to the stored procedure itself.

I googled the error and found this: Help - IBM WebSphere Help System

and then I executed this:
GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER OPETLL70

he is still getting the error. can anyone help?

ARWinner · #2 (**permalink**) 05-08-09, 13:36

What is the entire statement that he is trying to execute? And what is the entire error he is getting?

Andy

itsonlyme44 · #3 (**permalink**) 05-08-09, 13:45

Here is the SQL that he is using to call the truncate procedure from his ETL (BODI) job

Code:

sql('DS_Stage_Data_Mart','CALL '||$sys_DM_Target_Schema ||'.truncate_table(\''||$sys_DM_Target_Schema ||'\',\''|| substr( $GV_JOBNAME,0,index( $GV_JOBNAME,'_STG',0)-1) ||'\')');

and here is the error:

Code:

Warning: Cannot create stored procedure(s), resulting in non-optimal performance. To improve performance, you must authorize
user <OPETLL70> to CREATE, DROP, and EXECUTE stored procedures. DB2 data source <%1> warning message for operation <%3>: <%4>,

ARWinner · #4 (**permalink**) 05-08-09, 13:53

I am sorry I cannot help you. I do not know ETL.

Andy

itsonlyme44 · #5 (**permalink**) 05-08-09, 14:01

I think it's a db2 permissions problem..

the user has these permissions to the stored procedure:

GRANT EXECUTE ON PROCEDURE KCSTGDUT.TRUNCATE_TABLE(VARCHAR(),VARCHAR()) TO USER OPETLL70;

am I missing something?

stolze · #6 (**permalink**) 05-08-09, 14:21

The problem is that you show us an error message that does not originate from DB2 but rather from some application that you are using. You will have to figure out what that application is doing and whether there is indeed a problem with DB2 or the user's privileges - or not.

ARWinner · #7 (**permalink**) 05-08-09, 14:23

It looks like the schema for the procedure is dynamic. Is there more than one SP with that name is a different schema that the user does not have right to execute?

Andy

itsonlyme44 · #8 (**permalink**) 05-08-09, 14:25

my deepest apologies.

I thought the error was originated from DB2. I'll have to look through my diag log and see if it is generating an error..

stolze · #9 (**permalink**) 05-09-09, 06:54

All DB2 (or rather IBM) error codes have a 3-letter product prefix. For DB2 follows 4 or 5 digit number and a single letter indicating the severity. None of the DB2 error messages starts with "Warning: ".

Internet Services

Web Development

Digital Marketing

Consumer Tech