About Debugging SQL
Use the Debugger to debug any script that contains a procedure regardless of the SQL included in the script (i.e., SELECT statements).
Notes:
- To configure your DB2 for LUW database or DB2 for z/OS subsystem to support the Debugger, see the Toad for DB2 Installation Guide.
- You can run the Debugger on only SQL procedures.
Prepare and Debug an SQL Procedure
You can run the Debugger on a stored SQL procedure that has been compiled with the debug support code. When you launch the Debugger on a procedure, the procedure script opens in the Editor. From here, you monitor and control the debugging process.
To prepare and debug an SQL procedure
-
Select the Procedures node in the Database Explorer or Object Explorer.
-
Add the debug support code to the SQL procedure you want to debug. Use one of these methods:
» Right-click the SQL procedure, and select Compile SQL Procedure with Debug. Note: For a DB2 for z/OS procedure, this method opens the Compile Subroutine window. To compile, complete the fields and execute the generated script. (The read-only Function field on the Build page shows REBUILD_DEBUG.)
-
Do one of the following.
- Click
to create the SQL procedure.
- Select the procedure to which you are adding debug support code, and click
to alter it.
- Click
- Select the appropriate value on the Build page:
- In Create window, select BUILD_DEBUG for the Function field.
- In the Alter window, select ALTER_REBUILD_DEBUG for the Function field.
- Complete the remaining fields in the Create or Alter window.
- Execute the generated script both to create or alter the procedure and to compile it with the debug support code.
Note: If errors occur during the compile process, Toad notifies you and rolls back the previous version of the procedure. If no errors occur, Toad commits the procedure.
-
- Right-click the SQL procedure, and select Debug.
-
Define the start parameters.
Tip: You can manually set start parameters at anytime during the debugging process by clicking
in the Debug toolbar.
- In the Editor, use the Debug toolbar functions to control the debugging process. See Related Topics for more information.
Compile or recompile the procedureConfigure Debugger Options
|
To... |
Use this Option Page... |
|
Set an option so function calls are not evaluated when hovering the cursor over watches |
Debugger | General |
|
Select a default desktop configuration that displays when starting or ending a debugging session | |
|
Set DB2-specific options, such as requirements for commits and debug-session time-outs |
Debugger | General | DB2 |
Tips:
-
If you have not set a breakpoint, clicking
runs the SQL script to completion without stopping.
- If you try to run a debug script, you are prompted to set start parameters to use as an entry point, as well as provide values for any required parameters.
- To remove debug support code from an SQL procedure, right-click the procedure and select Compile SQL Procedure. You can also alter a DB2 for z/OS procedure, changing the Function property on the Build page to ALTER REBUILD.