Results 1 to 2 of 2

Thread: Sqlcode -551

  1. #1
    Join Date
    Aug 2013
    Posts
    14

    Unanswered: Sqlcode -551

    Dear All,

    Test environment where my application is deployed is using DB2 UDB v10.1 on Linux machine. I don't have access to that system so can't perform anything on my own and need to advise any suggestion/ solution to concerned team managing that DB server.
    What happening there is, application logs showing that SQLCODE -551 is coming whenever application is trying to call any stored procedure and that's because user doesn't has execute privileges on stored procedure.

    I know that can be resolved by running GRANT execute on stored procedures on that machine.

    But my actual question here is, application was working fine and no error was coming earlier. But as soon as, I provided one script (having Alter command, Import command, Reorg table and indexes, Runstats table and indexes, Drop all stored procs, Create again all Stored procs) to concerned team and they ran it, after that this problem has started.
    So I am not sure that user was having execute rights on all stored procedures earlier then how come it was revoked even though I haven't mention any revoke command in my script.

    Does Dropping stored procedure revokes Execute privileges? I dropped existing stored procedures because I wanted them to pick the latest access path created after runstats.

    Not sure what the reason is for SQLCODE -551.

    I ran that script on my local development environment and everything is working like a charm.

    Can anyone please throw some light on it and let me know what could be the possible reason for it.

    Also is there any other way to make sure stored procedures uses latest action plan after runstats. I think it can be done by Bind but not sure how to do that in DB2 UDB.

    Any help would be highly appreciated!

    Thanks in anticipation!

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by Kapil006 View Post
    But my actual question here is, application was working fine and no error was coming earlier. But as soon as, I provided one script (having Alter command, Import command, Reorg table and indexes, Runstats table and indexes, Drop all stored procs, Create again all Stored procs) to concerned team and they ran it, after that this problem has started.
    At the end of your script add some GRANTs

    Quote Originally Posted by Kapil006 View Post
    Does Dropping stored procedure revokes Execute privileges?
    Yes.

    Quote Originally Posted by Kapil006 View Post
    I ran that script on my local development environment and everything is working like a charm.
    I guess you are using an user that has DATAACCESS
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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