Level: Intermediate Eric Johnson (ejohnson@us.ibm.com), Advisory Software Engineer, IBM
26 Jul 2007 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
About the author | 
| | Eric M. Johnson is a developer on the IBM Database Add-Ins for Visual Studio team. |
Rate this page
| 
