Way to catch all type of rised signals in DB2 SQL-PL via a declare handler

stored-procedures db2 sql-pl
AngocA picture AngocA · Feb 10, 2014 · Viewed 7.1k times · Source

I want to treat all types of generated signals in my code. I know how to catch a specific one with a Declare Handler, however I would like all of them.

For example, here I catch the 72822 signal.

DECLARE EXIT HANDLER FOR SQLSTATE '72822'
  BEGIN
    IF ( var = 'OK' )
      RESIGNAL SQLSTATE '72623' SET MESSAGE_TEXT = 'Got SQLSTATE 72822';
    ELSE
      RESIGNAL SQLSTATE '72319' SET MESSAGE_TEXT = var;
  END;

I will call a stored procedure that I do not know the code, and I have to be able to deal with all possible signals (for example insert the signal code in a table.)

I would like something like

DECLARE EXIT HANDLER FOR ALL

Answer

mustaccio picture mustaccio · Feb 10, 2014

That would be DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, I think.

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0004239.html

Related questions

How to call a stored procedure in IBM System i Access for Windows GUI Tool

I would like to test a DB2 stored procedure running on an AS400 system. I have the IBM System i Access for Windows installed and can run SQL commands against the DB2 database. My question is: What is the syntax …

Read More
How can I call a DB2 stored procedure with OUT parameters from SQuirreL SQL?

I really like SQuirreL SQL as a SQL query tool, but I've never been able to get it to call stored procedures in our AS/400 DB2 database. I always get the error "The number of parameter values set or registered …

Read More
DECLARE GLOBAL TEMPORARY TABLE Vs CREATE GLOBAL TEMPORARY TABLE in DB2

am creating a GLOBAL TEMPORARY TABLE in DB2. and when i surfed i got a two way to create 1. Declare 2. Create. 1. DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP (EMPNO CHAR(6) NOT NULL, SALARY DECIMAL(9, 2), BONUS DECIMAL(9, 2), COMM DECIMAL(9, 2)) WITH REPLACE …

Read More