IBM Data Studio, an Eclipse based graphical development tool, provides an integrated, modular environment for database development and administration of IBM DB2 for Linux, UNIX and Windows (LUW). Many developers use Data Studio for debugging database routines. Data Studio provides important information such as variable values and call stack information. Debugging routines using Data Studio is as convenient as debugging any Java application using the Eclipse IDE. Data Studio 4.1 includes new features for debugging DB2 LUW routines.
In this article, learn to use the new features in Data Studio and improve your productivity by quickly and easily debugging triggers, nested routines, and anonymous blocks. To follow along with the examples in this article, you need to install IBM Data Studio 4.1. The features are only available when debugging on DB2 LUW 10.1 or later.
Valid lines for breakpoints
With Data Studio, you can set breakpoints in the routine editor before launching the debugger. Some lines cannot have a breakpoint. Data Studio shows all the breakpoint candidate locations after the routine is deployed in debug mode, as in Figure 1. You no longer have to guess where to place breakpoints nor launch the routine debugger first before setting the breakpoints.
Figure 1. Show valid breakpoints
With breakpoints placed in a database routine, you can temporarily interrupt the routine execution and inspect variables, parameters, and the call stack. For routine debugging, you can place the breakpoints once you've stepped into a routine. For users of DB2 LUW 10.1 or later, you can set breakpoints before the routine is launched. Data Studio even shows all the valid breakpoint locations so you won't put breakpoints in locations where the routine execution cannot be interrupted.
Add breakpoints at the valid positions by double-clicking or right-clicking and selecting Add Breakpoint before launching the debugger. (You can always add breakpoints after the debugger has started.) You can also use the shortcut key Control+Shift+B to toggle the breakpoint. If you try to set breakpoints on an invalid location, Data Studio displays the message shown in Figure 2.
Figure 2. Alert for setting breakpoints at invalid lines
The flow of routine debugging
This section explains how to create and deploy a stored procedure, set breakpoints in the editor, and debug the stored procedure.
Create and deploy a stored procedure
Follow these steps to create the sample stored procedure:
- Create a new project in Data Studio by selecting File -> New -> Data Development Project. The wizard prompts you to select a database connection for your project. Create a database connection if no connection is available.
- Create a new stored procedure by right-clicking the Stored
Procedures folder on the project and selecting New ->
Stored Procedure. Set the stored procedure name to
SAMPLE_PROCEDUREand choose SQL as the language in the wizard. Modify the content of the stored procedure as in Listing 1.
Listing 1. Stored procedure example
CREATE PROCEDURE SAMPLE_PROCEDURE (INOUT num INT) L1: BEGIN DECLARE HV1 VARCHAR(999); DECLARE HV2 INT DEFAULT 99; DECLARE SQLCODE INT; DECLARE C2 CURSOR WITH RETURN FOR SELECT C1 FROM TBTG2213 Where C1 < 2; DECLARE EXIT HANDLER FOR SQLEXCEPTION H2: BEGIN SET NUM = SQLCODE; -- expected output parm value END H2; OPEN C2; FETCH C2 INTO HV2; IF HV2 = 1 THEN UPDATE TBTG2213 SET C1 = 3; END IF; SELECT C1 INTO NUM FROM TBTG2213 WHERE C1 = 3; END L1
- After the stored procedure is created, save it and deploy it by clicking
icon in the upper right of the editor, as highlighted in red in
Figure 3. A wizard helps you deploy the
Figure 3. Deploy stored procedure
- To debug the deployed stored procedure, check the box next to Enable debugging
in the Deploy Routines window of the deployment wizard, as
in Figure 4.
Figure 4. Enable debugging
After the stored procedure is successfully deployed, the editor displays hints for valid positions for breakpoints. You can add breakpoints at the valid lines. The example adds breakpoints at lines 6, 12, and 16, as shown in Figure 5.
Note: You might not see the valid lines for DB2 z/OS, but you can still debug it and set breakpoints after the debug session has been launched.
Figure 5. Valid breakpoints
Debug the stored procedure
After the stored procedure is deployed, start the debugger by clicking the Debug icon in the upper right of the editor, as highlighted in red in Figure 6.
Figure 6. Start the debugger
When the debugger starts, Data Studio switches to debug perspective and the execution suspends at the first breakpoint encountered. Figure 7 shows the debug perspective. The execution suspended at line 6, with the green background, where we put a breakpoint.
You can control the execution using the Resume, Stop, Step over, Step into, and Step return icons from the tool bar, as highlighted in red boxes in Figure 7.
- Resume: Jump to the next breakpoint to suspend.
- Stop: Cancel the debug session.
- Step over: Jump to the next stoppable line regardless of breakpoints.
- Step into: Suspend in the first stoppable line of the current line if it's the caller of another function or stored procedure.
- Step return: Back to the caller and suspend at where it stepped into the callee.
Figure 7. Debug perspective
Debug nested routines
Adding breakpoints in the editor before launching a routine makes it easier to debug nested routines. Data Studio supports both SQL and PL/SQL nested routines. See Resources for information on debugging SQL nested routines.
In this section, an example PL/SQL nested stored procedure shows how to use the debugger to debug nested routines and how to check the routine's status in different scopes.
Create and deploy a nested stored procedure
Follow the same steps you took in Create and deploy a stored procedure to create the parent stored procedure.
Set the stored procedure name to
PARENT_PROCEDURE and choose
PL/SQL as the language in the wizard. Modify the content of the stored
procedure as in Listing 2.
Listing 2. Parent stored procedure
CREATE OR REPLACE PROCEDURE PARENT_PROCEDURE AS var1 int; PROCEDURE nested_procedure AS var1 int; var2 int; BEGIN var1 := 1; var2 := 2; END; PROCEDURE another_nested_procedure AS var3 int; BEGIN var3 := 3; END; BEGIN nested_procedure; another_nested_procedure; END PARENT_PROCEDURE
After the stored procedure is created, save it and deploy it by clicking the Deploy icon in the upper right of the editor, as shown in Figure 3.
To debug the deployed stored procedure, check the box to Enable debugging in the Deploy Routines window of the deployment wizard, as in Figure 4.
After the stored procedure is successfully deployed, the editor displays hints for
valid positions for breakpoints, as in Figure 8. In nested stored
you can set breakpoints on the line either in the parent stored procedure or inside of
the nested stored procedure. The debugger can stop at any valid breakpoint
even if it is
inside of the inner stored procedure. In the following example, you can add a breakpoint at
line 10 to suspend execution inside the stored procedure
Figure 8. Valid breakpoints
After the stored procedure is deployed, start the debugger by clicking the Debug icon in the upper right of the editor, as in Figure 6.
When the debugger starts, Data Studio switches to debug perspective and the execution suspends at the first breakpoint encountered. Figure 9 shows the debug perspective. You can see that the execution suspended in the inner stored procedure at line 10 (in the red box).
Figure 9. Debug perspective
View debug information in different scopes
The Debug view provides information about the current call stack, shown in the green box in Figure 9. It provides the names of the stored procedures and current line number for each stored procedure. In the example, execution suspends at line 10 for the inner stored procedure NESTED_PROCEDURE and on line 20 in its parent stored procedure PARENT_PROCEDURE.
When you click on the stored procedure name in the Debug view, the suspended line for that stored procedure is highlighted and the variables for the selected stored procedure are displayed in the Variables view in the blue box in Figure 9. By switching the stored procedure in the Debug view you can view variables in different scopes.
After executing the current line by selecting the Step
over icon from the
menu bar (in the yellow box in Figure 9), the statement on the
currently-selected line is executed. Execution suspends on the next executable
statement, as in Figure 10. Line 10 is performed after clicking the Step over
icon and the debugger brings you to line 11. The variable
var1 is set
to 1, as shown in the Variables view.
Figure 10. Variables in NESTED_PROCEDURE
var1 is declared in both the parent stored procedure and
the nested stored procedure, you can check the value of
var1 in different scopes by selecting the
stored procedure in the Debug view and watching the value in the Variables view. As
shown in Figure 11, the value of
var1 declared in the parent stored procedure is still
null although the
var1 declared in the inner stored procedure has already been assigned a
Figure 11. Variables in PARENT_PROCEDURE
Return to parent stored procedure
To jump to the parent stored procedure of the current nested stored procedure, click the Step return icon (in the yellow box in Figure 9). The execution will resume at the next line in the parent stored procedure unless another breakpoint of the current nested stored procedure is encountered.
At line 11, if you select the Step return action the debugger takes you to line 21, which is another call to the nested stored procedure. Figure 12 shows the example.
Figure 12. Return to parent stored procedure
Step into a nested stored procedure
When the execution suspends on a call to another nested stored procedure and you want to debug this nested stored procedure, use the Step into icon to go into the nested stored procedure.
In the example, after you click the Step into
icon the debugger brings you to the beginning of
another_nested_procedure, as in Figure 13. When the
debugging of this nested stored procedure completes, the debugger brings you back to the
parent stored procedure.
Figure 13. Step into a another nested stored procedure
With Data Studio you can easily debug triggers by setting breakpoints in the editor and executing a statement in the SQL Editor to fire the trigger. This section provides step-by-step instructions for debugging a trigger.
Create a trigger
A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. Currently, you can create triggers from Data Source Explorer in Data Studio.
In this example, we'll create a trigger on table TEST_TRIGGER_TABLE. The table definition is in Listing 3.
Listing 3. Table definition
CREATE TABLE TEST_TRIGGER_TABLE ( id INT, name VARCHAR(10), val INT )
Insert one row into the table, as in Listing 4.
Listing 4. Insert data into the table
INSERT INTO TEST_TRIGGER_TABLE VALUES (1, 'JAY', 10)
The function of the trigger is to disallow users to update the
with a bigger value than the old value. The definition of the trigger is shown in
Run the SQL statement in the SQL Editor to create the trigger.
Listing 5. Definition of the trigger
CREATE OR REPLACE TRIGGER BEFORE_UPDATE_TABLE BEFORE UPDATE OF val ON TEST_TRIGGER_TABLE REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.val > O.val) BEGIN DECLARE newVal,oldVal INTEGER; SET newVal = N.val; SET oldVal = O.val; SET N.val = oldVal; END
Deploy trigger in debug mode
The trigger BEFORE_UPDATE_TABLE is under the TEST_TRIGGER_TABLE in Data Source Explorer after the create script is run, as in figure 14.
Figure 14. Trigger in Data Source Explorer
You need to redeploy the trigger in debug mode. From the context menu of BEFORE_UPDATE_TABLE, select Open With Routine Editor, as in Figure 15.
Figure 15. Open trigger with routine editor
Click the Deploy icon from the routine editor tool bar (highlighted in orange), as in Figure 16.
Figure 16. Deploy the trigger
Deploying a trigger in debug mode is similar to deploying a routine, as in Figure 17.
Figure 17. Enable debugging mode
After successfully deploying the trigger, set a breakpoint at line 8, as in Figure 18.
Figure 18. Set breakpoints for the trigger
Debugging a trigger
To debug the trigger you need to execute an SQL statement to fire the trigger. Open the SQL Editor and enter the following statement:
UPDATE TEST_TRIGGER_TABLE SET VAL=900 WHERE id=1
Highlight the statement, as in Figure 19, then click the Debug icon in the SQL Editor (in the upper right and highlighted in the red box) to launch the debug session.
Figure 19. Launch debugger
When the SQL statement matches the conditions of the trigger's definition, the debugger will be launched. Data Studio then switches to the debug perspective and stops at the first breakpoint. In our example the execution will suspend at line 8. Just like debugging any routine, you can perform any debug operation such as Step over or Step into. You can also examine the variables defined in the trigger and their values from the Variables view, as in Figure 20.
Figure 20. Debug trigger
Debug an anonymous block
Sometimes you might want to debug a block of SQL statements quickly without creating a new routine. With anonymous block debugging in Data Studio, you can easily and quickly debug a section of lines without creating a new routine. By entering an anonymous block in the SQL Editor you can debug anonymous blocks just like you would debug any other routines. You can even debug nested routines. For example, input the script in Listing 6 in the SQL Editor.
Listing 6. Definition of the block
DECLARE VARCOUNT INT; BEGIN SELECT COUNT(*) INTO VARCOUNT FROM SYSIBM.SYSTABLES WHERE CREATOR = 'DB2ADMIN' AND NAME LIKE VARNAME; db2admin.dsff(); return; END
Because the semicolon is the default statement terminator, you need to change the statement terminator to another character, such as @, so the parser won't treat the block as multiple statements. Click the Debug icon in the toolbar to launch the debug session, as in Figure 21.
Figure 21. Set terminator and launch debugger
Unlike the routine editor, you cannot specify breakpoints in the SQL Editor. Breakpoints can be specified after the debug session starts, as in Figure 22.
Figure 22. Add breakpoints for anonymous block
If the anonymous block calls another routine, you can Step Into that routine, as in Figure 23 and Figure 24.
Figure 23. Step Into the sub-routine
Figure 24. Debug into sub-routine
This article demonstrates enhancements to Data Studio 4.1 to debug routines, triggers, and anonymous blocks. With the new features you can accelerate your development when debugging complex routines and triggers.
- IBM Data Studio: Explore the features, what's new, resources, and downloads.
- "Debugging stored procedures in DB2 z/OS with Data Studio, Part 1" (developerWorks, 2010): Use the Unified Debugger in a sample scenario.
- "Debugging stored procedures in DB2 z/OS with Data Studio, Part 2" (developerWorks, 2011): Configure the stored procedure debug session manager on z/OS.
- Visit the developerWorks Information Management zone to find more resources for DB2 developers and administrators.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.