Debugging SQL and SPL Procedures in CLR Applications - IBM Database Add-Ins for Visual Studio

While you develop Windows or ASP.NET applications in C# and Visual Basic, you can debug the applications. If the applications access DB2^® servers, you can debug the SQL and SQL PL procedures that are called from the applications while you debug the applications.

Starting in Informix^® Data Server (IDS) Version 11.70, if the applications access IDS servers, you also can debug SPL procedures that are called from the applications. In IDS SPL procedures, you can debug only the SPL statements, not the SQL statements.

Restriction: The IBM Unified Debugger, which is the feature that you use to debug SQL, SQL PL, and SPL procedures in C# and Visual Basic applications, is available for the following releases of DB2 and IDS:

DB2 for Linux^®, UNIX^®, and Windows^® Version 9.1, or later
DB2 for z/OS^® Version 9 new function mode (NFM), Version 10 in Version 9 conversion mode (CM), or Version 10 NFM
DB2 for i V5R4, V6R1, and V7R1
IDS Version 11.70, or later

The IBM Unified Debugger is also available with qualified support for the following releases of DB2. For detailed information, see Debugging CLR Applications against DB2 Version 8 Servers.

DB2 Universal Database™ for Linux^®, UNIX^®, and Windows^® Version 8
DB2 for z/OS^® Version 8 NFM and Version 9 in Version 8 CM

The IBM Unified Debugger debugs SQL, SQL PL, and SPL procedures through the IBM Procedure Designer. If a procedure definition is open in the designer when you start debugging an application, as the debugger steps into the procedure, that instance of the designer is activated. If the procedure definition is not open in the designer, as the debugger steps into the procedure, the debugger opens the procedure definition in a new instance of the designer.

Notes:

The following data types are not supported for debugging:
- Array
- (DB2 for z/OS Version 10 NFM) TIMESTAMP WITH TIMEZONE
- XML
If you debug a procedure that has a parameter or that accesses a column that is one of the unsupported data types, you cannot evaluate the value of the parameter or the column. A message that the expression is not supported is displayed in the Immediate window. After this occurs, you can continue debugging the rest of procedure.
You can debug LOB data (that is, data of type BLOB, CLOB, and DBCLOB). However, when you debug a procedure that has a parameter or that accesses a column of LOB data, you cannot modify the value of the parameter or the column. If you edit the data, it reverts to its original value.
When you debug LOB data, you can use visualizers to examine text, HTML, XML, decimal, hexidecimal, image and video data.

Prerequisite: To debug SQL, SQL PL, or SPL procedures in a CLR application, enable IBM SQL debugging in the project that contains the application.

To debug SQL or SPL procedures in a CLR application:

For each SQL, SQL PL, or SPL procedure in the application:
1. Enable debugging for the procedure.
  1. In the Server Explorer under your data connection, right-click the procedure that you want to debug, and then click Open Definition on the shortcut menu.
  2. In the Procedure view of the IBM Procedure Designer, select ALLOW in the Debug mode list.
  3. Save the procedure, and optionally leave the designer open to set line breakpoints.
2. Optional: Set line breakpoints in the IBM Procedure Designer.
  1. In the SQL Body section of the Procedure view in the designer, set line breakpoints.
  2. To set properties for a breakpoint, right-click the breakpoint in the left margin, select Location, Filter, or When Hit on the shortcut menu, and then specify the necessary information in the window that opens.
  3. Leave the designer open.
Start debugging the application. In the Solution Explorer, right-click the application, select Debug on the shortcut menu, and then select Start new instance.
If you are using a DB2 for Linux, UNIX, and Windows, Version 8.1, Fix Pack 15 (or later) server, the debugger client checks for the existence of all the stored procedures that are needed for debugging SQL procedures in CLR applications. If any of the procedures are missing, the Start Debugging window opens. Before you can start debugging, you must create the missing procedures.

Debugging starts and the Debugger Task Status window opens.

The call stack for each procedure that is being debugged is merged with the call stack for the thread that calls the procedure. You can see where the procedure is called from the C# or Visual Basic code.
Run each procedure in debug mode as it is called.
- If you closed the IBM Procedure Designer in step 1.a.iii, in the new instance of the designer, set line breakpoints and breakpoint properties for the procedure in the SQL Body section.
- Set variable breakpoints. In the SQL body, right-click a variable name, click Breakpoints on the shortcut menu, and then select Insert Variable Breakpoints.
- Modify variable breakpoint values.
To cancel a long-running task, click Cancel in the Debugger Task Status window.
If you closed the window while you were debugging your application, from the Tools menu, select Show IBM Debugger Task Status to reopen the window.
Continue debugging the application until all of the SQL procedures return the expected results.

Debugging SQL, SQL PL, and SPL Procedures in CLR Applications

See Also