What types of abnormal processors are involved in DB2 stored procedures?

The following article describes how to handle exceptions in the DB2 stored procedure. In the DB2 database, if you want to use sqlcode, you must declare before the DDL statement. This is what we all need to know. The following describes the main content of this article.

Handling of Stored Procedure exceptions:

 
 

  
  
DECLARE handler-type HANDLER FOR condition handler-action  
 
 

The exception Processor type (handler-type) has the following types:

After the processor operation is complete, CONTINUE continues to execute the next statement after the exception statement is generated.

After the processor operation is complete, EXIT terminates the stored procedure and returns the control to the caller.

Before UNDO executes a processor operation, DB2 rolls back the SQL operations performed during the stored procedure. After the processor operation is complete, the stored procedure is terminated and the control is returned to the caller.

Exception processors can handle custom exceptions based on specific SQLSTATE values or classes with predefined exceptions. The predefined three types of exceptions are as follows:

The not found flag causes an exception where the SQLCODE value is + 100 or the SQLSATE value is 02000. This exception usually occurs when SELECT does not return rows.

An error occurs when the SQLCODE value is negative due to the sqlexceptioin id.

The SQLWARNING flag causes a warning exception or an exception that causes a SQLCODE value other than 100.

If a not found or SQLWARNING exception is generated and no exception processor is defined for the exception, the exception is ignored and the control flow is switched to the next statement. If an SQLEXCEPTION exception is generated and no exception processor is defined for this exception, the DB2 stored procedure fails and the control flow is returned to the caller.

The following example declares two exception processors. The EXIT processor is called when an SQLEXCEPTION or SQLWARNING exception occurs. Before terminating the SQL program, the EXIT processor sets the variable named stmt to "ABORTED" and returns the control flow to the caller. The UNDO processor rolls back the SQL Operations completed in the Stored Procedure body before returning the control flow to the caller.

Listing 3: exception processor example

 
 

  
  
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING SET stmt = 'ABORTED';   
  
  
DECLARE UNDO HANDLER FOR NOT FOUND;  
 
 

If the predefined exception set does not meet your requirements, you can customize the exception Declaration for the specific SQLSTATE value and then declare the processor for this custom exception. Syntax:

List 4: custom exception Processors

 
 

  
  
DECLARE unique-name CONDITION FOR SQLSATE 'sqlstate'  
 
 

The processor can be defined by a separate Stored Procedure statement or by BEGIN... END Block composite statement definition. Note that the values of SQLSATE and SQLCODE are changed when the statement is executed. If you need to retain the values of SQLSATE and SQLCODE before the exception, you need to assign SQLSATE and SQLCODE to local variables or parameters in the first statement that executes the compound statement.

Generally, an output parameter (for example, poGenStatus) is defined for the DB2 stored procedure ).

 
 

  
  
declare sqlcode integer default 0;   
  
  
begin   
  
  
declare continue handler for sqlexception set ret = sqlcode;   
  
  
declare continue handler for sqlwarning set ret = sqlcode;   
  
  
declare continue handler for not found set ret = sqlcode;   
  
  
end ;  
 
 

Exception statement

Exception Handling

 
 

  
  
If sqlcode <0 or sqlcode = 100 then
  
  
Set O_RetCod = RetCode;
  
  
Set O_RetMsg = 'cln02: An error occurred while connecting the product instance to the customer! ';
  
  
Insert into LOG. OPER_LOG_TAB (PROC_NAME, OBJ_TAB, REGION_COD, OPER_COUNT, ERR_CODE, DATA_TIME, OPER_TIME)
  
  
Values ('P _ DW_CLEAN ', 'Global TEMP', 0, 0, retcode, CHAR (last_3_mon_time), current TIMESTAMP );
  
  
Return;
  
  
Else
  
  
Set RetCode = 0;
  
  
End if;
 
 

Good instance:

 
 

  
  
CREATE PROCEDURE divide ( IN numerator INTEGER,  
  
  
IN denominator INTEGER,  
  
  
OUT result INTEGER)  
  
  
LANGUAGE SQL  
  
  
BEGIN  
  
  
DECLARE overflow CONDITION FOR SQLSTATE '22003';  
  
  
DECLARE CONTINUE HANDLER FOR overflow  
  
  
RESIGNAL SQLSTATE '22375';  
  
  
IF denominator = 0 THEN  
  
  
SIGNAL overflow;  
  
  
ELSE  
  
  
SET result = numerator / denominator;  
  
  
END IF;   
 
 

The above content is an introduction to the troubleshooting methods for DB2 Stored Procedure exceptions. I hope you will find some gains.