Debugging Stored Procedures

The Development Center offers a complete range of debugging tools for stepping through a procedure. The debugger is integrated into the Editor View. When a debug session is started, the Development Center will either open up a window for the current procedure or use an existing window if the procedure is already being edited.

The following icons shown in Table D.4 are used to initiate and terminate a debug session.

Table D.4. Debugger-Related Icons

Icon	Action	Description
	Build for debug	Builds a stored procedure in debug mode.
	Run with debug	Run a stored procedure in debug mode. This button is enabled only if the procedure was built for debug.
	Pause debug	Temporarily stop debugging.
	Resume debug	Resumes a paused debug.
	Terminate	Stop run in debug mode. Uncommitted changes will be rolled back.

A procedure must be built in debug mode in order for the debugging capabilities to be used. If a procedure is built in regular mode, attempts to run it in debug mode will result in the procedure simply being run normally.

Once a procedure is run in debug mode, you can use the stepping buttons shown in Table D.5 to walk though lines of code one by one.

Table D.5. Icons for Controlling Debugger Stepping

Icon	Action	Description
	Step into	Trace through code one line at a time.
	Step to cursor	Runs the procedure to the line of code where the cursor is currently placed or the next breakpoint.
	Step over	Execute a block of code or a call to nested procedure without stepping through their sub steps. This allows you to avoid tracing entire blocks of code in which you are not interested.
	Run to completion	Run the procedure to the end ignoring all breakpoints.
	Step return	Exit the procedure immediately from the cur rent point in the code.

As you step through code in debug mode, you can view the state of all variables in the variables section of the Debug view (see Figure D.26).

Figure D.26. Viewing and changing variable values in a debugging session.

Tip

When execution has paused in debug mode, variable values can be manually changed mid-flight if required (with the exception of SQLCODE and SQLSTATE).

A procedure can often be quite long, and having to step through many lines of code to reach the section of interest can be quite tedious. To alleviate this, a breakpoint can be added to the code which will halt the execution of a procedure when that point is reached by the debugger. Multiple breakpoints can be added to a procedure, and each breakpoint will be indicated by a red dot. A breakpoint is useful if you want to check the current values of a variable at a particular point in the code to ensure that the procedure is performing as expected.

The buttons shown in Table D.6 are used to manipulate breakpoints.

Table D.6. Icons for Setting and Manipulating Breakpoints in the Development Center

Icon	Action	Description
	Add breakpoint	Adds a breakpoint at the current cursor position.
	Remove breakpoint	Removes the breakpoint at the current cursor position.
	Toggle breakpoint	Enables or disables an existing break point. If no breakpoint exists, it will be added.
	Remove all breakpoints	Removes all defined breakpoints.

A breakpoint can also be added, removed, and toggled by clicking in the leftmost column of the Editor view while the arrow is on the line of code where the break should occur (see Figure D.27).

Figure D.27. Adding a breakpoint.

A breakpoint in a procedure can be reached by clicking the Step to Cursor icon. The code will execute until the current cursor position or the first breakpoint is reached.