Obtaining diagnostic information and debugging stored procedures

You have several options for obtaining diagnostic information and debugging stored procedures, depending on the type of stored procedure.

Procedure

To obtain diagnostic information and debug stored procedures:

Take the appropriate action, depending on the type of stored procedure that you use.
Type of stored procedure Actions
All types of stored procedures
  • Look at the diagnostic information in CEEDUMP. If the startup procedures for your stored procedures address spaces contain a DD statement for CEEDUMP, Language Environment® writes a small diagnostic dump to CEEDUMP when a stored procedure terminates abnormally. The output is printed after the stored procedures address space terminates. You can obtain the dump information by stopping the stored procedures address space in which the stored procedure is running.
  • Debug the stored procedure as a stand-alone program on a workstation.
  • Record stored procedure debugging messages to a disk file or JES spool file by using the Language Environment MSGFILE run time option.
  • Store debugging information in a table. This option works well for remote stored procedures.
C stored procedures Use IBM Debug for z/OS.
C++ stored procedures Use IBM Debug for z/OS.
COBOL stored procedures Use IBM Debug for z/OS.
External applications Use a driver application.
External SQL procedures Use the Unified Debugger.
Deprecated function: External SQL procedures are deprecated and not as fully supported as native SQL procedures. For best results, create native SQL procedures instead. For more information, see Creating native SQL procedures and Migrating an external SQL procedure to a native SQL procedure.
Java™ stored procedures Use the Unified Debugger.
Native SQL procedures Use the GET DIAGNOSTICS statement. The DB2_LINE_NUMBER parameter returns:
  • The line number where an error is encountered in parsing, binding, or executing a CREATE or ALTER statement for a native SQL procedure.
  • The line number when a CALL statement invokes a native SQL procedure and the procedure returns with an error.
This information is not returned for an external SQL procedure, and this value is meaningful only if the statement source contains new line control characters.

What to do next

Remember: After you finish debugging stored procedures, remember to disable the debugging option that you used, so that you do not run debugging tools in a production system.