Topic
7 replies Latest Post - ‏2014-05-15T12:15:10Z by clivia
clivia
clivia
8 Posts
ACCEPTED ANSWER

Pinned topic how to get debug to work for sql stored procedures

‏2014-04-21T21:29:25Z |

Hello, 

I created a SQL stored procedures in Data Studio v4.1.  I like to be able to debug the routine because it contains some logic problem.  

When I deployed the store procedure, I selected the enable debug flag.  The procedure deploy successfully.

However, when I try to debug the routine, I encountered an error 

"Message: "SYSDEBUGPRIVATE" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.16.53"

The DataStudio is a new install.

Can someone please tell me how to get the debugger to work correctly.

 

Than you

Laifong

  • Equal
    Equal
    134 Posts
    ACCEPTED ANSWER

    Re: how to get debug to work for sql stored procedures

    ‏2014-04-22T07:37:06Z  in response to clivia

    Hi Laifong,

    Which entry in Data Studio did you use to deploy the procedure? In Routine Editor or in the menu of Data Source Explorer? Could you paste the status log in the SQL results view?

    The error seems that the procedure has not been deployed. Do you see the same error when you run it? Thank you!

    Equal

    • clivia
      clivia
      8 Posts
      ACCEPTED ANSWER

      Re: how to get debug to work for sql stored procedures

      ‏2014-04-22T12:49:24Z  in response to Equal

      I deploy with the "Enable debugging" from the Data Project Explorer perspective, as well as from what I think you mean by Routine Editior .  If Routine Editor means the right panel in the attached screen shot.

       

      I also try from the Debug perspective to build and deploy with the debug enabled. 

      Got same error both ways.

       

       

  • Stony
    Stony
    8 Posts
    ACCEPTED ANSWER

    Re: how to get debug to work for sql stored procedures

    ‏2014-04-23T03:25:06Z  in response to clivia

    Hi,Laifong,

    From the error message,  I highly suspect that this is a permission violation error. Probably reason:

    1. The "SYSDEBUGPRIVATE" role is lost or dropped from your database.  Please have a double check whether the role exist in your database. If not, please create the role and assign the current user with the role. Other than "SYSDEBUGPRIVATE" role, another role "SYSDEBUG" role should also exist(see attachments).

    If it still does not work:

    1. Open cmd in Windows and run "db2 ? SQL204N" to check the possible reasons.

    2. In cmd, run "db2level" and tell me the db2 version.

     

     

    Attachments

    • clivia
      clivia
      8 Posts
      ACCEPTED ANSWER

      Re: how to get debug to work for sql stored procedures

      ‏2014-04-23T14:28:14Z  in response to Stony

      Hi,

      The ROLE SYSDEBUG exist, but not SYSDEBUGPRIVATE.

      When I tried to create ROLE SYSDEBUGPRIVATE, I got an error message saying that:

      The name "SYSDEBUGPRIVATE" cannot be used because the specified identifier is reserved for system use.. SQLCODE=-707, SQLSTATE=42939, DRIVER=4.16.53
       

      Is there another way to create the SYSDEBUGPRIVATE role?

      The DB2 version is v10.1 fp3

      ------------------------------------

      I found this APAR http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IC96837.  It looks like the solution is to go to fp4.

      However, if you know of another way to create the role SYSDEBUGPRIVATE, I like to know, because the project I am working on may not move to fp4 any time soon.

      If there is no other way, I may have to look into setting up another environment where I can go to 10.1 fp4.  I am trying to avoid having to do that if possible.

      Thank you for your help

      Laifong

       

       

       

       

      Attachments

      Updated on 2014-04-23T14:57:46Z at 2014-04-23T14:57:46Z by clivia
    • clivia
      clivia
      8 Posts
      ACCEPTED ANSWER

      Re: how to get debug to work for sql stored procedures

      ‏2014-04-23T21:27:29Z  in response to Stony

      I like to make a correct to my previous reply. 

      The lastest fixpack that I can find for v10.1 is fp3.  I went ahead and installed a v10.1 on one of my vmware image that has Window 2008 server OS.  I updated it with fp3 and then created a sample db.  That sample db has both the SYSDEBUG and SYSDEBUPRIVATE roles.  I will create a stored procedure on that sample db and see if I can successfully invoke the debug feature.

      I'll let you know if I am successful.

      • YuanFeng
        YuanFeng
        78 Posts
        ACCEPTED ANSWER

        Re: how to get debug to work for sql stored procedures

        ‏2014-05-15T02:57:26Z  in response to clivia

        Is it works for you now for debug stored procedure with latest fixpack?

        • clivia
          clivia
          8 Posts
          ACCEPTED ANSWER

          Re: how to get debug to work for sql stored procedures

          ‏2014-05-15T12:15:10Z  in response to YuanFeng

          Yes.  The debug work.  Thank you.