< Previous | Next >

Debugging the stored procedure

The workbench includes an integrated stored procedure debugger. By stepping through your code while you are running in debug mode and viewing the results, you can discover problems with your stored procedure and make the necessary changes.

The stored procedure debugger supports debugging SQL or Java stored procedures, with some limitations depending on your operating system and DB2 version. See the stored procedure debugger help topics in the information center for more details.

To debug the stored procedure:

  1. Set up the debugger. Before you debug, you must set up the server and the session manager. The session manager connects the debugger user interface to the DB2 server and must be running before the debug session can start.
    1. Set up the server for debugging.

    2. Locate one of the following local session manager start files in the workbench's root installation directory:
      • For Windows For Windows: db2dbgm.bat
      • For Linux For Linux: db2dbgm.000
    3. Modify the appropriate file as needed to reflect your install path, CLASSPATH, and port settings.
    4. Run the file to start the local (client) session manager.
    5. Note the IP address and port of the session manager daemon in the command window that opens when you run the file, or in a command window that is already open. You must enter this information into the DB2 Stored Procedure Debugger Preferences window.
      Screen shot showing command window with session manager status, as described.
    6. Click Window > Preferences, and expand the Run/Debug node.
    7. Click DB2 Stored Procedure Debugger.
    8. In the right panel, select Use already running session manager, and then enter the IP address and port number for the session manager.
      Screen shot showing the Preferences window, as described.
    9. Optional: Modify session timeout and other settings.
    10. Click OK to save your settings and close the Preferences window.
  2. In the routine editor, double-click in the left vertical margin to set line breakpoints.
  3. In the Data Project Explorer view, right-click the stored procedure, and select Debug. The Debug Configurations window opens.
  4. In the list, select the schemaname.SPEmployee stored procedure.
  5. If the Procedure field is blank, click Browse. The Stored Procedures window opens. Select schemaname.SPEmployee and click OK.
  6. To the right of the Argument list field, click Edit. The Run Settings window opens.
  7. On the Parameter Values page, in the Value field for DEPT type D11. D11 is the ID for a department (EMPLOYEE.WORKDEPT) in the EMPLOYEE table. When you enter this value, the stored procedure will return rows that match this department ID.
  8. Click OK in the Run Settings window, and click Debug in the Debug window. A prompt asks if you want to switch to the Debug perspective. Click Yes.
  9. From the Run menu, select Resume, Step into, or Step over to run in debug mode.
  10. Continue running in debug mode until the stored procedure returns the required results.
< Previous | Next >
Feedback