Error Trapping in Stored procedure

[lespaul]

I've been looking through the Redbook on stored procedures and triggers trying to find information on error trapping. I've found the information on trapping for specific errors:

DECLARE record_not_found
    CONDITION for '02000';

but what if I'm not interested in the specifics? All I want to do is if there was a problem with the query write a message in the job log (QSYSOPR messages) that the SP failed to run properly so that it can be run again.

Here's the procedure:

CREATE PROCEDURE QGPL.SP_TAHEARINGS ( 
  IN I_HEARINGDATE DECIMAL(8, 0) ) 
  LANGUAGE SQL 
  SPECIFIC QGPL.SP_TAHEARINGS 
  NOT DETERMINISTIC 
  MODIFIES SQL DATA 
  CALLED ON NULL INPUT 
  BEGIN 
  
    DELETE FROM CMLIB . CMPTAHEARINGS ; 

    DELETE FROM CMLIB . CMPTASENTNC ; 

    INSERT INTO CMLIB . CMPTAHEARINGS ( CASPRE , CASNUM , HERTIM, DEFNAM )( SELECT DISTINCT H.CASPRE , H.CASNUM , H.HERTIM, DEFNAM FROM CMLIB.CMPHERMF H INNER JOIN CMLIB.CMPDEFMF D ON H.CASPRE = D.CASPRE AND H.CASNUM = D.CASNUM WHERE H.HERNGDAT = I_HEARINGDATE AND H.HERTYP = 'TA' ) ; 
END  ;

Thanks for any info!

Leslie

 

[sathyarams]

See if this page helps

http://www.db2click.com/my_script_dir/SP_ErrorHandler.html

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[lespaul]

Ok, I reviewed that page plus a couple of others I found and I've come up with this, but I never get an error message in the QSYSOPR message queue. (this is a different SP than the one above, the one below has a better chance of failure)

CREATE PROCEDURE QGPL.SP_DAILYCKINS2 (
  IN I_HEARINGDATE DECIMAL(8, 0) ) 
  LANGUAGE SQL 
  SPECIFIC QGPL.SP_DAILYCKINS2 
  NOT DETERMINISTIC 
  MODIFIES SQL DATA 
  CALLED ON NULL INPUT 
  BEGIN[b]
    DECLARE v_err_SQLSTATE char(5) DEFAULT '00000';
    DECLARE SQLSTATE char(5) DEFAULT '00000';
    DECLARE v_err_message varchar(255) DEFAULT '';[/b]

    DELETE FROM CMLIB . CMPCKINHEARINGS ; 

    INSERT INTO CMLIB . CMPCKINHEARINGS ( HERTIM , HERTYP , CASPRE , CASNUM , JUDNAM , OFFCR , OFFNUM , MAGENC , CRTROM , DEFNAM ) 
( SELECT P . HERTIM , P . HERTYP , P . CASPRE , P . CASNUM , P . JUDNM , P . OFFCR , P . OFFNUM , P . MAGENC , CASE WHEN P . CRTROM <> '' THEN P . CRTROM ELSE '0' END AS CRTROM , D . DEFNAM 
FROM PLOBJLIB . PLPOFFHR P 
INNER JOIN CMLIB . CMPDEFMF D ON P . CASPRE = D . CASPRE AND P . CASNUM = D . CASNUM 
WHERE P . HERNGDAT = I_HEARINGDATE AND ( P . STSCDE = '8' OR P . STSCDE = '9' ) 
UNION 
SELECT S . HERTIM , S . HERTYP , S . CASPRE , S . CASNUM , S . JUDNM , S . OFFCR , S . OFFNUM , S . MAGENC , CASE WHEN S . CRTROM <> '' THEN S . CRTROM ELSE '0' END AS CRTROM , D . DEFNAM 
FROM SLOBJLIB . SLPOFFHR S 
INNER JOIN CMLIB . CMPDEFMF D ON S . CASPRE = D . CASPRE AND S . CASNUM = D . CASNUM 
WHERE S . HERNGDAT = I_HEARINGDATE AND ( S . STSCDE = '8' OR S . STSCDE = '9' )
UNION 
SELECT DISTINCT H . HERTIM , HT . HERTYP , H . CASPRE , H . CASNUM , J . JUDNAM , TRIM ( N . MLNAME ) || ', ' || TRIM ( N . MFNAME ) , C . OFFNO1 , N . MAGENC , H . CRTROM , D . DEFNAM 
FROM CMLIB . CMPHERMF H 
INNER JOIN CMLIB . CMPDEFMF D ON D . CASPRE = H . CASPRE AND D . CASNUM = H . CASNUM AND D . DEFSEQ = H . DEFSEQ 
INNER JOIN CMLIB . CMPJUDNM J ON J . JUDCOD = H . JUDCOD
INNER JOIN CMLIB . CMPCHGMF C ON C . CASPRE = H . CASPRE AND C . CASNUM = H . CASNUM AND C . DEFSEQ = H . DEFSEQ AND C . CHGSEQ = 1 
INNER JOIN CMLIB . CMPOFFCR N ON N . OFFNUM = C . OFFNO1 AND N . MAGENC = C . ISSAGC 
INNER JOIN CMLIB . CMPHERTP HT ON H . HERTYP = HT . HERTYP AND HT . CALJUD = 'Y' AND HT . SCHLEO <> 'N' 
WHERE H . HERNGDAT = I_HEARINGDATE AND C . ISSAGC NOT IN ( 'APD' , 'BSO' ) ) ; 
[b]
  SET v_err_SQLSTATE=SQLSTATE ;

  if LEFT(v_err_SQLSTATE, 2) <> '00' then 
    set v_err_message = 'TA Hearings SP failed...process manually';
    SIGNAL SQLSTATE '01H01' SET MESSAGE_TEXT = v_err_message; 
  end if;[/b]

END  ;

What am I doing wrong? Thanks for any info!

Leslie