Overview

The integrated SQL Debugger is DB2's latest addition to the application development tool suite. It is included with IBM DB2 Universal Databaseâ„¢ (Linux, UNIX®, and Windows®) V7.1 FixPak 3, and DB2 Universal Database V7.2. It is also available through the DB2 Development Center on DB2 for UNIX, and Windows for 7.2 and V8.1. This support is also planned to be available on DB2 for z/OSâ„¢ Version 8.

Using IBM DB2 Stored Procedure Builder and the SQL Debugger component running on the DB2 server, you can remotely debug stored procedures executing on any DB2 server. The debugging support allows you to step through your code, set line or variable break points, view variable values, modify variable values, view call stack information, and also switch between different stored procedures on the call stack. In a nutshell, the SQL Debugger provides support for the following:

• Fenced and unfenced stored procedure debugging
• Nested stored procedure debugging with a call stack view
• Setting, enabling, disabling, and clearing break points with break points view
• Setting execution break points on source code lines managed with break points view
• Setting break points on variable value change event managed with variable and break points views
• Capturing SQL State and SQL Code at every line of execution
• Capturing SQL exceptions

With the new SQL Debugger, stored procedure debugging support is directly integrated into the DB2 Stored Procedure Builder for stored procedures running on DB2 for Windows and UNIX. For debugging Java stored procedures running on DB2 for the Windows and UNIX platforms, you need the IBM Distributed Debugger (available separately as part of IBM VisualAge® for Java.) For debugging SQL stored procedures running on DB2 for OS/390®, you need the IBM Distributed Debugger and the IBM Debug Tool for OS/390.

NOTE: The update variables support is only available with the V8.1 of the integrated SQL debugger which is part of the DB2 Development Center.

Back to top

User Interface

The DB2 Stored Procedure Builder user interface has been enhanced to include support for the new integrated SQL debugger. These enhancements include the addition of multiple debugger toolbars and debugger views. For example:

In this illustration, debug information is displayed in the debug tab in the output area and in the editor window. The debug tab in the output area shows you the state of your debugging session, including: variables, call stack, and break points information. This debug tab also allows you to modify breakpoints and variable values, as well as navigate to other nested stored procedures on the call stack. The editor window shows you the stored procedure source, highlighting the current line of execution. You can set and modify line breakpoints by clicking in the editor prefix area. If an SQL exception occurs during a debug session, you will see an indication of this in the status bar.

Back to top

Debugger Commands

Debuggers, regardless of language, provide a standard set of debugging commands to control the execution of the routine being debugged. The list of debug commands supported by the integrated SQL debugger include: run, run to line (cursor), step into, step over, step out, step return, pause (break), add line break points, add variable breakpoints, toggle break point state (enable/disable), remove break points, get large variable value, set large variable value, set variable value, and run to completion without further debugging.

Back to top

Compiling in Debug Mode

To prepare an SQL stored procedure for debugging, the stored procedure must be built in debug mode as follows:

• Select the desired SQL stored procedure from the project tree view
• Click the Build for debug toolbar button, or right-click and select the build for debug pop-up menu item
• Ensure that the KEEPDARI DB2 server configuration parameter is set to yes. You can do this with the following steps:
  1. Open a DB2 CLP command window
  2. Type the following DB2 command: db2 update database manager configuration using KEEPDARI YES

The stored procedure will now be built in debug mode.

Back to top

Running in Debug Mode

For those SQL stored procedures compiled in debug mode, you have the option of either running them in debug or non-debug modes. To run a stored procedure in debug mode, do the following:

• Select the desired SQL stored procedure from the project tree view
• Click the run in debug toolbar button, or right-click and select the run in debug pop-up menu item

You can also initiate the run in debug mode by clicking on the step-into toolbar button while selecting an SQL stored procedure that was compiled in debug mode. If you select the run in debug option and there are no breakpoints, the run will stop on the first line. If there are any breakpoints in the initial stored procedure it will stop at the first breakpoint. If you select the step into option it will always stop on the first line.

Regardless of issuing the run in debug mode command, if a stored procedure was not compiled in debug mode, then the run will not be debugged, and the stored procedure will be allowed to run to completion without interruption.

Back to top

Debugger Toolbars

The SQL debugger graphical interface is made up of the following related toolbars:

1. Build and Run Toolbar for the run and build in debug or non-debug modes, as well as pause, and run to completion without debugging.
2. Execution Toolbar for the various debug execution stepping commands including: step into, step over, step return, and run to line (cursor).
3. Break Points Toolbar for the various debug break point commands including, set, toggle (enable/disable), remove, and remove all.

Back to top

Build & Run Toolbar

The DB2 Stored Procedure Builder build & run toolbar has been extended to include additional SQL debugger commands for building and running a stored procedure in debug mode. The following table illustrates the various debugger commands added:

Icon	Command Description
	Build (compile) selected stored procedure in debug mode.
	Run or resume running of stored procedure in debug mode.
	Pause (break) execution of stored procedure at next possible line.
	Resume running of stored procedure to completion, ignoring all break points.

Back to top

Execution Toolbar

The debugger execution toolbar has been added to incorporate all of the debug stepping commands. The following table illustrates the various debugger stepping commands supported:

Icon	Command Description
	Step into next line or block of SQL code. If the current statement is a stored procedure call, then the next line is the first line of the called stored procedure.
	Step over to the next line of execution. If the current line is a call to a nested stored procedure or the next line is an indented block of code, then the nested procedure or block of code will be executed as one statement unless a break point was encountered.
	Step return causes execution to resume at the next line in the parent stored procedure of the current nested stored procedure unless a break point is encountered. If the current stored procedure is the only stored procedure in the call stack, then execution will run to completion or the next break point encountered.
	Run to line (cursor) causes the stored procedure being debugged to run and break at the line where the cursor is currently positioned unless an earlier break point is encountered.

Back to top

Break Points Toolbar

The debugger break points toolbar has been added to incorporate all of the debugger break points managements commands. The following table illustrates the various debugger break points commands supported:

Icon	Command Description
	Add new break point for the currently selected source code line or add a variable value change event break point for the currently selected variable.
	Toggle the state of a break point between enabled and disabled.
	Delete the currently selected break point.
	Delete all break points for the current SQL stored procedure.

Back to top

Debugger Views

The SQL debugger graphical interface is made up of the following related views:

1. Editor View, showing the SQL code
2. Break Points View, showing the list of break points currently set
3. Call-Stack View, showing the list of nested stored procedures
4. Variables View, showing the list of defined variables
5. Status Bar, showing the occurrence of SQL exceptions

These views are connected in the sense that the break points and variables views show the debug data for the stored procedure currently shown in the editor view. Switching to a different procedure in either the project tree or the call-stack view causes the break points and variables view to display the debug data for the newly selected stored procedure whose code is shown in the editor view.

Back to top

Editor View

The editor view pane displays the SQL code of stored procedures. The name of the stored procedure is indicated in the window title bar. Selecting a different stored procedure either through the call-stack view or the project tree view causes this view to display the SQL code for that stored procedure.

For example, while in debug mode, the current line of execution is highlighted by a yellow background color. When nested stored procedures are involved, the lines in each stored procedure that invoke a nested stored procedure also are highlighted. The current line of execution in the top-most procedure is also marked with the > sign in the pre-fix area of the editor pane.

Break points may be placed or toggled at valid execution lines either by clicking on the pre-fix area or placing the cursor on the desired line and clicking the break points toolbar buttons.

Back to top

Break-Points View

The break-points view pane displays the list of breakpoints currently defined in the stored procedure shown in the editor window. Both types of break points are shown -- namely, line and variable change event break points. The name of the stored procedure is indicated in the window title bar. Selecting a different stored procedure either through the call-stack view or the project view causes this view to display the break points for that stored procedure.

This view is made up of a table having two columns:

1. Break Point Indicator Column - This table column allows for toggling the state of a break point. Clicking on this column would toggle enabling and disabling a breakpoint. To remove a breakpoint from this view you can use the popup or the toolbar. Note that you can set break points in the editor and variable views. When break points are toggled in this column, their state is also reflected in the variables and editor views and vice versa.
2. Break Point Name Column - This table column displays the descriptive name of a break point. The break point name for a line break point is made up of the keyword Line: followed by the stored procedure schema and name, then the actual line number. The break point name for a variable break point is made up of the keyword Variable: followed by the stored procedure schema, stored procedure name, and the actual variable name, then the line number where the variable was declared.

Note that double clicking on an entry in the break points view causes the editor to move to that line of code in the stored procedure.

Back to top

Call-Stack View

The call-stack view pane displays the list of stored procedures in the execution stack. It is used as both a navigational control to switch between nested stored procedures, as well as an informational view for the execution stack. Double clicking on an entry in the call stack causes the editor to display the code for the selected stored procedure, and also refreshes the variable view.

The call stack view displays the stored procedure names and line numbers at which the stored procedure was invoked.

Back to top

Variables View

The variables view pane displays the list of variables currently defined in the stored procedure shown in the editor window while in debug mode. This view is empty when not in debug mode. The name of the stored procedure is indicated in the window title bar. Selecting a different stored procedure either through the call-stack view or the project view causes this view to display the variables for that stored procedure.

This view is made up of a table having four columns:

1. Break Point Indicator Column - This table column allows for setting break point on a variable value change event. Clicking on this column would toggle enabling and disabling a breakpoint. To remove a breakpoint from this view you can use the popup or the toolbar. Break points set in this column are also reflected in the break points view and vice versa. Note that you cannot set a break point for the two automatic variables, SQL STATE and SQL CODE.
2. Variable Name Column - This table column displays the names of all the variables declared in the current SQL stored procedure. The variable name is post fixed with a colon and the line number at which the variable was declared. The use of the line number allows for distinguishing between two variables having the same name but declared in different code blocks.
3. Variable Type Column - This table column displays the variable type and size (where applicable) for the SQL variable.
4. Variable Value Column - This table column displays the actual value of the SQL variable.

Back to top

Status Bar Support

The status bar has been slightly modified so that it displays the SQL exception information.

The SQL exception information includes the stored procedure name, the line that caused the exception, the SQL State, and SQL Code.

Back to top

Debugger Tutorial

An HTML as well as a PDF version of an interactive SQL debugger tutorial is featured under the DB2 Integrated Development Tools Web site. You can access this tutorial, as well as many other relevant DB2 Stored Procedure Builder tutorials, under the following URL:

This tutorial guides you through the steps required to code, build, debug, and deploy an SQL stored procedure using the DB2 Stored Procedure Builder. After you have worked your way through this brief tutorial, you should have the basic understanding required to debug your own stored procedures.

Back to top

Hints & Tips

The following is a set of Q & A's that might be useful in trouble-shooting problems that may be encountered while using the SQL debugger:

• Why does my procedure run to completion even though I build and run it in debug mode?
  The only known reason for this problem is that the KEEPDARI DB2 server configuration parameter is not set up properly. The SQL Debugger requires that the KEEPDARI configuration parameter be left at its default setting of YES. You can do this as follows:
  1. Open a DB2 CLP command window
  2. Type the following DB2 command:
     db2 update database manager configuration using KEEPDARI YES
• When I use the debugger, why is variable content dropped at 80 bytes?
  The default maximum variable is set at 80 bytes, but you can control this by modifying the following entry in the db2spb.ini file, which is found under the path sqllib\spb: [IBM DB2 Stored Procedure Builder 2.1.3]
  DEBUGGER_MAX_VAR_REPORT_SIZE = 80
• When debugging a stored procedure on a DB2 UNIX server, I keep getting an initialization failure (rc = -5)!
  This is a known defect. DB2 Universal Database V7.2 FP4 is expected to address this. If this is a show-stopper for you, contact the DB2 service team and they may be able to provide you with an interim fix for this.
• I've been encountering frequent UI freezes or hanging while debugging.
  This is a known defect. DB2 Universal Database V7.2 FP4 is expected to address this. If this is a show-stopper for you, contact the DB2 service team and they may be able to provide you with an interim fix for this.
• When selecting a SQL SP, why are the options grayed out for build and run in debug?
  When SPB comes up there is an internal check to see if the debugger support is installed on the server, if this check fails the options are grayed out. If your server does support SQL debugging, then this may be due to a known defect which will be fixed in FP4. You can fix this problem by ensuring that permission are set correctly for the following binaries by having both read and execute permission for user, group, and other:
  sqllib\lib\libdb2psmd.a
  sqllib\function\db2psmds (note the s at the end)
  sqllib\function\unfenced\db2psmdr (note the r at the end)
  
• When I try and select "SQL Stored Procedure Build Options" for an individual stored procedure in SPB I receive SQL10013N concerning the library file .../sqllib/function/db2psmdr.
  This is a known defect. Simply ensure that the binaries have the proper permission as shown above.

Back to top

Valid Break Point Lines

In the initial release of the SQL debugger, breakpoint line positions are not validated. You basically have the ability to set break points anywhere, however any breakpoint that is not at a valid position will be ignored at runtime. Breakpoint validation will be addressed in a future release of the SQL debugger. In addition, breakpoints must be set on the last line of multi-line statements. The following list outlines those SQL statements that are considered valid break point lines:

ALLOCATE CURSOR
ASSOCIATE LOCATORS
CALL
CASE (EXPRESSION)
COMMIT
CREATE PROCEDURE (1st line highlighted)
CREATE <table, view, index>
DEFAULT (VALUE)
DROP <table, view, index>
ELSEIF (EXPRESSION)
EXECUTE
EXECUTE IMMEDIATE
FETCH <..> INTO
FOR v1 AS <SQLstmt>
GET DIAGNOSTICS
GOTO(LABEL)
IF (EXPRESSION)
RETURN(value)
SELECT <..> INTO
SET (EXPRESSION)
UNTIL (EXPRESSION)
WHEN (VALUE)
WHILE (EXPRESSION)

The following are SQL statements that are NOT valid break point lines:

BEGIN
BEGIN
BEGIN NOT ATOMIC
BEGIN ATOMIC
CLOSE CURSOR
DECLARE cursor WITH RETURN FOR <sql statement>
DECLARE , var without default
DECLARE CONDITION (CONDITION) FOR SQLSTATE (VALUE) "..."
DECLARE CONTINUE HANDLER
DECLARE CURSOR
DECLARE EXIT HANDLER
DECLARE RESULT_SET_LOCATOR [VARYING]
DECLARE SQLSTATE
DECLARE SQLCODE (unless there is a default)
DECLARE UNDO HANDLER (unless they are entered)
DO
ELSE
END
END CASE
END IF
END FOR
END REPEAT
END WHILE
ITERATE
LEAVE
LOOP
OPEN CURSOR
REPEAT (as a keyword alone)
RESIGNAL
SIGNAL
THEN
labels, e.g. P1:

Back to top

Conclusion

The integrated SQL debugger is DB2's latest, and perhaps most important, application development tool enhancement that offers source level debugging of SQL stored procedures. It supports standard debugging features -- including the ability to set variable and line break points, capture SQL exceptions, track SQL state and code, and view and modify SQL variable values.

The DB2 Stored Procedure Builder user interface has been extended to seamlessly support the new SQL debugger by providing new toolbars and views. The new toolbars allow for issuing debugger commands -- for example build and run in debug, pause, step in, step out, step over, step return, run to cursor, run to completion; and break point commands: set, enable, disable, and clear break points. The new debugger views include enhanced editor view to showcase debugger breakpoints and current line highlighting, break points view, call stack view, and variable values view.

A number of future SQL Debugger enhancements are planned to provide additional leading-edge features -- making the SQL debugger an even more indispensable application development tool.

Resources

Learn

• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Abdul H. Al-Azzawe is a senior software engineer at the IBM Silicon Valley Laboratory in San Jose and an IBM veteran since 1990. He is the lead architect for the next generation application development tooling for DB2. Prior to his current assignment, Abdul was a member of the core DB2 engine development team at the Toronto Lab. Abdul is the chief architect of the DB2 SQL debugger technology.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Overview
• User Interface
• Debugger Commands
• Compiling in Debug Mode
• Running in Debug Mode
• Debugger Toolbars
• Build & Run Toolbar
• Execution Toolbar
• Break Points Toolbar
• Debugger Views
• Editor View
• Break-Points View
• Call-Stack View
• Variables View
• Status Bar Support
• Debugger Tutorial
• Hints & Tips
• Valid Break Point Lines
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers