Debugging stored procedures

When debugging stored procedures, you might need to use different techniques than you would use for regular application programs. For example, some commonly used debugging tools, such as TSO TEST, are not available in the environment where stored procedures run.

Procedure

To debug a stored procedure, perform one or more of the following actions:

  • Take one or more of the following general actions, which are appropriate in many situations with stored procedures:
    • Ensure that all stored procedures are written to handle any SQL errors.
    • Debug stored procedures as stand-alone programs on a workstation.

      If you have debugging tools on a workstation, consider doing most of your development and testing on a workstation before installing a stored procedure on z/OS®. This technique results in very little debugging activity on z/OS.

    • Record stored procedure debugging messages to a disk file or JES spool file.
    • Store debugging information in a table. This technique is especially useful for remote stored procedures.
    • Use the DISPLAY command to view information about particular stored procedures, including statistics and thread information.
    • In the stored procedure that you are debugging, issue DISPLAY commands. You can view the DISPLAY results in the SDSF output. The DISPLAY results can help you find information about the started task that is associated with the address space for the WLM application environment.
    • If necessary, use the STOP PROCEDURE command to stop calls to one or more problematic stored procedures. You can restart them later.
  • If your stored procedures address space has the CEEDUMP data set allocated, look at the diagnostic information in the CEEDUMP output.
  • For COBOL, C, and C++ stored procedures, use the Debug Tool for z/OS.
  • For COBOL stored procedures, compile the stored procedure with the option TEST(SYM) if you want a formatted local variable dump to be included in the CEEDUMP output.
  • For native SQL procedures, external SQL procedures, and Java™ stored procedures, use the Unified Debugger.
  • For external stored procedures, consider taking one or both of the following actions:
    • Use a driver application.
    • Create or alter the stored procedure definition to include the PARAMETER STYLE SQL option. This option enables the stored procedure to share any error information with the calling application. Ensure that your procedure follows linkage conventions for stored procedures.
  • If you changed a stored procedure or a startup JCL procedure for a WLM application environment, determine whether you need to refresh the WLM environment. You must refresh the WLM environment before certain stored procedure changes take effect.