CREATE PROCEDURE proc_zujbd11(INP_DATE CHAR(8),INP_SHNAME VARCHAR(20) )
BEGIN
DECLARE SQLCODE INT DEFAULT 0;
DECLARE msg_text VARCHAR(200);
DECLARE sql_code INTEGER DEFAULT 0 ;
DECLARE sql_msg VARCHAR(200);
DECLARE sdate DATE ;
DECLARE cnt INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET sql_code = SQLCODE ;
GET DIAGNOSTICS EXCEPTION 1 msg_text = MESSAGE_TEXT ;
SET sql_msg =
SUBSTR( CHAR(sql_code) ||':' || char(msg_text),1,200 );
DELETE FROM ZUDTB03 WHERE SH_NAME = INP_SHNAME;
INSERT INTO ZUDTB03 VALUES (INP_SHNAME , 'ERCD'||':'||sql_msg);
COMMIT ;
END;
-- メッセージテーブルを削除 --
DELETE FROM ZUDTB03 WHERE SH_NAME = INP_SHNAME;

-- ①処理基準日（削除日）を取得する --
IF INP_DATE = '00000000' THEN
-- 処理基準日取得 --
SET sdate = (SELECT TODATE( CHAR( SYORIDATE ), 'YYYYMMDD' ) from ZUDTX04);
ELSE
-- パラメータの日付 --
SET sdate = (SELECT TODATE( INP_DATE, 'YYYYMMDD' ) from SYSIBM.SYSDUMMY1);
END IF;

-- ②アクセスログテーブルの日付が①の日付以前のレコードをカウントする --
SET CNT =( SELECT COUNT(*) from PODTX03
WHERE TRNDATE < INT( TOCHAR( sdate, 'YYYYMMDD' ) )) ;

-- メッセージテーブルに格納する --
INSERT INTO ZUDTB03 VALUES
( INP_SHNAME ,'ERCD'||':'||'0000'||':'||CHAR(cnt));

-- ②で選択したレコードを削除 --
DELETE FROM PODTX03
WHERE TRNDATE < INT( TOCHAR( sdate, 'YYYYMMDD' ));

COMMIT ;

END
-----------------------------------------
The table definition is :
create table PODTX03(trndate int)
create table zudtb03(sh_name char(20),message varchar(200))
create table ZUDTX04(SYORIDATE int,SYORITIME int)

I rename a table zudtx04:
db2 rename zudtx04 to ss

But I cannot get error message and error code from zudtb03.

can anyone help me to determine why the exception handling doesn't work?

tochar.sql:
CREATE FUNCTION TOCHAR (D DATE, FMT Varchar(50)) \
RETURNS Varchar(254) \
SPECIFIC TO_CHARTimestamp \
LANGUAGE SQL \
CONTAINS SQL \
NO EXTERNAL ACTION \
NOT DETERMINISTIC \
BEGIN ATOMIC \
DECLARE Retv VARCHAR(254) DEFAULT ' '; \
DECLARE Pos INTEGER DEFAULT 1; \
DECLARE Len INTEGER DEFAULT 2; \
\
SET Retv = FMT; \
\
SET Pos = posstr(upper(Retv),'DD'); \
IF Pos > 0 THEN \
SET Retv = CASE \
WHEN upper(substr(Retv,Pos+2,1)) = 'D' THEN \
insert(Retv,Pos,3,substr(digits(dayofyear(D)),8,3) ) \
ELSE \
insert(Retv,Pos,2,substr(digits(day(D)),9,2)) \
END; \
END IF; \
\
SET Pos = posstr(upper(Retv),'MM'); \
IF Pos > 0 THEN \
SET Retv = insert(Retv,Pos,2,substr(digits(month(D)),9,2)); \
END IF; \
\
SET Pos = posstr(upper(Retv),'YY'); \
IF Pos > 0 THEN \
SET Len = 2 + posstr(upper(substr(Retv,Pos+2,1)),'Y') + \
posstr(upper(substr(Retv,Pos+3,1)),'Y'); \
SET Retv = insert(Retv,Pos,Len,substr(digits(year(D)),11-Len,Len)); \
END IF; \
\
RETURN Retv; \
\
END

todate.sql:

CREATE FUNCTION TODATE(inpdat VARCHAR(8), fmt VARCHAR(10)) \
RETURNS DATE \
LANGUAGE SQL \
READS SQL DATA \
SPECIFIC TO_DATEfmt \
DETERMINISTIC \
--RETURNS NULL ON NULL INPUT \
BEGIN ATOMIC \
DECLARE outdat DATE; \
DECLARE str CHAR(10); \
DECLARE tmp VARCHAR(10); \
SET tmp = inpdat; \
IF UPPER(fmt) = 'YYYYMMDD' \
THEN \
SET str = SUBSTR(tmp,1,4) || '-' || \
SUBSTR(tmp,5,2) || '-' || SUBSTR(tmp,7,2); \
ELSE \
SIGNAL SQLSTATE '38Z01' \
SET MESSAGE_TEXT = 'INVALID FORMAT SPECIFIED.'; \
END IF; \
SET outdat = DATE(str); \
RETURN OUTDAT; \
END

That's an interesting approach ... ;-)

Have you tried using the Development centre Debugger, to see how far it goes ?

Sathyaram