- Preparing the sample table
- Creating and deploying a stored procedure
- Setting up the debugger session manager
- Setting up additional debugger preferences
- Starting the debugging
- Understanding debugger perspective, views and operations
- Exploring other complications
- Downloadable resources
- Related topics
Debugging stored procedures in DB2 z/OS with Optim Development Studio, Part 1
Use the Unified Debugger in a sample scenario
This content is part # of # in the series: Debugging stored procedures in DB2 z/OS with Optim Development Studio, Part 1
This content is part of the series:Debugging stored procedures in DB2 z/OS with Optim Development Studio, Part 1
Stay tuned for additional content in this series.
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 Related topics).
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
Following are the steps to create the sample stored procedure called PROFITUPDATE.
- 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
- 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
- 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
- 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.
- 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
- 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
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
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
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 Related topics for information about setting up your environment in DB2 for z/OS for stored procedure debugging.
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.
- 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.
- 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
- 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.
- Select Window > Preferences.
- Find the debugger preferences page under Run/Debug > Routine Debugger > IBM.
- 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
- 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
- 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.
- 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
Because your stored procedure has an input variable, the debugger brings up the Specify Parameter Values window.
- Enter input variable values. If you have entered values previously,
modify the values if needed. Figure 11 shows the name
varchar(6), and a value of
Figure 11. Specify Parameter Values window
- 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
- 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
- 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
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
Table 1. Debug view and its actions
|Step Over||Use Step Over to debug the procedure line by line.|
|Step Into||Use Step Into to debug into a nested procedure. When no nested procedure is available, its function is same as Step Over.|
|Step Return||Use 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.|
|Resume||When 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.|
|Terminate||Use Terminate to stop debugging at any time.|
|Remove Terminated Launches||Remove 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
Figure 17. Nested stored Procedure2
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
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
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
When the variable value changes during debugging, the debugger stops right after that line.
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
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
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
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
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.
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.
We would like to thank Kathy Zeidenstein, Tina Chen, and other reviewers for their help during review of this article.
- Refer to the IBM Optim Development Studio product page.
- Explore the tutorial Get Started with Optim Development Studio for a great overview of IBM Optim Development Studio.
- Check out the Redbook DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond to help you design, install, manage, and tune stored procedures with DB2 9 for z/OS.
- Grow your skills with resources available on the Optim page on developerWorks.
- Download a trial version of Optim Development Studio.