Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Problems executing a stored procedure

    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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the entire statement that he is trying to execute? And what is the entire error he is getting?

    Andy

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    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>,

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am sorry I cannot help you. I do not know ETL.

    Andy

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    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?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  8. #8
    Join Date
    Dec 2007
    Posts
    288
    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..

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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: ".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •