Use the integrated debugger to debug SQL and SQL PL procedures as they run on a DB2^® server. You can step through your code, set line or variable breakpoints, view variable values, change variable values, view call stack information for nested procedures, and switch between different procedures on the call stack. By stepping through your code while running the procedure in debug mode and viewing the results, you can discover problems with your procedure and make the necessary changes.

Starting in Informix^® Data Server (IDS), Version 11.70, you can debug SPL procedures as they run on an IDS server. In IDS SPL procedures, you can debug only the SPL statements, not the SQL statements.

You can also debug versions of DB2 for z/OS^® native SQL procedures in the same way that you debug SQL procedures. This function is supported for DB2 for z/OS Version 9.1 or later.

Notes:

• The following data types are not supported for debugging:
  • Array
  • [DB2 for z/OS Version 10 new function mode (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 the 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.

To debug an SQL, SQL PL, or SPL procedure:

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, but leave the designer open.
2. Set line breakpoints in the IBM Procedure Designer.
   1. If the procedure is not open in the designer, in the Server Explorer under the data connection, right-click the procedure and then click Open Definition on the shortcut menu.
   2. In the SQL Body section of the Procedure view, set line breakpoints.
   3. 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. Start running the procedure in debug mode.
   • If the procedure is open in the IBM Procedure Designer, click Step Into on the IBM Procedure Designer toolbar.
   • If the procedure is not open in the designer, right-click the procedure in the Server Explorer, and then click Step Into on the shortcut menu.
4. Run the procedure in debug mode, and use either of the following methods:
   • 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.
5. Continue running the procedure in debug mode until the procedure returns the expected results.

See Also

Managing Procedures, Functions, and Objects | Debugging SQL and SPL Procedures in CLR Applications | Changing SQL and SPL Procedure Definitions

.NET Development Forum   developerWorks: Visual Studio .NET   DB2 FAQs

© Copyright IBM Corporation 2002, 2012. All Rights Reserved.