Debugging stored procedures

Db2® for z/OS® Development tooling provides support for debugging native and external SQL stored procedures. Learn how to configure and run the debugger for an SQL stored procedure .spsql source file.

Before you start

  1. Verify that the port specified for the debugger session manager is correct. For more information about this preference setting, see Configuring Db2 for z/OS tooling.
  2. Connect to a Db2 for z/OS connection. For more information about creating and connecting to a Db2 for z/OS connection, see Connecting to Db2.
  3. Associate the connection with the SQL stored procedure source file. For more information about associating connections with files, see Associating a source file or script with a Db2 connection.

Starting a debugging session

  1. To start the debugger, do one of these steps:
    • Open an .spsql file in the SQL editor, then right-click in the editor and select Debug As > Db2 for z/OS Routine.
    • In the z/OS Projects view, right-click an .spsql file and select Debug As > Db2 for z/OS Routine
    • In the z/OS Projects view, select an .spsql file, and then do one of these actions: press F11, select Run > Debug from the main menu, or select Debug from the toolbar.
    All of these commands generate a default debug configuration. If you prefer to create a debug configuration for Db2 for z/OS routines, select Debug As > Debug Configuration.
  2. Enter any required input parameters, if prompted, and then click OK. If the SQL statement has been run against the active database recently, the input parameters can be populated with recent values for the Data Type, Null, and Value fields.
    Results:
    • The debugger session manager generates a debug configuration and opens the source code for the active stack frame in a separate read-only editor, which is different from the one you might be editing in. The reason for the separate editor is that the local source might contain changes not present in the deployed source. The read-only editors close automatically when the debugging session has ended.
    • The debugger automatically stops at the first line of the routine.
    • If the Debug Perspective is not already open, the workbench prompts you to open it. If you choose not to open the Debug Perspective, then the Debug view is added to the current perspective.
    • While a debug session is active, the source file in the SQL editor is marked read-only so that you cannot make changes to it.
    • The debug session does not run if there are errors in the source file or if there is already a stored procedure debug session open. You can run only one debug session at a time. For more information about debug errors, see Troubleshooting debugging sessions.

Adding breakpoints and stepping through code

Use the debug editor and the Debug view to set breakpoints that determine where the debugger pauses and to step through the stored procedure code. Breakpoints can only be added to the editors displaying remote source files. These are initially available while in a debug session. You can view and modify existing breakpoints in the Breakpoints view. Breakpoints in remote files persist across multiple debug sessions. You can open the last retrieved remote source content by viewing the source of a breakpoint, and add or remove breakpoints to that remote source copy outside of a debug session.

The Debug view displays a hierarchical view of the stored procedure as it executes. This hierarchical view includes the debug target, process, thread, and one or more stack frames displaying the schema, name, and version of the routines containing the execution source for that frame.

You can use the Breakpoints view to view or manage breakpoints. Clicking a breakpoint in this view re-opens the read-only debugging source file. For more information about the Breakpoints view, see Breakpoints View.

Debug editor and Debug view

To add a breakpoint, double-click the marker bar at the line where you want to set a breakpoint. To remove a breakpoint, double-click the marker. You can also use the pop-up menu on the marker bar to enable and disable breakpoints.

When execution is suspended, you can use these commands in the Debug view:
Resume Resume
Resumes execution until either the next breakpoint or the end of the stored procedure is reached.
Stop Stop
Ends the debug session.
Step Into Step Into
Attempts to step into the subroutine at the current line as a new stack frame.
Step Over Step Over
Attempts to step to the next instruction of the current stack frame.
Step Return Step Return
Attempts to return execution to the parent stack frame.

As you step through the code, the debugger annotates the current instruction of the active top stack frame and the current instruction in stacks other than the active top stack frame. If you want to customize the appearance of these annotations, open the Preferences page and navigate to General > Editors > Text Editors > Annotations. Scroll down to find Debug Call Stack and Debug Current Instruction Pointer.

SQL query results

When the debugging session ends, the editors displaying the remote source files close, and the results of the SQL call operation are shown in the Execution Status window, where you can see any warnings or errors and link to more information about SQL codes and status. The CALL request is also added to the Query History folder of the Db2 for z/OS connection.

Demonstration

The following animation shows the end-to-end debugging process.
Debugging a Db2 for z/OS Routine

Editing variables during a debugging session

When you start a debugging session, you are given the option to open the Debug perspective. This perspective includes a Variables view that you can use to edit variables as you step through code. The Variables view lists the variables in scope at the selected stack frame and their values. You can also display the declared SQL type of variables by selecting Layout > Select Columns from the view's overflow menu, and then selecting Declared Type from the list.
Displaying additional columns in the Variables view
There are two ways to edit a variable value:
  • Click it in the Variables view and type a new value. To set the variable to null, type *NULL*.
  • Right-click a variable and select Change Value. The Set Variable Value window opens. Type a new value in the text pane or click Set value to *NULL*.
Only variables at the top frame of the stack can be edited. You cannot edit the diagnostic variables.

Whenever you use the Resume, Step Over, Step Into, or Step Return actions to reach a breakpoint, any variables that were modified between the resumption and the suspension are highlighted in the variables view. Newly created variables are not highlighted. The SQL State and SQL Code diagnostic variables are also highlighted when their values change.

For an overview of the other views in the Debug perspective, see Debugging your application.

Troubleshooting debugging sessions

The following situations can prevent the debugging session from starting:
  • The source file is empty or does not contain a CREATE PROCEDURE statement.
  • The source that is open in the SQL editor does not resolve to a file in the workspace.
  • The content contains syntax errors.
  • The source file does not contain SQL. The debugger supports only native SQL stored procedures.
  • A debug session is already open.
The following situations can result in warnings, but do not prevent the debugger from starting:
  • The source file contains multiple stored procedures. In this case, the debug operation is run on only the first stored procedure.
  • The source file contains other types of SQL statements besides the CREATE PROCEDURE statement. In this case, the debug operation is run on only the first stored procedure.