Debugging stored procedures in DB2 z/OS with Optim Development Studio, Part 1: Use the Unified Debugger in a sample scenario

The Unified Debugger in DB2® product and the Optim™ Development Studio product provide a great toolset for anyone creating SQL native or external stored procedures as well as Java™ stored procedures. This article provides important background information about the Unified Debugger and shows you step-by-step how to use Optim Development Studio to create and debug a sample native SQL stored procedure. Typical problems are also addressed. The focus in this article is on DB2 for z/OS® stored procedures, but many of the concepts and steps also apply to DB2 servers on other platforms. [Nov 2010: Updated to reflect product change to Optim Development Studio and applicable product enhancements. --Ed.]

Tom Miller (millerrt@us.ibm.com), Senior Software Engineer, IBM

Tom MillerTom 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.



Hongdi Zhang (emilyz@us.ibm.com), Advisory Software Engineer, IBM

Emily ZhangHongdi 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.



18 November 2010 (First published 06 November 2008)

Also available in Chinese

Introduction

IBM Optim Development Studio V2.2.1 incorporates a Unified Debugger client and supports debugging routines, including stored procedures, user-defined functions, and PL/SQL packages. If you are familiar with debugging applications using the Eclipse platform, you will find debugging routines to be similar. In this article, you will explore how to use the debugging features in IBM Optim Development Studio V2.2.1 with a sample DB2 for z/OS SQL stored procedure. Many of the steps in this article can also be used for routines 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 and later.

To get the most out of this article, you should be familiar with using IBM Optim Development Studio 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 Optim Development Studio 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 technologies for DB2 for z/OS:

  • The older original technology is called the SQL Debugger, and it supports DB2 for z/OS V8 (with V8 PTF UK03933 from the same release date as DB2 for Linux, UNIX, and Windows 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 DB2 for z/OS V9 and later. 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 and later, the Unified Debugger supports debugging Java stored procedures, native SQL procedures, and external SQL stored procedures. For DB2 for z/OS V10, the Unified Debugger also supports debugging non-inline scalar user-defined functions. For DB2 V97, the Unified Debugger also supports debugging PL/SQL procedures, functions, and packages. In Optim Development Studio v2.2.1, you can debug stored procedures and user-defined functions from the Data Project Explorer or the Data Source Explorer.

The main difference between these two technologies is that the Unified Debugger supports debugging Java and SQL procedures, supports non-inline scalar user-defined functions, and can continue debugging into nested procedure calls. This article describes the Unified Debugger.

Preparing the sample table

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

Listing 1 shows the DDL you need 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.

Listing 1. DDL to create the sample product table
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);

Creating and deploying a stored procedure

In this section, you 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. You then specify that the stored procedure be deployed with the debugging option enabled. Without these actions, you cannot debug the stored procedure.

Create the procedure

Debugging an existing stored procedure

If you have any existing SQL stored procedure on the server, you can debug it directly on the Data Source Explorer, or you can drag and drop it from the Data Source Explorer to your data development project. However, make sure the stored procedure is prepared for debugging, which means that the stored procedure has already been deployed with the debug option.

If the stored procedure is not deployed with the debug option, you cannot debug it until you select Enable debugging and deploy the stored procedure again.

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

  1. Create a new data development project in Optim Development Studio using a DB2 for z/OS database connection. Select File > New > Data Development Project. The wizard asks you to select 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, as shown in Figure 1.
Figure 1. Choose a database connection
On the Select connection screen, new is selected.
  1. Use the new stored procedure wizard to create an SQL stored procedure. You can launch the new stored procedure wizard by right clicking the project you just created and selecting New > Stored Procedure, as shown in Figure 2.
Figure 2. Create a new SQL stored procedure
Screen shows Stored Procedure selected from the dropdown list
  1. Call the stored procedure PROFITUPDATE, and specify SQL as its language. In the Select a template section, select a native stored procedure template. For example, you can select the Deploy & Run: (Native) IN/OUT parameters template, as shown in Figure 3.
Figure 3. Select a template
screen shows Deploy and Run: (Native IN/OUT parameters) selected
  1. Preview the stored procedure by clicking on the DDL tab in the Preview section. When you are ready, click Finish to generate the default stored procedure. You will see the stored procedure you created open in the routine editor. When you examine the source, notice that it does not include the logic of calculating profit.
  2. Modify the body of the stored procedure to include the logic in Listing 2.
Listing 2. SQL statement for PROFITUPDATE stored procedure
SELECT COST, SHIPPING, PAYMENT FROM PRODUCT
WHERE ITEM = itemID;

To save time, you can instead copy and paste the body of the sample procedure from 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
  1. Click Save to save the procedure. Now you are ready to deploy the stored procedure.

Deploy the stored procedure

To deploy the stored procedure, right-click the procedure in the Data Project Explorer, and select Deploy. Make sure the Enable debugging checkbox is checked on the Routine Options window of the deployment wizard, as shown in Figure 4.

Figure 4. Enable debugging option in the deploy routines wizard
Routine Options screen shows enable debugging box checked

Setting 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 5. In this framework, the client talks to the session manager, and the session manager talks to the server to exchange debugging information and requests.

Figure 5. Unified Debugger components for client debugger IDE
shows client debugger IDE connecting to session manager at the DB2 admin server layer, which then connect to the DB2 server

The session manager is critically important because it is the middleware that coordinates the communication between client and server using a TCP/IP connection. The session manager needs to be set up and started somewhere in the network (at the client, at the server, or somewhere else) before you can use the debugger, otherwise the Unified Debugger will not work, as shown in Figure 6.

Figure 6. Session manager is not available
shows error message: Cannot ping the session manager

If your environment is set up to have a session manager running on the server, you can configure the debugger preferences to use it. In the z/OS environment, server-side setup is more complicated, so this article focuses on using the debugger on the client. See the Redbook™ listed in Resources for information about setting up your environment in DB2 for z/OS for stored procedure debugging.

What if session manager is not running locally?

Regardless of where your session manager is running on the network, the steps described in this section for setup are virtually the same except that you'll need to locate db2dbgm.bat on whichever server the session manager resides.

A built-in client session manager is integrated with Optim Development Studio V2.2.1. You can choose to use the built-in client session manager, which is set as the default in the debugger preferences page. You can also launch the session manager on the client yourself and share it with your team over the network.

Complete the following steps to run the session manager on the client.

  1. Locate the session manager program that is shipped with Optim Development Studio (ODS) V2.2.1. The file name is db2dbgm.bat, and it is located at [ODS install directory] \dsdev\bin.
  2. Execute db2dbgm.bat in a command prompt window. You will see a host IP address and port number, which you should write down. You also see timeout information. In the example db2dbgm.bat output shown in Figure 7, the session manager times out if it has been idle for 50 minutes.
Figure 7. Start a session manager and get IP address, port number, and idle value
db2dbgm.bat file contains args[3]: 50, indicating session manager will time out after 50 minutes
  1. Modify the idle time and port number by editing db2dbgm.bat. You can specify a value of 0 to request no session manager timeout. Now you can enter the IP address and port number of the running session manager in the session manager section of the debugger preferences.
  2. Select Window > Preferences.
  3. Find the debugger preferences page under Run/Debug > Routine Debugger > IBM.
  4. Click the Use an already running session manager radio button on the debugger preferences page, and enter the IP address and the port number from running db2dbgm.bat, as shown in Figure 8.
Figure 8. Specify session manager IP address and port number in preferences
Preferences screen shows radio button selected and 9.30.68.163 for Host address and 4554 for Port
  1. Leave the session manager command window open, because you don't want to end the session manager session yet. Proceed to debugging, and let the session manager window run in the background.

Setting up additional debugger preferences

Besides session manager, you might also want to configure other debugger preferences before you start debugging. This section describes the inactivity setting and error trace setting, as shown in Figure 9.

Figure 9. DB2 stored procedure debugger preferences
shows inactivity setting of 300 and error trace setting of 0 highlighted
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 after which it lets the procedure run to completion automatically. You can specify how long the debugger will remain inactive on the debugger preferences page. The default value is 300 seconds.
Error trace setting
Your IBM Service representative might 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.

Starting the debugging

Now that you completed the setup, you are ready to debug your stored procedure. To start debugging, complete the following steps.

  1. Right-click the stored procedure name in the Data Project Explorer, and select Debug from the list, as shown in Figure 10.
Figure 10. Start the debugger from a procedure in Data Project Explorer
shows Debug selected in dropdown list

Because your stored procedure has an input variable, the debugger brings up the Specify Parameter Values window.

  1. Enter input variable values. If you have entered values previously, modify the values if needed. Figure 11 shows the name itemid, type varchar(6), and a value of 002.
Figure 11. Specify Parameter Values window
shows input variable with the name itemid, type varchar(6), with a value of 002
  1. Click OK. Debugging takes place within the debug perspective. You see a window confirming that you want to switch to the debugging perspective, as shown in Figure 12.
Figure 12. Switch to the debugging perspective
Confirm perspective switch
  1. Click Yes to confirm that you want to go to the debugging perspective. Optionally, click the Remember my decision checkbox to remember your decision so that you won’t be prompted again.

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, as shown in Figure 13. The current position is highlighted with an arrow on the left.

Figure 13. Debugger stops at the first line
shows the stored procedure with the first line highlighted
  1. Debug the procedure, stopping at each execution line. Or if you set any breakpoints, you can continue to run to the next valid breakpoint.

Notice that the debug editor is different than the routine editor, because now the debugger gets the routine source from the server side, displays it in a temporary read-only editor, and debugs.

Because this is server-side debugging, you cannot set breakpoints in the routine editor ahead of time. However, as soon as the debugger stops at the first line of a routine, you can start setting breakpoints. The debugger also remembers the breakpoints you set in the previous debug session.


Understanding debugger perspective, views and operations

The Eclipse debug perspective offers four views to help you debug stored procedures, as shown in Figure 14 and described below:

Figure 14. Debugger perspective overview
shows fourpanes for the four views

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, including Step Into, Step Over, Step Return, Resume, and Terminate, as shown on the toolbar in Figure 15.

Figure 15. Debug view toolbar
shows the toolbar with icons from Table 1
Table 1. Debug view and its actions
ProcedureIconAction
Step Overicon for step overUse Step Over to debug the procedure line by line.
Step Intoicon for step intoUse Step Into to debug into a nested procedure. When no nested procedure is available, its function is same as Step Over.
Step Returnicon for step returnUse Step Return 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.
Resumeresume iconWhen you stop at a certain line, stop at the next valid breakpoint. If no more valid breakpoints exist, Resume runs to the end of procedure and stops debugging.
TerminateTerminate iconUse Terminate to stop debugging at any time.
Remove Terminated Launchesremove all terminated launches iconRemove all terminated launches.

If you have nested procedure calls, the debugger view can provide debugging information for both procedures, including the current line number in each procedure. Figures 16 and 17 show 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 can 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.

Figure 16. Nested stored Procedure1
shows procedure1 with the call to procedure2
Figure 17. Nested stored Procedure2
shows procedure2 with VAR1

Variables view

The variables view shows the current values of currently available variables and parameters, as shown in Figure 18. The Unified Debugger also shows SQLCODE and SQLSTATE diagnostic information.

Figure 18. Variables view
shows the current variables of 0 and null, with ITEMID 002 highlighted

The variables view enables you to do the following:

  • 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 selecting Change Value, as shown in Figure 19.
Figure 19. View and modify variable in the variables view
shows popup menu

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

You can set variable change breakpoints in the variables view. Right-click on a variable in the variable view, and select Add Variable Change Breakpoint, as shown in Figure 20.

Figure 20. Create a variable change breakpoint
shows popup menu

When the variable value changes during debugging, the debugger stops right after that line.

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. Figure 21 shows both types of breakpoints in the breakpoints view.

Figure 21. Breakpoints view
shows list of breakpoints such as ADMF001.PROFITUPDATE.2:Line 30 and ADMF001.PROFITUPDATE.2.dbg:Variable ITEMID

When you double-click the right margin of a line in the debug 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 sidebar in the editor, as shown in Figure 22.

Figure 22. Add breakpoint menu
menu shows Add Breakpoint highlighted

If a breakpoint is set on a line that contains a valid SQL procedure execution statement, the debugger stops 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.

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, as shown in Figure 23.

Figure 23. A line breakpoint
shows OPEN cursor1; highlighted in the stored procedure

Be careful when you set breakpoints for statements that cross multiple source lines. Use Step Over to get familiar with these valid breakpoint positions.

Data output view

The data output view shows the result of the procedure when debugging is complete, which is the same result as running the stored procedure. The data output view provides debug action status, run message, parameter values, and run results. If the results are not what you expect, you can debug your stored procedure again.

For the 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, as shown in Figure 24.

Figure 24. Data output view: Result of debug action
shows status Succeeded for Debug operation

Exploring other complications

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

Cannot find session manager

Sometimes even after you set up the session manager, IBM Optim Development Studio continues to report that the session manager is not available. Check that you have the session manager running at the server or elsewhere on your network. If the session manager is running, the TCP/IP connection could be blocked by a firewall or the IP address could be incorrect. Pinging the session manager IP address from the server is a good way to verify that 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, and the other is for the session manager. If the session manager has not been servicing a debug session for a specified amount of time, the session manager will shut down. To use the session manager again, restart it. A debug session that is not active will be released by the debug client. Be sure to adjust either setting to meet your needs.

The debugger menu is not enabled

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

  • You might not have deployed your stored procedure with the debugging option. To check this, open your procedure in the editor, and verify that the enable debugging checkbox is checked.
  • The database connection might not support the debugger. 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 server, you won’t be able to debug it within Optim Development Studio V2.2.1.

Conclusion

IBM Optim Development Studio 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.

Acknowledgments

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

Resources

Learn

Get products and technologies

Discuss

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=350254
ArticleTitle=Debugging stored procedures in DB2 z/OS with Optim Development Studio, Part 1: Use the Unified Debugger in a sample scenario
publish-date=11182010