Debug SQL stored procedures with IBM Database Add-Ins for Visual Studio 2005, Part 1: Start debugging your SQL stored procedures

Learn how to use IBM Database Add-Ins for Visual Studio 2005 to debug stored procedures written in SQL. Discover how much has changed in SQL debugging support, when compared to previous versions of the DB2 Tools for Visual Studio. The revisions greatly simplify the process of building, debugging, revising, and rebuilding SQL stored procedures.

Eric Johnson (ejohnson@us.ibm.com), Advisory Software Engineer, IBM

Eric Johnson photoEric M. Johnson is a developer on the IBM Database Add-Ins for Visual Studio team.



26 July 2007

Also available in Russian

Introduction

The IBM® Database Add-Ins for Visual Studio® 2005 provide extensive options for debugging SQL stored procedures. If you are familiar with debugging applications written in languages such as C#, Visual Basic, or C++ using Microsoft Visual Studio 2005, you will find debugging DB2® SQL procedures to be similar.

Methods for building stored procedures with debugging support and starting the debugger have been simplified in Visual Studio 2005. In Visual Studio 2003, there were two different code windows that displayed the source code for a procedure; one used for debugging, and one used for revising and rebuilding. In Visual Studio 2005, you have a single view of the source code for a procedure, used for both building and debugging. This makes the process of building, debugging, revising, and rebuilding a stored procedure much easier to manage.

To get the most out of this article, you should be familiar with using the IBM Database Add-Ins for Visual Studio to create database connections and working with database objects, such as tables and stored procedures. A good way to become familiar with the IBM Database Add-Ins for Visual Studio is to read the Overview of IBM Database Add-Ins for Visual Studio 2005 and the Develop proof-of-concepts .NET applications tutorial series.

In this article, you'll explore the debugging features in the DB2 Database Add-Ins for Visual Studio 2005, Version 9, Fix Pack 2. The debugging features in earlier releases of the IBM Database Add-Ins for Visual Studio 2005 were similar, but this article will discuss some minor differences.


Creating stored procedures

In order to debug them, SQL stored procedures must be created with debugging support. The IBM Database Add-Ins provide two different ways to create debuggable stored procedures: the stored procedure designer, and the DB2 database project. Before using either of these methods, you must create a connection to a database in the Server Explorer. See the Server Explorer Integration section of Overview of IBM Database Add-Ins for Visual Studio 2005 for details on creating a connection.

Stored procedure designer

Once you have created a connection, expand the connection node. Right-click the Procedures node, and select Add new SQL Procedure with Designer.

Figure 1. Open the stored procedure designer for a new procedure.
Open the stored procedure designer for a new procedure

Use the designer tool to create your new stored procedure. Be sure to set the Debug mode field to ALLOW to enable debugging.

Figure 2. Set the debug mode for a procedure in the procedure designer.
Set the debug mode for a procedure in the procedure designer

You can also use the stored procedure designer to debug an SQL stored procedure that already exists, but was not initially created with debugging support. To do this, expand the Procedures node in your connection, and right-click the procedure you want to debug. Select Open Definition to open the stored procedure designer for that procedure. Then, change the debug mode to ALLOW and save the procedure.

Figure 3. Open an existing procedure in the procedure designer.
Open an existing procedure in the procedure designer

DB2 Project

You can also create SQL stored procedures for debugging using the DB2 project. To do this, first create a new DB2 project, or open an existing one.

Creating stored procedures in debug mode requires using a project configuration with the Build for Debug property enabled. When a new DB2 project is created, two project configurations are created: Debug and Release. Initially, Build for Debug is enabled in the Debug configuration, but not the Release configuration. You can check this setting for your project using the project property pages. Right-click your project and select Properties. The project property page editor is shown.

Figure 4. Check or change the Build for Debug setting in your project's configurations.
Check or change the Build for Debug setting in your project's configurations

After creating stored procedures using a DB2 project, you will need to refresh the Procedures node in the connection to see the new stored procedures.


Starting the debugger

Unlike debugging code in other languages such as Microsoft Visual Basic or Microsoft C#, the IBM SQL debugger is not started from a project. Instead, debugging is started from a DB2 connection in Server Explorer, or from the stored procedure designer.

To start debugging from a DB2 connection in Server Explorer, expand your connection under Data Connections, and then expand the Procedures node. Right-click the SQL procedure you wish to debug, and select Step Into.

Figure 5. Start the SQL debugger from a procedure node in Server Explorer.
Start the SQL debugger from a procedure node in Server Explorer

To start debugging from the stored procedure designer, first open the designer as described above. Make sure the procedure's debug mode is set to ALLOW and that you have saved any changes you made. Then use any of these actions to start debugging: the Step Into toolbar button on the stored procedure designer, the Step Into menu item on the IBM Procedure Designer top-level menu, or the Step Into menu item from the context menu available by right-clicking in the procedure designer.

The next step in starting the debugger is the Run Options dialog. You can specify the initial values for any input parameters here. Optionally, you can indicate whether to automatically commit at the end of the stored procedure execution, and you can specify SQL scripts to be run before and after debugging the stored procedure. If you specify a pre-run or post-run script, they are not debugged.

Figure 6. Complete the Run Options dialog.
Complete the Run Options dialog

The debugger should now start, and automatically stop at the first line of the stored procedure. The current position is indicated by the yellow marker in the left margin. When execution has stopped at the first line, it is a good time to set breakpoints. You may then continue execution using any of the step operations.

Figure 7. The current position is indicated by the marker in the left margin.
The current position is indicated by the marker in the left margin

IBM debugger task status

When the SQL procedure debugger is starting and running, the IBM Debugger Task Status window is shown. This is initially a floating tool window, but it can be docked like other tool windows in Visual Studio.

The task status window shows the steps involved in starting and stopping the debugger. It may sometimes be necessary to stop some of these steps if they seem to take too long. The Cancel button is enabled when a task is running that can be cancelled. This window is only shown in Version 9 Fix Pack 2 and later.

Figure 8. The IBM Debugger Task Status window allows the debugger session to be cancelled if startup takes too long.
The SQL debugger task status window allows the debugger session to be cancelled if startup takes too long

Debugger views

While debugging an SQL stored procedure, there are several features available to examine the current state of the procedure. You can only use these when the debugger is stopped in a stored procedure.

Local variables view

The local variables view may be shown by selecting, from the Debug top-level menu, the Windows cascading-menu, and from that, the Locals menu item. This shows all the variables and parameters that are visible in the current position. The diagnostic variables SQLCODE and SQLSTATE are also always shown here. Variable values may be updated here.

Figure 9. View and modify any variable in the local variables view.
View and modify any variable in the local variables view

Auto variables view

The auto variables view may be shown by selecting, from the Debug top-level menu, the Windows cascading-menu, and from that, the Autos menu item. This shows all the variables and parameters that are referenced in the current lines and nearby lines. The diagnostic variables SQLCODE and SQLSTATE are also always shown here. Variable values may be updated here.

Figure 10. View and modify variables in the auto variable view.
View and modify variables in the auto variable view

Watch view

Like the Locals and Autos views, this is accessed from the top level Debug menu, through the cascading Windows menu. In this case, there is another cascading menu, Watch, from which any of the menu items Watch 1, Watch 2, Watch 3, or Watch 4 may be selected. Each of the watch windows allows you to enter the names of variables you are interested in, and only those variables are shown. This is useful if the stored procedure you are debugging contains a large number of variables, and you are interested in only a few of them. You can also select variable names from the stored procedure source, and drag them to a watch window.

Figure 11. View and modify the variables of your choice in the watch variable view.
View and modify variables of your choice in the watch variable view

Hover variables view

You can examine the value of any variable by pointing the cursor at the variable name in the procedure source. To change the value of the variable, left-click on the hover view, or right-click it and select Edit Value from the context menu.

Figure 12. View and modify variables by hovering over them with the cursor.
View and modify variables by hovering over them with the cursor

Call stack

Like many of the other views, you activate this one from the Debug menu, through the Windows cascading menu, using the Call Stack menu item. If you are debugging nested stored procedures, in which SQL stored procedures call into other SQL stored procedures, this view can show you which lines in other procedures are calling the current procedure. The call-stack is shown top-down: the current procedure is on top, the caller of it is shown next, the caller of that one is shown next, and so on. By double-clicking any of the procedure names, you can see the position in that procedure of the caller who originated the call. You can also see the variables that are in scope in each of the callers, using the locals view or any of the other variable views.

Figure 13. View the call stack.
View the call stack

Debugger operations

When stored procedure execution is stopped at a break or a breakpoint, it is possible to examine the state of the stored procedure execution as described above. Besides viewing, it is possible to change the values of variables. There are several options for stepping or resuming execution of the stored procedure.

Changing variable values

In any of the variable value views described above (the locals view, the autos view, the watch views, and the hover view), it is possible to update variable values. Generally, this is done by selecting the variable value, activating it by double-clicking or pressing F2, and typing the new value. To change a variable value to NULL, use the value (NULL). SQLCODE and SQLSTATE values cannot be changed.

Stepping

The following types of stepping are supported: step over, step into, and step out. In addition to stepping, it is possible to stop execution, or to continue execution while at a break or breakpoint.

Step over causes execution to continue to the next stopping point in the current routine. When execution is stopped on a CALL statement, step into causes execution to continue to the first line of the routine being called. If the routine being called is not a SQL procedure with debugging support, or if execution is not stopped on a CALL statement, step into performs the same operation as step over.

Step out cause execution to continue until the first of the following conditions is met: a stopping point in the procedure that called the current one is reached; a breakpoint is activated; or the procedure exits.

Stopping points do not directly correspond to lines of code. Generally, stopping points are defined at each location in the code where variable values are read or modified, control statements affecting the flow of execution, and call statements. The exact statements containing stopping points vary by platform. It is possible for a single line of code to contain multiple stopping points. This can happen if there are multiple statements on a single line. On some platforms, it also happens when there are multiple variable declarations on a single line. When there are multiple stopping points on a single line, a step into or step over operation on that line will result in stopping on the same line again.

