Ibm data studio developer v1.2 includes a unified debugger client that supports debugging SQL stored procedures. If you are familiar with debugging applications using Eclipse platform, you will find that the steps of debugging DB2 SQL process are very similar. In this article, we will introduce how to debug the sample DB2 for z/OS SQL stored procedure using the debugging features in IBM Data Studio Developer V1.2. Many steps in this article can also be applied to DB2 for Linux? ，UNIX？ , and Windows? And SQL stored procedures. The sample stored procedures used in this article are specific to DB2 for z/OS v9.

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

Understand debugger technology in different DB2 versions.

DB2 for z/OS has two IBM debugger technologies:

The old technology named SQL Debugger supports DB2 for z/OS V8 (including V8 PTF UK03933 released at the same time as LUW V8 FP7). It only provides debugger support for external SQL stored procedures.

Unified debugger is the latest debugger technology, which supports DB2 for z/OS V8 (including PTF UK25860 released simultaneously with DB2 for Linux, UNIX and Windows V9FP2) and DB2 for z/OS V9. For DB2 for z/OS V8, you can use Unified Debugger 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 main difference between these two technologies is that the unified debugger supports debugging Java and SQL procedures and continues to support debugging nested procedure calls. This article discusses the unified debugger.

Prepare sample table

The stored procedure created in this paper is written in native SQL, taking the product ID as input, and calculating the profit of the product by using the cost, delivery fee and customer payment information stored in the product table. The output of the stored procedure is called profit, and the data is stored back to the product table.

Listing 1 shows the DDL required to create and populate the product table. If you want to check the SQL statements in the process, you need to run this DDL first.

Listing 1. DDL used to create sample product tables

Create the table ADMF00 1. product

(

ITEM VARCHAR(6) is not empty.

Cost decimal (8, 2),

Shipping decimal (8, 2),

Decimal payment (8, 2),

Decimal profit (8,2)

);

Insert ADMF00 1. Products (items, costs, transportation, payment)

Value ('00 1', 100.00, 19.99,150);

Insert ADMF00 1. Products (items, costs, transportation, payment)

Value ('002', 80, 8.99, 99.99);

Creating and deploying stored procedures

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 procedures and add logic to calculate the profit. Then, learn how to specify that stored procedures should be deployed with debugging options enabled. If you don't do this, you can't debug the stored procedure.

production process

Debugging an existing stored procedure

If there are any existing SQL stored procedures on the server, you can drag them from the database explorer to the data development project and debug them. However, you need to ensure that the stored procedure is ready for debugging, which means that debugging options should be enabled when deploying the stored procedure.

You cannot debug a stored procedure if the debugging option is not enabled when it is deployed. You must select Enable Debugging and deploy it again.

Follow these steps to create an example stored procedure named PROFITUPDATE:

First, create a new data development project using DB2 for z/OS database connection in Data Studio Developer. Choose file > new> data development project. The wizard asks you to specify a database connection. If there is no database connection, you can use the Database Connection Wizard to create a DB2 for z/OS database connection.

Figure 1. Select database connection

Next, use the New Stored Procedure Wizard to create an SQL stored procedure. You can right-click the project you just created and select New > Stored Procedure (see Figure 2) to start the New Stored Procedure wizard. Name the stored procedure PROFITUPDATE and specify native SQL as its language.

Figure 2. Create a new SQL stored procedure

On the SQL statement page, add the SQL statement shown in Listing 2 to the statement details box. Note that the variable name is preceded by a colon (:), which defines the variable name as a host variable.

Listing 2. SQL income statement update stored procedure

Select cost, freight and payment from the product.

Where ITEM =:itemID；;

Figure 3. SQL statement page

Click the Validate button to check the SQL syntax. If there is a syntax error, it will point out the error before writing the statement into the procedure. In addition, it helps to format SQL statements. Figure 3 shows the checked statement. Click Next.

On the Parameters page (Figure 4), you will notice that the input variable itemID is created based on the host variable itemID. On this page, you can modify existing parameters, add new parameters and delete parameters that are no longer needed.

Figure 4. Parameter page

Now, you must add a second parameter (output parameter) by clicking the Add… button. This parameter is called itemProfit and the mode is OUT. The type is Decimal (8 8,2), as shown in Figure 5. Click OK.

Figure 5. Add parameters

On the Deploy Options page of the wizard, you can Enable debugging by selecting the Enable Debugging check box, as shown in Figure 6. Click finish.

Figure 6. Ensure that the Enable Debugging check box is selected.

Now, you should open the created stored procedure in the editor as shown in Figure 6- 1.

Figure 6- 1. Generation process

When you look at the source code, you will notice that it contains no logic to calculate profits. You need to modify the procedure body of the stored procedure to add this logic. To save time, you can copy and paste the sample procedure body shown in Listing 3.

Listing 3. Stored procedure for calculating profit

Create process PROFITUPDATE (in itemID VARCHAR(6)),

OUT Profit Decimal (8,2))

Version version 1

Isolation level CS

Language SQL

Allow debugging mode

WLM environment of debugging mode WLMENV 1

Qualifier ADMF00 1

Submit Yes on Return

-

-SQL stored procedure

- itemID

-Project profit

-

P 1: here we go

-Declare variables

Decimal (8,2) defaults to 0.0;

Decimaltotal _ costdecimal (8,2) defaults to 0.0;

P2: Start

Decimal (8,2) defaults to 0.0;

The default value of declare items shipping fee decimal (8,2) is 0.0;

-Declare cursor

To declare cursor 1 cursor

Select cost, freight and payment from the product.

Where ITEM = itemID

Open the cursor1;

Take cursor 1 into itemCost, itemShippingFee, itemPayment.

SET total _ cost = item cost+itemshipping fee；

End p2;

SET item profit = item payment-total _ cost；

Update product set profit = project profit

Where ITEM = itemID

End P 1

Click Save to save the stored procedure. Now it's time to deploy stored procedures.

Deploy stored procedures

To deploy a stored procedure, right-click the procedure in Data Project Explorer and select Deploy. Because debugging was enabled when this procedure was previously created, it will enable debugging options at deployment time. If the debugging option is not enabled, you can still select the Enable debugging checkbox in the Routine Options section of the deployment wizard (Figure 7).

Figure 7. Enable debugging options in the deployment routine wizard

Set debugger session manager

It is helpful to understand the debugger framework before debugging stored procedures. The unified debugger has three components: server, client and session manager, as shown in Figure 8. In this framework, the client communicates with the session manager, which communicates with the server to exchange debugging information and requests.

Figure 8. Unified debugger framework

It can be seen that the session manager is very important. It is a middleware, which is responsible for coordinating the communication between client and server through TCP/IP connection. Before using the debugger, you need to start the session manager process somewhere on the network (client, server or elsewhere). If the settings are incorrect, you will encounter problems when using IBM Data Studio Developer, because the session manager is not available (Figure 9), so you can't use the unified debugger.

Figure 9. The session manager is not available.

If you run the session manager on a server in your environment, you don't need to do anything on the client. However, the server-side setup is very complicated (especially in z/OS environment), so this article only focuses on using the debugger on the client side, and the server-side setup will be discussed in another article. At the same time, it is suggested to learn how to set up DB2 for z/OS environment for stored procedure debugging through the red book listed in Resources.

What should I do if the session manager is not running locally?

If the session manager is running somewhere on the network, you can still use the steps described in this section, but you need to find db2dbgm.bat on the server where it is located.

The easiest way to use the session manager is to run it on the client and then configure the debugger preferences page. The steps to run the session manager on the client are as follows:

Locate the session manager contained in Data Studio Developer v 1.2. The file name is db2dbgm.bat, which is located in the following directory:

[DS installation directory] \dsdev\bin

Execute db2dbgm.bat in the command prompt window. It will provide a host IP address and port number, which you should remember. Its settings also include timeout information. The sample output of db2dbgm.bat shown in figure 10 shows that if the session manager is idle for 50 minutes, it will time out. (Specifying a value of zero indicates that no session manager timed out). You can modify the idle time and port number by editing db2dbgm.bat

Figure 10. Start the session manager and get the IP address and port number.

Now that you have the IP address and port number of the running session manager, you need to enter this information in the session manager section of the debugger preferences:

Select window & gt preferences ...

In Run/Debug >, locate the debugger preferences page under DB2 stored procedure debugger.

Click the use ready running session manager radio button on the debugger preferences page and enter the IP address and port number mentioned earlier.

Figure 1 1. Specify the IP address and port number of the session manager in preferences.

Note: Do not close the session manager window, as this will end the session manager session. Therefore, you should continue debugging and let the session manager window run in the background.

Set other debugger preferences

In addition to the session manager, you can configure other debugger preferences before you start debugging. This section briefly introduces two options, as shown in figure 12.

Figure 12. DB2 stored procedure debugger preferences

Inactive setting: when the debugger stops at the breakpoint, if there is no further debugging operation, it is considered that debugging is in an idle state. In order to avoid occupying server resources indefinitely, the debugger assumes that debugging should end after a specified period of inactivity and the process should run automatically. You can specify the length of time that the debugger is inactive on the Debugger Preferences page. The default value is 300 seconds.

Error tracing settings: To help identify the problem, an IBM service representative may ask you to enable diagnostic tracing on the server. A value of 2 means that a full diagnostic trace is enabled on the DB2 server. This setting should be kept at 0 (zero) unless the IBM service representative explicitly requests that server diagnostic tracing be enabled.

Optional: Set the running settings before debugging.

Finally, as the last optional step before starting debugging, specify the settings to be applied to the run, including:

Specifies that some SQL statements run before or after debugging (these statements are not debugged)

Set the input parameter values.

Specifies whether to commit changes to the database.

Set the collection ID for DB2 with z/OS connection.

The sample stored procedure updates the product table at the end. To check for updates, you can use the post-run statement to display the results of the SELECT statement on the PRODUCT table. This section introduces the specific practice.

Right-click the stored procedure in Data Project Explorer and select Run Settings to open the Run Settings dialog box, as shown in figure 13.

Figure 13. Run the setup menu.

The Running Settings window has multiple tabs. Select the "After Running" tab and enter the select * from PRODUCT statement, as shown in figure 14.

Figure 14. Run the setup dialog window.

This SQL statement is executed after running or debugging the stored procedure. The result of the profit update will be displayed in the output view.

Note: In the Operation Settings dialog box, you can also specify parameter values in the Parameter Values tab. However, if you don't specify it here, you still have the opportunity to specify the parameter value during debugging.

Back to the top

Start debugging

Now that all setup activities have been completed, you can start debugging the stored procedure.

The way to start debugging is to right-click the stored procedure name in the data project browser and select Debug…, as shown in figure 15.

Figure 15. Start the debugger from a procedure in Data Project Explorer.

The debugger first starts the standard Eclipse debugging configuration page, as shown in figure 16. All configuration items are preset. Click Debug to continue.

Figure 16. Debugging configuration page

Because the sample stored procedure has an input variable, the debugger will open the Specify Parameter Value dialog box, where you can enter the parameter value. If you previously entered a parameter value in the Operation Settings dialog box, the value will be displayed again and you can check or modify it.

Figure 17. Specify Parameter Values dialog box

Debugging is done in Debug perspective. The following window asks you to confirm switching to the Debug perspective. Click Yes to continue. You can also click the check box to make it remember your decision, so you won't be prompted to confirm.

Figure 18. Switch to the debug perspective.

Now, the debugger is working. Implicit breakpoints are always set on the line that contains the PROCEDURE keyword before the stored procedure logic. The current position is highlighted with an arrow. You can stop the process on any execution line for debugging. If any breakpoint is set, you can run until the next valid breakpoint.

Figure 19. The debugger stops at the first line.

Back to the top

Debugger perspective, view and operation

The Eclipse Debug perspective (Figure 20) provides several views to help debug stored procedures. This section describes these views.

Debugging view

Variable view

Breakpoint view

Data output view

Figure 20. Overview of debugging perspective

Debugging view

Debug view shows the current call stack. It provides the stored procedure name and the current line number. In debugging view, you can perform different operations through the toolbar shown in Figure 2 1, including single step entry, single step end, single step return, continue and terminate:

Figure 2 1. Debugging view and its operation

Use Step Over to debug the process line by line.

Debugging nested procedures with a single step. If there is no nested procedure, its function is the same as Step Over.

Single-step return helps to return from nested procedures. If you are not currently in a nested process, Step Return will run the whole process, ignoring all breakpoints.

When stopping on a line, you can use Resume to execute to the next valid breakpoint. If there is no valid breakpoint, run until the process ends and stop debugging.

You can stop debugging at any time by using Terminate.

Finally, you can delete all terminated runs by clicking this icon:

Tip about nested procedures: If there are nested procedure calls, the Debug view can provide debugging information of external procedures and nested procedures, including the current line number in each procedure.

Figure 22 shows an example of debugging nested stored procedures, in which procedure 1 calls procedure 2. On the line calling procedure2(var 1), if you choose to step into the operation, the debugger will go inside procedure2 from the first line and debug Procedure2. As you can see on the call stack, procedure 2 now appears on procedure 1. After debugging process 2, the debugger returns to process 1.

Figure 22. Debugging nested stored procedures

Variable view

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

Figure 23. Variable view

In the variable view, you can:

Specifies the breakpoint of variable modification.

Copy and paste all variable values into other files.

Modify variable values according to debugging needs. You can double-click the variable value and enter a new value, or right-click the variable and select Change Value… from the pop-up menu, as shown in Figure 24.

Figure 24. Viewing and Modifying Variables in Variable View

You cannot modify the values of diagnostic information SQLCODE or SQLSTATE, and you cannot set variable modification breakpoints for them. They are different from ordinary variables.

Breakpoint view

The breakpoints view shows all breakpoints set for the stored procedure. Breakpoint view provides standard breakpoint management support, including:

Enable/disable breakpoints

Delete breakpoint

Import/export breakpoints

The unified debugger supports two breakpoints: line breakpoint and variable modification breakpoint. In the following figure, the breakpoint view shows these two breakpoints.

Figure 25. Breakpoint view

Add a new breakpoint: Double-click the blank edge on the right of a line in the editor to set a line breakpoint. You can also right-click on the left side of the editor and set line breakpoints using the Add Breakpoint menu that appears.

Figure 26. Add breakpoint menu

If the breakpoint is set on a line that contains a valid SQL procedure execution statement, the debugger can stop on that line during debugging. Some source code lines and SQL procedure statements cannot set breakpoints, such as comment lines or cursor declarations. In addition, each SQL procedure statement has only one keyword that defines a breakpoint for the statement. Usually, a valid breakpoint is the first keyword of an SQL procedure statement. Be careful when setting breakpoints for statements that span multiple lines of source code. By stepping through the operations, you can become familiar with these valid breakpoint locations.

Figure 27. Line breakpoint

You can set the breakpoint of variable modification in variable view. In Variables view, right-click a variable and select Add Variable to Change Breakpoint. During debugging, when the variable value changes, the debugger will stop at the line where the variable is modified.

Figure 28. Create variables to modify breakpoints

Data output view

The data output view shows the process results when debugging is completed. The result is the same as when the stored procedure runs normally. It provides status information, running messages, parameter values and running results of debugging operations. The data output view helps to check the results of running stored procedures. If the result is different from expected, you can debug the stored procedure again.

For our example stored procedure, the profit of the second commodity (commodity 002) is 1 1.00. The profit value is displayed in the output mode parameter itemProfit in the parameters tab of the data output view.

Figure 29. Data output view-results of debugging operation

Remember how to set the post-run statement in the run settings? The execution result of the statement after running is also displayed in the data output view. As shown in Figure 30, the contents of the PRODUCT table are listed in the Results tab. The profit value of item 002 in the table is 1 1.00.

Figure 30. Data output view-the execution result of the statement after running.

Back to the top

Other issues that need to be understood.

So far, you have learned how to prepare a session manager for debugging, how to create a stored procedure, how to deploy it with debugging options enabled, and how to debug it by checking variable values and results. This section discusses some common situations when debugging on DB2 for z/OS.

The session manager could not be found.

As mentioned earlier, the session manager can be installed on the client, server or other places on the network. Sometimes, even if the session manager is set, IBM Data Studio Developer still reports that the session manager is unavailable.

If you encounter this problem and your session manager is running on the server or elsewhere on the network, the firewall may block the TCP/IP connection. In addition, sometimes it is because the IP address is incorrect. A good way to check whether the TCP/IP connection path is clear is to ping the IP address of the session manager from the server.

Timeout problem

The Unified Debugger uses two timeout settings. One for debugging the client; The other is the session manager. If the session manager does not service the debugging session within the specified time period, it will shut down. To use the Session Manager again, you must restart it. Debugging clients will release debugging sessions that have been inactive for a period of time. Be sure to adjust these two settings according to your own needs.

Debugger menu is not enabled.

Sometimes, the debugger menu is disabled. The following two situations can lead to this problem:

First, stored procedures cannot be deployed with debugging options enabled. You can open the procedure in the editor and check that the Enable Debugging check box is selected.

Second, the database connection may not support the debugger. As mentioned earlier, using PTF UK03933 on V8 provides debugger support for DB2 for z/OS. If you are connected to DB2 for z/OS V7, you cannot debug stored procedures in Data Studio Developer V 1.2.

Concluding remarks

IBM Data Studio Developer provides debugging features for SQL stored procedures, allowing debugging in the Eclipse framework in a way similar to debugging Java applications. This paper introduces how to debug SQL stored procedures on DB2 of z/OS, but many concepts are also applicable to other DB2 servers.

Express gratitude/gratitude

We sincerely thank Kathy Zeidenstein, Tina Chen and other reviewers for their help in this article!

reference data

study

Request notifications for subsequent articles in this series through RSS feeds. Learn more about the RSS feed of developerWorks content.

Learn about Data Studio products and technology-related resources through IBM Data Studio product topics.

Check the product page of IBM Data Studio to understand the benefits that this product can bring to the enterprise.

Getting Started with data studio developer: This tutorial provides an overview of Data Studio Developer.