Introduction

IBM Data Studio Developer V1.2 incorporates a Unified Debugger client and supports debugging SQL stored procedures. If you are familiar with debugging applications using the Eclipse platform, you will find debugging DB2 SQL procedures to be similar. In this article, explore how to use the debugging features in IBM Data Studio Developer V1.2 with a sample DB2 for z/OS SQL stored procedure. Many of the steps in this article can also be used for SQL stored procedures created on DB2 for Linux®, UNIX®, and Windows®, and DB2 for i. The sample stored procedure used in this article is specific to DB2 for z/OS v9.

To get the most out of this article, you should be familiar with using IBM Data Studio Developer to create database connections and to work with database objects, such as tables and stored procedures. A good way to become familiar with the IBM Data Studio Developer is to download the trial version and read its tutorial series (see Resources).

Understanding the debugger technologies in different DB2 versions

There are two types of IBM debugger technology for DB2 for z/OS:

• The older original technology is called the SQL Debugger and supports DB2 for z/OS V8 (with V8 PTF UK03933 from the same release date as LUW V8 FP7). It provides debugger support for external SQL stored procedures only.
• The Unified Debugger, which is the latest debugger technology, supports both DB2 for z/OS V8 (with PTF UK25860 from the same release date as DB2 for Linux, UNIX, and Windows V9 FP2) and also DB2 for z/OS V9. For DB2 for z/OS V8, the Unified Debugger can be used to debug external SQL stored procedures. However, for DB2 for z/OS V9, it supports debugging Java stored procedures, native SQL procedures and external SQL stored procedures.

The major difference between these two technologies is that the Unified Debugger supports debugging both Java and SQL procedures and can continue debugging into nested procedure calls. This article discusses the Unified Debugger.

Preparing the sample table

The stored procedure created in this article is written in SQL-native and, based on an item ID as input, calculates the profit of an item using the cost, shipping fee, and the customer’s payment information stored in the PRODUCT table. The output of the stored procedure is called PROFIT and is stored back into the PRODUCT table.

Listing 1 shows the DDL you need to execute to create and populate the PRODUCT table that the sample stored procedure will reference. If you want to validate your SQL statement in the procedure, you need to run this DDL first.

CREATE TABLE ADMF001.PRODUCT 
( 
 ITEM VARCHAR(6) NOT NULL,
 COST DECIMAL(8,2), 
 SHIPPING DECIMAL(8,2),
 PAYMENT DECIMAL(8,2),
 PROFIT DECIMAL(8,2)
 );

INSERT INTO ADMF001.PRODUCT (ITEM, COST, SHIPPING, PAYMENT)
  VALUES ('001', 100.00, 19.99, 150);

INSERT INTO ADMF001.PRODUCT (ITEM, COST, SHIPPING, PAYMENT)
  VALUES ('002',80, 8.99, 99.99);

				

Create and deploy a stored procedure

In this section, use the Stored Procedure wizard to generate a simple version of the sample stored procedure. You need to modify the generated stored procedure to add the logic for calculating profit. Then learn how to specify that the stored procedure be deployed with the debugging option enabled. If you don't do this, you can't debug the stored procedure.

Create the procedure

Here are the steps to create the sample stored procedure called PROFITUPDATE.

1. First, create a new Data Development Project in Data Studio Developer using a DB2 for z/OS database connection. You can do that by selecting File > New > Data Development Project. The wizard will ask you to specify a database connection. If you don’t have database connection available yet, you can create a DB2 for z/OS database connection by following the database connection wizard.
   
   Figure 1. Choose a database connection
   
   
   
2. Next, use the New Stored Procedure wizard to create a SQL stored procedure. You can launch the New Stored Procedure wizard by right clicking on the project you just created, and selecting New > Stored Procedure as Figure 2 shows. Call the stored procedure PROFITUPDATE and specify SQL-native as its language.
   
   Figure 2. Create a new SQL stored procedure
   
   
   
3. When you get to the SQL Statements page, add the SQL statement shown in Listing 2 to the Statement details field. Note that the variable name has a colon (:) in front of it, which defines it as a host variable.
   
   Listing 2. SQL statement for PROFITUPDATE stored procedure
   

   SELECT COST, SHIPPING, PAYMENT FROM PRODUCT WHERE ITEM = :itemID;

   
   
   
   Figure 3. SQL Statements page
   
   
   
4. Click the Validate button to validate the SQL syntax. In case your syntax has an error, it will let you know before you write the statement into the procedure. In addition, it will also help you format your SQL statement. Figure 3 shows how the statement looks like after validation. Click Next.
5. On the Parameters page (Figure 4), you will notice an input variable, itemID, has already been created based on the host variable :itemID. In this page, you have chance to modify the existing parameters, or add a new parameter, or even remove a parameter that you don’t need any more.
   
   Figure 4. Parameters page
   
   
   
6. Now you must add the second parameter (the output parameter) by clicking the Add… button. This parameter is named itemProfit, with OUT mode, and type is Decimal (8, 2), as Figure 5 illustrates. Click OK.
   
   Figure 5. Add a parameter
   
   
   
7. On the Deploy Options page of the wizard, you get your first opportunity to enable debugging, by checking the Enable debugging checkbox Figure 6 shows. Click Finish.
   
   Figure 6. Ensure the Enable debugging checkbox is checked
   
   
   

Now the stored procedure you created should open up in the editor as Figure6-1 shows.

When you examine the source, notice that it does not include the logic of calculating profit. You need to modify the body of the stored procedure to include this logic. To save time, you can copy and paste the body of the sample procedure as shown in Listing 3.

Listing 3. Stored procedure to calculate profit

				
CREATE PROCEDURE PROFITUPDATE ( IN itemID VARCHAR(6),
                                OUT itemProfit DECIMAL(8, 2) )
	VERSION VERSION1 
	ISOLATION LEVEL CS 
	LANGUAGE SQL
	ALLOW DEBUG MODE 
	WLM ENVIRONMENT FOR DEBUG MODE WLMENV1
	QUALIFIER ADMF001
	COMMIT ON RETURN YES
------------------------------------------------------------------------
-- SQL Stored Procedure 
	-- itemID  
	-- itemProfit 
------------------------------------------------------------------------
P1: BEGIN
	-- Declare variables
	
	DECLARE itemPayment DECIMAL(8,2) DEFAULT 0.0;
	DECLARE total_cost DECIMAL(8,2) DEFAULT 0.0;
	
	P2: BEGIN
		DECLARE itemCost DECIMAL(8,2) DEFAULT 0.0;
		DECLARE itemShippingFee DECIMAL(8,2) DEFAULT 0.0;
		
		-- Declare cursor
		DECLARE cursor1 CURSOR FOR
			SELECT COST, SHIPPING, PAYMENT FROM PRODUCT 
			WHERE ITEM = itemID;

		OPEN cursor1;
		
		FETCH cursor1 INTO itemCost, itemShippingFee, itemPayment;
		SET total_cost = itemCost + itemShippingFee;
		
	END P2;
	
	SET itemProfit = itemPayment - total_cost;
	UPDATE PRODUCT SET PROFIT = itemProfit 
		WHERE ITEM = itemID;
END P1
				
				

Deploy the stored procedure

To deploy the stored procedure, right click the procedure in the Data Project Explorer, and select Deploy. Because you created this procedure in the last step with debugging enabled, it will be deployed with the debug option enabled. If you hadn’t already done that, you could still click the Enable debugging checkbox on the Routine Options section of the deployment wizard (Figure 7).

Set up the debugger session manager

Before you debug a stored procedure, it’s helpful to understand the debugger framework. The Unified Debugger has three components: server, client, and session manager, as shown in Figure 8. In this framework, the client talks to the session manager, and the session manager talks to the server to exchange debugging information and requests.

As you can see, the session manager is critically important because it is the middleware that coordinates the communication between client and server via TCP/IP connection. It’s a process that needs to be started somewhere in the network -- at the client, the server or somewhere else -- before you can use the debugger. If you don’t have it set up properly, you will run into a problem with IBM Data Studio Developer since the session manager is not available (Figure 9) and you will not be able to use the Unified Debugger without it.

If your environment is set up to have a session manager running on the server, there is nothing you need to do on the client side. However, especially in the z/OS environment, server-side setup is more complicated so this article focuses on using the debugger on the client side and cover server-side setup for z/OS in another article. In the meantime, we recommend the Redbook listed in Resources for information about setting up your environment in DB2 for z/OS for stored procedure debugging.

Again, the easiest way to use session manager is to run it on the client, then configure the debugger Preferences page. To run the session manager on the client:

1. Locate the session manager program that is shipped with Data Studio Developer v1.2. The file name is called db2dbgm.bat. It is located at following directory.

   [DS install directory] \dsdev\bin

   
   
2. Execute db2dbgm.bat under a Command Prompt window. It will provide you with a host IP address and port number, which you should write down. Its setting also includes timeout information. In the example db2dbgm.bat output shown in Figure 10, it indicates that the session manager will time out if it has been idle for 50 minutes. (Specify a value of zero to request no session manager timeout.) You can modify the idle time and port number by editing db2dbgm.bat.
   
   Figure 10. Start a session manager and get IP address and Port number
   
   
   
3. Now that you have the IP address and port number of the running session manager, you need to enter that same information in the session manager section in the debugger preferences:
   1. Select Window>Preferences…
   2. Find the debugger preferences page under Run/Debug > DB2 Stored Procedure Debugger.
   3. Click the Use already running session manager radio button on the debugger preferences page, and enter the IP address and the port number you copied previously.
   
   
   Figure 11. Specify session manager IP address and Port number in Preferences
   
   
   

Note: Do not close the session manager command window because that ends the session manager session. So you should proceed to debug and let the session manager window run in the background.

Set up additional debugger preferences

Besides session manager, you might also want to configure other debugger preference before you start debugging. This section provides a brief description of two of those options, shown in Figure 12.

• Inactivity setting: When you let debugger stop at a breakpoint and do not take any further debug actions, debugging is considered idle. To prevent holding server resources indefinitely, the debugger assumes you are finished with debugging after a defined period of inactivity, and lets the procedure run to completion automatically. You can specify how long the debugger remains inactive on the Debugger preference page. Its default value is 300 seconds.
• Error trace setting: Your IBM Service representative may ask you to activate diagnostic tracing at the server to assist in problem determination. A value of 2 engages full diagnostic tracing at the DB2 server. Leave the value at 0 (zero) unless directed by IBM Service to activate a server diagnostics trace.

Optional: Set Run settings before debugging

Finally, as one last optional step before debugging, you specify certain settings that apply to this run, including:

• Specifying certain SQL statements to be run before or after debugging (These statements will not be debugged)
• Setting an input parameter value
• Whether to commit the changes to the database
• Setting the collection ID for a connection to DB2 for z/OS

The sample stored procedure updates the PRODUCT table at the end. It is a good idea to verify that update by having a run-after statement that displays the results of a SELECT statement on the PRODUCT table. This section describes how to do that.

1. Open the Run Settings window by right clicking on the stored procedure in the Data Project Explorer as Figure 13 shows.
   
   Figure 13. Run settings menu
   
   
   
2. The Run settings window has multiple tabs. Select the After Run tab and enter the select * from PRODUCT statement shown in Figure 14.
   
   Figure 14. Run settings dialog window
   
   
   

After running or debugging the stored procedure, this SELECT statement is executed. The result of the profit update will show in the output view.

Note: In the Run Settings dialog, you can also specify parameter values in the Parameter Values tab. However, if you don’t do it here, you still have a chance to specify parameter values during debugging.

Back to top

Start debugging

Now that you’ve completed all the setup activity, it’s time to debug your stored procedure.

1. To start debugging, right click on the stored procedure name in the Data Project Explorer and then select Debug… as shown below in Figure 15.
   
   Figure 15. Start the debugger from a procedure in Data Project Explorer
   
   
   
2. Debugger first launches the standard Eclipse debug configuration page as shown in Figure 16. All entries are pre-filled. Click on Debug to continue.
   
   Figure 16. Debug configuration page
   
   
   
3. Because your stored procedure has an input variable, the debugger brings up the Specify Parameter Values dialog so that you can enter a value. If you have entered a value previously in the Run Settings dialog, the value shows up again for your review or modification.
   
   Figure 17. Specify Parameter Values dialog
   
   
   
4. Debugging takes place within the Debug perspective. The following window confirms with you before switching to Debugging perspective. Click Yes to continue. Optionally, click the checkbox to remember your decision so that you won’t be asked this again.
   
   Figure 18. Switch to Debugging Perspective
   
   
   

Now the debugger starts working. There is always an implied breakpoint set ahead of the stored procedure logic at the line containing the PROCEDURE keyword. The current position is highlighted with an arrow. You can debug the procedure stopping at each execution line. Or if you have set any breakpoints, you can continue to run to the next valid breakpoint.

Back to top

Debugger perspective, views and operations

The Eclipse Debug perspective (Figure 20) offers several views to help you debug stored procedures. These views are reviewed in this section.

• Debug view
• Variables view
• Breakpoints view
• Data Output view

Debug view

The Debug view shows the current call stack. It provides the stored procedure name and current line number. In the Debug view, you can take different debugging actions as shown on the toolbar in Figure 21, including Step Into, Step Over, Step Return, Resume, and Terminate:

• Use Step Over to debug the procedure line by line.
  
  
  
  
• Use Step Into to debug into a nested procedure. When no nested procedure is available, its function is same as step over.
  
  
  
  
• Step Return helps you to return from a nested procedure. If you are not in a nested procedure, Step Return runs the procedure to completion and ignores all the breakpoints.
  
  
  
  
• When you stop at certain line, Resume lets you stop at the next valid breakpoint. If no more valid breakpoints exist, resume runs to the end of procedure and stops debugging.
  
  
  
  
• Use Terminate to stop debugging at any time.
  
  
  
  
• Finally, you can remove all terminated launches by clicking this icon:
  
  
  
  

More on nested procedures: If you have nested procedure calls, the debugger view can provide debugging information for both procedures, including the current line number in each procedure.

Figure 22 shows an example of debugging a nested stored procedure in which Procedure1 calls Procedure2. At the line of call procedure2(var1), if you select step into action, the debugger takes you into Procedure2. You start debugging Procedure2 from the first line. As you can see in the call stack, Procedure2 is on top of Procedure1 now. When you finish debugging Procedure2, the debugger brings you back to Procedure1.

Variables view

The Variables view (Figure 23) shows the current values of currently available variables and parameters. The Unified Debugger also shows SQLCODE and SQLSTATE diagnostic information.

The Variables view lets you:

• Specify a variable change breakpoint.
• Copy and paste all the variable values to other files
• Change the value of variables for debugging purposes. You can do this either by double clicking the variable value and entering a new value, or by right clicking the variable and using selecting Change Value… from the popup menu as shown in Figure 24.

You cannot change the value of diagnostic information SQLCODE or SQLSTATE. You cannot set variable change breakpoint on them either. They are different than the normal variables.

Breakpoints view

The Breakpoints view shows all the breakpoints that have been set for the stored procedure. The Breakpoints view provides standard breakpoint management support, including:

• Enable/disable breakpoints
• Remove breakpoints
• Import/export breakpoints

The Unified Debugger supports two types of breakpoints: line breakpoints and variable change breakpoints. In the following figure, both types of breakpoints are shown in the Breakpoints view.

Adding a new breakpoint: When you double-click on the right margin of a line in the editor, a line breakpoint is set for that line. You can also set the line breakpoint by using the Add Breakpoint menu which appears when you right-click the left side bar in the editor.

If a breakpoint is set on a line that contains a valid SQL procedure execution statement, the debugger can stop at that line during debugging. Some source lines and SQL procedure statements are not valid for breakpoints, such as a line of comments or a cursor declaration. Also, each SQL procedure statement has only one keyword that defines the breakable point for that statement. Generally, a valid breakpoint is the first keyword of an SQL procedure statement. Be careful setting breakpoints for statements that cross multiple source lines. Use Step Over to get familiar with these valid breakpoint positions.

Variable change breakpoints can be set in the Variables view. Right click on a variable in the Variable view, and select Add Variable Change Breakpoint. When the variable value changes during debugging, the debugger stops right after that line.

Data Output view

The Data Output view shows the result of the procedure when debugging is done. It is the same result as if you ran the stored procedure. It provides the information of debug action status, run message, parameter values, and run results. The output view helps you exam the result of running a stored procedure. If the result is not what you expect, you can go back to debug your stored procedure.

With our sample stored procedure, the profit of the second item (item 002) is 11.00. This profit value is shown in the OUT mode parameter itemProfit, under the Parameters tab in the Data Output view.

Remember how you set the after-run statement in the Run Settings? The result of after-run execution is also shown in the Data Output view. As you can see in Figure 30, the PRODUCT table contents are listed under the Results tab. A profit value of 11.00 is saved for item 002 in the table.

Back to top

Other things you need to know

So far you’ve learned how to prepare the session manager for debugging, how to create a stored procedure, how to deploy it with debug option, and how to debug it by examining the variable values and checking the result. This section describes some additional situations that we see frequently when helping people with debugging on DB2 for z/OS.

Cannot find session manager

As stated earlier, you can set up the session manager on a client, the server, or somewhere else on the network. Sometimes, even after you have set up the session manager, IBM Data Studio Developer continues to report the session manager is not available.

If you see this problem and you have the session manager running at the server or elsewhere on your network, the TCP/IP connection could be blocked by a firewall. Also, sometimes the IP address is not correct. Pinging the session manager IP address from the server is a good way to verify the TCP/IP connection pathway is usable.

Time out issue

There are two timeout settings used with the Unified Debugger. One is for the debug client; the other is for the session manager. If the session manager has not been servicing a debug session for certain time, it will shut down. To use the session manager again, it must be started. A debug session that is not active is released by the debug client. Be sure to adjust either setting to meet your needs.

When the Debugger menu is not enabled

Sometimes the debugger menu is disabled. This can be the result of the following two situations:

• First, you may not have deployed your stored procedure with the debugging option. To check this, you can open your procedure in the editor, and then verify the enable debugging checkbox is checked.
• Secondly, it could be the database connection doesn’t support debugger. As mentioned above, debugger support for DB2 for z/OS starts on V8 with PTF UK03933. If you make a connection to a DB2 for z/OS V7, you won’t be able to debug it within Data Studio Developer V1.2.

Summary

IBM Data Studio Developer provides debugging features for SQL stored procedures similar to the debugging features for applications written in Java development in the Eclipse framework. This article shows you how to use the debugging technology to debug SQL stored procedures on DB2 for z/OS, although many of the same concepts apply to other DB2 servers as well.

Acknowledgements

We would like to thank Kathy Zeidenstein, Tina Chen and other reviewers for their help during review of this article.

Resources

Learn

• Use an RSS feed to request notification for the upcoming articles in this series. (Find out more about RSS feeds of developerWorks content.)
  
  
• Check out the IBM Data Studio product page to learn all about how this product can benefit you and your business.
  
  
• Get Started with Data Studio Developer: This tutorial is a great overview of Data Studio Developer .
  
  
• DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond: This IBM Redbook helps you design, install, manage, and tune stored procedures with DB2 9 for z/OS.
  
  

Get products and technologies

• Download a trial version of Data Studio Developer.
  
  
• Download more Data Studio products.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Data Studio Forum on developerWorks.
  
  
• Data Studio Community Space on developerWorks.
  
  
• Data Studio Team Blog on developerWorks.
  
  
• IBM Unified Debugger Space on developerWorks.
  
  

About the authors

Tom Miller is a senior software engineer at the IBM Silicon Valley Laboratory in San Jose and an IBM veteran since 1982. Since 2001, Tom has been a member of the DB2 for z/OS Development team, leading the SQL stored procedure technologies. Tom is a member if the IBM Unified Debugger development team, bringing the Unified Debugger technology to Java and SQL procedures on DB2 for z/OS. Prior to his current assignment, Tom was the QMF Architect.

Emily Zhang is an advisory software engineer for IBM's Application Development Tooling organization at Silicon Valley Laboratory in San Jose, CA. She is a member of the development team for IBM Data Studio. Her areas of expertise include routine tooling development and unified debugger.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Preparing the sample table
• Create and deploy a stored procedure
• Set up the debugger session manager
• Set up additional debugger preferences
• Optional: Set Run settings before debugging
• Start debugging
• Debugger perspective, views and operations
• Other things you need to know
• Summary
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers