Speed up debugging of triggers, nested routines, and anonymous blocks with IBM Data Studio

IBM® Data Studio is an Eclipse-based development tool for database developers and administrators. It offers a wide variety of features, including tools for debugging complex routines. Data Studio 4.1 has new features for debugging DB2® for Linux®, UNIX®, and Windows® (LUW) routines. In this article, learn to use the new features to debug triggers, nested routines, and anonymous blocks quickly. Accelerate your development of routines with Data Studio 4.1.

Peng Wang, Software Engineer, IBM

Peng WangPeng Wang joined the IBM China Development Lab in 2008. He currently works as a software developer on the Data Studio team. He has extensive experience in J2EE, FLEX, and Eclipse Plugin development. He has a Master's degree in Computer Science and is IBM DB2 DBA certified and SUN SCJP certified.



Ke Chen, Software Engineer, IBM

Ke ChenKe Chen, a software engineer at the IBM China Development Lab in Beijing, works on Data Studio development. She has been working on DB2 tools development for several years. Ke has published several papers on Data Studio, Flex, and mobile web development on the developerWorks China site. She holds a master's degree in computer science from Shanghai Jiaotong University, Shanghai.



Qi Min, Software Engineer, IBM

Qi MinQi Min is an software engineer at the IBM China Development Lab. He currently works on Data Studio development, focusing on Data Studio installation development.



13 March 2014

Overview

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.

Download IBM Data Studio available at no charge and with no time restrictions.

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
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
Alert when the user sets 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:

  1. 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.
  2. 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_PROCEDURE and 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
  3. After the stored procedure is created, save it and deploy it by clicking the Deploy icon in the upper right of the editor, as highlighted in red in Figure 3. A wizard helps you deploy the stored procedure.
    Figure 3. Deploy stored procedure
    Deploy stored procedure button

    Click to see larger image

    Figure 3. Deploy stored procedure

    Deploy stored procedure button
  4. 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
    Enable debugging option in deployment wizard

Set breakpoints

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
Valid breakpoints

Click to see larger image

Figure 5. Valid breakpoints

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
Click debug button

Click to see larger image

Figure 6. Start the debugger

Click debug button

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 perspective

Click to see larger image

Figure 7. Debug perspective

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 procedures, 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 nested_procedure.

Figure 8. Valid breakpoints
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
Debug perspective

Click to see larger image

Figure 9. Debug perspective

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
Variables in NESTED_PROCEDURE

Click to see larger image

Figure 10. Variables in NESTED_PROCEDURE

Variables in NESTED_PROCEDURE

Because 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 value.

Figure 11. Variables in PARENT_PROCEDURE
Variables in PARENT_PROCEDURE

Click to see larger image

Figure 11. Variables in PARENT_PROCEDURE

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
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
Step into a another nested stored procedure

Debug triggers

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 val field with a bigger value than the old value. The definition of the trigger is shown in Listing 5.

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
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
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
Deploy the trigger

Deploying a trigger in debug mode is similar to deploying a routine, as in Figure 17.

Figure 17. Enable debugging mode
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
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
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 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
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
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
Step into the sub routine
Figure 24. Debug into sub-routine
Debug into sub-routine

Summary

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.

Resources

Learn

Discuss

  • Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

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=965122
ArticleTitle=Speed up debugging of triggers, nested routines, and anonymous blocks with IBM Data Studio
publish-date=03132014