How to debug a BEGIN-END SQL block with DB2?

I have a big SQL script that contains a BEGIN DECLARE-END block for a DB2 database.

That block contains a big WHILE loop that seems to go infinite and I need to figure out what is the error :

--#SET TERMINATOR @
BEGIN
  DECLARE...
  //many insert/join/update
  WHILE(condition)
    //some looping join
  END WHILE;
END@

My script globally looks like that.

But I do not manage to analysis the behavior because the execution is poorly traced since the whole block is considered as a single statement so it only returns 1 code for all the statements contained in.

Is there any way to trace each executed statement within the block ?
If not, how would you recommend to proceed to debug my script ?

Thank you for your help,

Solution

Usually code inspection (possibly by a different pair of eyes) proves fastest.

You can debug (single step, breakpoint etc.) with IBM Data Studio (though you may need to encapsulate the block in a stored procedure first).

If your Db2-server runs on Linux/Unix/Windows, you can also make use of logging of activities, in other words changing your code to add debugging lines at key points, then letting your code run and viewing the output. This is restrictive because you may not get to see the output until the code stops , depending on the buffering. But it can help. To use it, add set serveroutput on to your script before the start of the block, and then add call DBMS_OUTPUT.PUT_LINE('....some debugging info...') at key points. See PUT_LINE. See also the other methods in the DBMS_OUTPUT package.

I've also seen code that logs to table, i.e. instead of using dbms_output.put_line, it calls an autonomous sproc to append progress line to a separate dedicated logging-table. That table can be viewed while the main sproc runs, and can have timestamps etc. This is very useful when the logging can be enabled/disabled on the fly.