Stop Debugging causes the procedure to be interrupted without completing. In this case, an exception indicating that the procedure has been interrupted is displayed in the debug output pane. No result sets or out parameters are reported when the procedure is stopped.

Continue causes execution to continue until either a breakpoint is activated, or the procedure exits.

All of these execution control operations are available from the main Debug menu in Visual Studio when execution is stopped at a break or breakpoint. In addition to the menu items, standard debugging toolbar buttons and keyboard shortcuts work for the debugging operations.


Breakpoints

There are two types of breakpoints supported by the SQL procedure debugger: line breakpoints and variable breakpoints.

Line breakpoints

Line breakpoints can be set on any line with a stopping point. Line breakpoints cause execution to stop when the stopping point is reached during execution of the procedure. The easiest way to set a line breakpoint is to left-click in the right margin next to the line.

Figure 14. The breakpoint marker indicates the location of a line breakpoint.
The breakpoint marker indicates the location of a line breakpoint

If a breakpoint is set on a line that does not contain a stopping point, the breakpoint will never be activated, and will have no affect.

Variable breakpoints

Variable breakpoints are set on variables, and are activated whenever the variable value changes. To set a variable breakpoint, left-click the variable name in the stored procedure source, and right-click to activate the context menu. Select the Breakpoints cascading menu, and select Insert variable breakpoint.

Figure 15. Create a variable breakpoint.
Create a variable breakpoint

Whenever the variable value changes, execution stops at the next stopping point after the variable value change, and a message box is shown indicating that the variable value has been changed.

Breakpoints window

To activate the Breakpoints tool window, use the top-level Debug menu, activate the Windows cascading menu, and select Breakpoints. This tool window can also be used to remove breakpoints, including variable breakpoints. To delete a breakpoint in the Breakpoints window, right-click it and select Delete. An alternate way to delete a line breakpoint is to click the breakpoint in the left margin of the stored procedure source editor.

Figure 16. View and modify breakpoints.
View and modify breakpoints

The Breakpoints tool window can be used to set the hit count setting for a breakpoint. To do this, right-click the breakpoint in the Breakpoints window, and select Hit Count.... The settings on the Breakpoint Hit Count dialog allow the breakpoint to be activated always, only when its hit count is equal to a specified value, only when its hit count is greater than a specified value, or only when its hit count is a multiple of a specified value.

Debugging SQL procedures on DB2 for z/OS Version 8 databases

There are several important differences that apply to debugging against DB2 for z/OS V8 databases.

On DB2 for z/OS V8, debugging requires the use of a debugger daemon running on the client workstation where Visual Studio 2005 is running. See Debugger settings for information about configuring the debugger daemon.

Additionally, the stepping mode is different on DB2 for z/OS V8. It is not possible to step into a nested stored procedure call. Only a single stored procedure may be debugged. Because nested stored procedures are not supported, the step into, step over, and step out operations work differently. Step into and step out operate on blocks of code rather than stored procedures. That is, step into will step into a block of code, such as the body of a WHILE loop. Step over will step over the block of code. Step out steps out of the current block of code.


Debugger settings

There are several settings in the Options window that affect the IBM SQL procedure debugger.

Debug Session Timeout: If a debug session is stopped, and no operations are performed for the number of seconds specified, the debug session is terminated and the procedure runs to completion. This applies to all platforms, on z/OS Version 8 and Version 9. Maximum Variable Display Length: Variable values longer than this setting are truncated. This applies on all platforms, on version 8 and version 9. Debug Daemon Host: This is the hostname or IP address of the local machine, where the debug daemon will run. Normally, you can leave the default value of localhost. If your machine has multiple IP addresses, you should set this value to be an IP address that is accessible from the database server on which you are running the stored procedure. This is used only for z/OS Version 8. Debug Daemon Port: The TCP/IP port number the debug daemon will use. Normally the default value can be used. If you have another service which uses the same port number, change this to an unused port number. This is used only for z/OS Version 8. Diagnostic Trace Level: The amount of diagnostic tracing performed on the server. Normally, you should leave this at 0, unless instructed by DB2 support. Session Manager Connection: The connection that will be used to run the session manager. In most cases, you should leave this blank, and the session manager will run on the same connection as the stored procedure you are debugging. If you wish to use a different connection for the session manager, first create the connection in Server Explorer, and then set the value here. This applies to all platforms, but to version 9 only. Session Manager Port: The port number used for the session manager. If the default value conflicts with another service on the server where the session manager runs, change this to any unused port number.

Figure 17. Edit debugger settings in the options window.
Edit debugger settings in the options window

Summary

IBM Database Add-Ins for Visual Studio 2005 provide debugging features for SQL stored procedures similar to the debugging features for applications written in Visual Basic, Visual C#, and Visual C++. This article shows you just how easy these SQL procedure debugging features make it to find and correct bugs in SQL stored procedures.

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 Enterprise 9.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=242865
ArticleTitle=Debug SQL stored procedures with IBM Database Add-Ins for Visual Studio 2005, Part 1: Start debugging your SQL stored procedures
publish-date=07262007