Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Debugging DB2 Stored Procedure using RDP

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Debugging DB2 Stored Procedure using RDP

    I have successfully created and deployed a DB2 SQL stored procedure using the wizard IBM RDi SOA (RBD + RDP). Also after running the stored procedure it is returning me the correct result in the SQL Result view. But I can’t able to debug the stored procedure

    While creating the stored procedure I have checked the Deploy on Finish and Enable debugging check boxes. I have started the DB2DBGM.bat session manager. Also set up the RDP preferences (Window -> Preferences -> Run/Debug -> Routine Debugger -> DB2 -> SQL and Java) Use an already running session manager with the IP address and Port number as showing in the db2dbgm session which is running in my PC.

    From the Data Project Explorer perspective after right clicking my sample DB2 Stored project when I click to the Debug option it is not launching the debug view to open my stored procedure so that I can step through. I have also tried after setting up the break point. Just it is running and output the result in the SQL Result view, simple like Run option.

    Can anyone please help me out on this.

    Thanks & Regards
    Dibs

    Below is the stored procedure for your further references

    CREATE PROCEDURE SPEMPLOYEE_TEST2 (IN offcode VARCHAR(10),
    OUT SQLSTATE_OUT CHAR(5),
    OUT SQLCODE_OUT INTEGER )
    RESULT SETS 1
    LANGUAGE SQL
    ALLOW DEBUG MODE
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- offcode
    -- SQLSTATE_OUT
    -- SQLCODE_OUT
    ------------------------------------------------------------------------
    ------------------------------------------------------------------------
    -- Variables declaration fragment inserted from UDFSCA_SQL_VAR.FRAGMENT
    ------------------------------------------------------------------------

    P1: BEGIN
    -- Declare variables
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;

    -- Declare cursor
    DECLARE cursor1 CURSOR FOR
    SELECT EMPLOYEENUMBER, FIRSTNAME, JOBTITLE, LASTNAME, OFFICECODE
    FROM EMPLOYEES
    WHERE OFFICECODE = offcode;

    -- Declare handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    FROM SYSIBM.SYSDUMMY1;

    -- Cursor left open for client application
    OPEN cursor1;
    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;
    END P1

  • #2
    Could anyone please let me know how to debug a DB2 UDB SQL Stored Procedure from RDi.

    What I want to create a Stored Procedure using RDPi (Rational Developer for Power for System i) and then deploy it to iSeries machine (V5R4) with enabling debug option. Once it deployed want to debug (step into) the stored procedure using RDPi.

    Would like to know, does it possible, if so what are the steps.

    Thanks & Regards
    Dibs

    Comment


    • #3
      The following additional steps need to be taken:

      1. Add SET OPTION DEBUGVIEW = *SOURCE to the SQL Stored Procedure source
      2. Start the debug server on the IBM i (STRDBGSVR)
      3. Find the IBM i object name of the C program generated from the create of the stored procedure in the Remote System Explorer perspective.(Tip: use a meaningful 10 character specific name)
      4. Set a Debug(service entry point) for this program
      5. From the Data Project Explorer right click on the stored procedure and choose Run (not Debug).
      6. The SQL statements from the deployed SQL Stored procedure will be displayed in the RDP Debug view. You can now step though the procedure.

      Comment


      • #4
        Thanks for your guidance, I have tried and it is working, but from the debugger perspective as it is going into the underneath C program and for that I have to hit the function key F5 (Step into) / F6 (Step over) for multiple times to go to the next line in my source code. Also when I move the cursor on to a variable I can’t view the contents of it neither it’s allow me to watch the variable (after selecting the variable right click and select the Monitor Expression) giving me the error “Server: CPF7E12 Identifier does not exists”. I can’t also set a break point in the source view.

        I can switch the view to *LISTING which brings me back the underneath C program and there I can set break point and can monitor the variables. But I am not very familiar to the C programming and also the variables are defined with other names and it’s also very hectic to debug there as the code is bit cryptic.

        I had an impression that the RDi/RDP will give me the opportunity to the Graphical Debugger where I can just debug my stored procedure set my break point, watch variables and take advantages of all debugging features, is there any way of doing these.

        Comment


        • #5
          Sounds like you should open a PMR against the RDP Debugger for the CPF7E12 error essage.

          You should be able to set a breakpoint on an SQL statement in the *SQL source view with both the RDP Debugger and the IBM i Graphical Debugger that's accessible from the System i Navigator Run SQL Scrips window. If not, then I'd create a PMR for that issue as well since that works on my system.

          Because there are potentially multiple lines of C code used to implement the SQL statement behind the scenes, you really need to use breakpoints instead of the "Step" debug function keys.

          Comment


          • #6
            Thanks for replying back.

            As I mentioned from the RDP in the Debugger View I can’t set up any breakpoint from the *SOURCE view, I can setup the breakpoint only in *LISTING view but it is the C code behind the scene. It is cryptic and the variables are declared in it own prototype.

            What basically will be useful, from the *SOURCE view in the Debugger view in RDP if I can setup the break point and monitor the variable. Not sure is it possible to do so or I am missing any bit which is not allowing me to do so.

            Could you please confirm me, do you able to add a breakpoint and can monitor a variable from the *SOURCE view (where your actual SQL Stored Procedure source is showing not the C code behind the scene) as well.
            Last edited by Guest.Visitor; 07-16-2010, 03:09 AM.

            Comment


            • #7
              After installing the fixed pack for RDP 7.6.0.1 now I can setup a breakpoint from the RDP debugger view while debugging a stored procedure.

              But getting the error “Server: CPF7E12 identifier does not exist” while trying to Monitor Expression (select the variable then right click and then select Monitor Expressin form the list) for a variable in the debugger.

              Comment

              Working...
              X