Error Handling in Database Procedures

Document ID : KB000020039
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

This document describes some considerations for error handling in database procedures.

Solution:

Database procedures effectively run as part of the IDMS nucleus. This means that if there is an abend in a database procedure, it is more than likely that the CV will abend. Therefore, it is important that, as much as possible, abends are anticipated and avoided in database procedures.

This becomes a significant issue if database DML statements are issued in a database procedure.

Issuing database DML (or SQL) is allowed but strongly discouraged in database procedures.

In application coding, it is common practice to take action on "expected" error-status codes, and, if any other non-zero error-status is encountered, call some sort of generic error-handling routine.

For example:-


OBTAIN CALC EMPLOYEE.
IF  DB-REC-NOT-FOUND  THEN
   ... 
   process EMPLOYEEs not found
   ...
ELSE
   PERFORM IDMS-STATUS
   ...
   process EMPLOYEEs found
   ...
END-IF.

If ERROR-STATUS is neither 0000 or 0326 (e.g. 0966), ultimately IDMS-STATUS will issue a CALL 'ABORT' and in this situation the CV will probably abend.

A better practice in a database procedure, is to trap such unexpected error-status codes, and pass that information back to the invoking application program with a user-specified ERROR-STATUS that will indicate to the user that such an abend has occurred. If the database procedure is a BEFORE procedure, it may also be desirable to CANCEL the verb.


OBTAIN CALC EMPLOYEE.
IF  DB-REC-NOT-FOUND  THEN
   ... 
   process EMPLOYEEs not found
   ...
ELSE
   PERFORM DBPROC-IDMS-STATUS
   ...
   process EMPLOYEEs found
   ...
END-IF.
 
******************************************************************
DBPROC-IDMS-STATUS                                              SECTION.
******************************************************************
DBPROC-IDMS-STATUS-PARAGRAPH.                                           
        IF DB-STATUS-OK GO TO DBPROC-ISABEX. 
        MOVE 99 TO SC-ERR-MINOR.
        MOVE 01 TO PC-CANCEL-SWITCH.                
        GOBACK.
DBPROC-ISABEX. EXIT.

For more information, see Chapter 16 of the CA-IDMS Database Administration Guide - "Writing Database Procedures".