SQL Debugger for DB2 UDB LUW

Aqua Data Studio’s integrated SQL Debugger for IBM DB2 UDB allows you to debug stored procedures and functions. You can troubleshoot complex queries, statements and procedural code. The debugger permits single-stepping, pausing and setting breakpoints within the procedure code, making it easy to evaluate variables and the conditional flow of the code. The Console, Breakpoints, Variables, Watches, Stack Frames and Profile tabs at the bottom of the debugger display detailed information as the execution progresses.

Aqua Data Studio SQL Debugger for DB2 UDB LUW includes the following features:


Launching the DB2 UDB Debugger

You can open the DB2 SQL Debugger through a right click in the Schema Browser on Stored Procedures or User Defined Functions and selecting Debug from the popup menu. More than one debugger can be opened at once, with each object to be debugged opened in a separate tab.

Setting break points for debugging

Breakpoints are temporary markers you place in your code to tell the debugger to stop your stored procedure at a given line. During the interruption, you can inspect the environment to find out whether your code, functions as expected. Set breakpoints using Ctrl+F8 or click on the Toggle Breakpoint Icon in the editor. The breakpoints can be viewed in the Breakpoints tab.

Step Into

Step Into executes the current statement and then stops at the next statement. If the current statement is a function, the debugger steps into that function, otherwise it stops at the next statement. A new debugger tab is opened for each function call stepped into. Once inside the function call, debugging can continue until the end of the function, or until you click “Step Return”.

View Variables in Debug Mode

You can view variables and their values during execution in the Variables tab.

Stack Frames

If the debugged procedure calls other procedures, the Stack Frames tab shows the path of execution across all external procedures that are called.

Target Query Window

A separate Query Window can be opened as a Target Query Window to run queries before the debugging starts.

Profiles

If Profiling is enabled the procedure debugger will show the time taken for each procedure’s executed statements. While in Profile Mode a full run will be executed one step at a time, allowing a full time profile per step.

DB2 UDB Debugger Workspace

Along the top of the debugger workspace are the Execute, Stop, Step Into, Step Over, Step Return, Toggle Breakpoint, Turn On Profiling, Target Query Window, Debugger Query Window buttons. Below the debugger window are tabs for Console, Breakpoints, Variables, Watches, Stack Frames and Profile.

Step Over

Step Over executes the current statement and then stops at the next statement. If the current statement is a function, then the debugger executes the whole function and it stops at the next statement after the function call. Use F8 key or use the “Step Over” button which will allow you to move to the next line of the code.

Step Return

The “Step Return” button allows you to execute the function or procedure until the current operation returns and then exit. Focus shifts back to the initially opened procedure. The tab of the opened function remains open for easy access. The results of the execution will be displayed in a results view window.

Setting Parameters for Procedure Debugging

Parameters used by procedures can be set via the GUI before the debugging starts. Values can be set to NULL, a default value or a value you define.

Watch Tab

If multiple variables are declared in the debugged procedure, they all appear in the Variables List. You can select a few variables of interest and place them in the Watches tab to observe how their values change in the course of the procedure execution.

Run to Cursor

It is possible to place a cursor within the procedure and right click to “Run to Cursor”, executing from the beginning of the procedure to that point and stopping.

References


  1. ^ DB2 Database Wikipedia article. IBM DB2 LUW – IBM DB2 Database for Linux, Unix and Windows edition.
  2. ^ IBM Corporation Wikipedia article.