Hi

I am in the process of writing a stored procedure. I need a help in handling sqlexception. Here is the snippet of my stored procedure that i am trying to write.

SET v_failed_flag = 0;
L_INNER:BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET v_failed_flag = 1;
UPDATE control_table_status SET SQLSTATE=SQLSTATE, SQLCODE=CHAR(SQLCODE), duration = timestampdiff(2, char(current timestamp - start_timestamp)), end_timestamp = current timestamp, records_count=0
WHERE etl_id = p_current_etl_id
AND procedure_config_id = p_procedure_id
AND etl_run_count = p_current_etl_run_count
AND category_id = v_category_id
AND sequence = v_sequence_count
AND etl_run_date = p_current_etl_run_date
AND table_name = v_table_name
AND status = 'F';

COMMIT;
END
EXECUTE IMMEDIATE v_stmt;
SET v_failed_flag = 1;
END L_INNER;






One problem I have is I have declared a continue handler exception to continue if there is any sqlexception. But unfortunately when I executed a statement EXECUTE IMMEDIATE v_stmt; which holds a insert statement to insert a record into one of the tables we have. That insert statement is causing duplicate row insertion which resulted in SQLCODE -803 and SQLSTATE 23505. And control is transferring to CONTINUE HANDLER FOR SQLEXCEPTION. But as soon as control is reaching SET v_failed_flag = 1, control just exits out of the L_INNER labeled compound statement. I tried RESIGNAL STATEMENT IN CONTINUE HANDLER and I tried CONTINUE AFTER FAILURE int the stored procedure declaration before LANGUAGE SQL options, But it couldn't even compile when I used CONTINUE AFTER FAILURE. None of the options worked.

I just wanted control to go to SET v_failed_flag = 1; statement when there is exception that results in negative SQLCODE. Not sure what options to use.

Any help is greatly appreciated.

I am running stored procedure on DB2 UDB 8.2 on windows.

Please help.

Thanks
GK

I think it is the the compound statement designated L_INNER is causing the problem ..

Remove them and they should work .. In this case, you should have your "SET v_failed_flag = 1;" after the UPDATE Statement ...

HTH

Sathyaram

I have removed L_INNER label and tried, but this time it breaks out of outer compound statement which is not in the snippet i enclosed. It looks like L_INNER may not be the problem.

Why don't you post the full proc ... Also mention which statement's failure causes the procdure to exit instead of going through the continue handler

CREATE PROCEDURE etl_export(IN p_prev_etl_id INTEGER, IN p_prev_etl_run_count INTEGER,
IN p_prev_etl_run_date DATE, IN p_procedure_id SMALLINT,
IN p_current_etl_id INTEGER, IN p_current_etl_run_count INTEGER, IN p_current_etl_run_date DATE,
IN p_fresh_start SMALLINT)
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE category_stmt VARCHAR(500);
DECLARE v_stmt VARCHAR(2000);
DECLARE v_failed_max_sequence_count INT DEFAULT 0;
DECLARE v_default_copy_frequency INT DEFAULT 1;
DECLARE v_copy_frequency INT DEFAULT 1;
DECLARE v_copy_diff INT DEFAULT 0;
DECLARE v_failed_category_id INT DEFAULT 0;
DECLARE v_category_id INT DEFAULT 0;
DECLARE v_sequence_count INT DEFAULT 0;
DECLARE v_max_sequence INT DEFAULT 0;
DECLARE v_failed_sequence_not_found INT DEFAULT 0;
DECLARE v_table_name VARCHAR(50);
DECLARE v_rec_start_date DATE;
DECLARE v_rec_end_date DATE;
DECLARE v_current_date DATE;
DECLARE v_start_timestamp TIMESTAMP;
DECLARE v_last_succ_rec_end_date_not_found INT DEFAULT 0;
DECLARE v_failed_flag INTEGER DEFAULT 0;
DECLARE v_sqlstate CHAR(5) DEFAULT '00000';
DECLARE v_sqlcode INT DEFAULT 0;

FOR f_categories AS c_categories CURSOR WITH HOLD FOR SELECT category_id FROM control_category_config WHERE category_id >= v_failed_category_id
DO
SET v_category_id = category_id;

BEGIN
DECLARE c_tables CURSOR WITH HOLD FOR SELECT ctc.table_name FROM control_table_config ctc
WHERE ctc.category_id = v_category_id
AND ctc.sequence = v_sequence_count;
SELECT DISTINCT MAX(sequence) INTO v_max_sequence FROM control_table_config;
WHILE (v_sequence_count <= v_max_sequence)
DO
--Insert a new set of run_status for the sequence

OPEN c_tables;
FETCH FROM c_tables INTO v_table_name;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET v_failed_flag = 1;



WHILE ( SQLSTATE = '00000' )
DO

SELECT sql_statement INTO v_stmt FROM control_table_config WHERE table_name = v_table_name AND category_id = v_category_id;
SELECT copy_frequency INTO v_copy_frequency FROM control_table_config WHERE table_name = v_table_name;
SELECT CURRENT TIMESTAMP - v_copy_frequency DAYS INTO v_start_timestamp FROM sysibm.sysdummy1;
INSERT INTO control_table_status (etl_id, procedure_config_id, etl_run_count, category_id,
sequence, table_name, etl_run_date, start_timestamp, status, sqlstate, sqlcode) VALUES ( p_current_etl_id, p_procedure_id, p_current_etl_run_count, v_category_id,
v_sequence_count, v_table_name, p_current_etl_run_date, CURRENT TIMESTAMP, 'F', '00000', '000');
SET v_failed_flag = 0;
BEGIN
DECLARE duplicate_handler CONDITION FOR SQLSTATE '23505';

DECLARE CONTINUE HANDLER FOR duplicate_handler
BEGIN
-- SET SQLSTATE = '00000';
-- SET SQLCODE = 0;
--VALUES (SQLSTATE, SQLCODE) INTO v_sqlstate , v_sqlcode;
--SET v_failed_flag = 1;
UPDATE control_table_status SET SQLSTATE=SQLSTATE, SQLCODE=CHAR(SQLCODE), duration = timestampdiff(2, char(current timestamp - start_timestamp)), end_timestamp = current timestamp, records_count=0
WHERE etl_id = p_current_etl_id
AND procedure_config_id = p_procedure_id
AND etl_run_count = p_current_etl_run_count
AND category_id = v_category_id
AND sequence = v_sequence_count
AND etl_run_date = p_current_etl_run_date
AND table_name = v_table_name
AND status = 'F';
SET v_failed_flag = 1;
COMMIT;
END;
--DECLARE NORMAL CONDITION FOR SQLSTATE '03000';
--DECLARE UNDO HANDLER FOR SQLSTATE '23505'
-- SET v_failed_flag = 1;

CALL EXECUTE_STMT(v_sqlstate, v_stmt); --EXECUTE IMMEDIATE v_stmt; -- USING v_current_date, v_rec_end_date;
SET v_failed_flag = 1;
END;

IF (v_failed_flag != 1)
THEN
UPDATE control_table_status SET status = 'P', duration = timestampdiff(2, char(current timestamp - start_timestamp)), end_timestamp = current timestamp, records_count=0
WHERE etl_id = p_current_etl_id
AND procedure_config_id = p_procedure_id
AND etl_run_count = p_current_etl_run_count
AND category_id = v_category_id
AND sequence = v_sequence_count
AND etl_run_date = p_current_etl_run_date
AND table_name = v_table_name
AND status = 'F';
END IF;
COMMIT;
FETCH FROM c_tables INTO v_table_name;
END WHILE; -- end while sqlstate
END;
SET v_sequence_count = v_sequence_count + 1;
CLOSE c_tables;
END WHILE; -- end while sequence count
SET v_sequence_count = 1;
END;
END FOR;
END


Please find EXECUTE_STMT procedure code below.

CREATE PROCEDURE REALM1.EXECUTE_STMT ( OUT SQLSTATE_OUT CHARACTER(5),
IN p_stmt VARCHAR(4000) )
DYNAMIC RESULT SETS 1
P1: BEGIN
-- Declare variable
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';


-- Declare handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET SQLSTATE_OUT = SQLSTATE;
END;

SET SQLSTATE_OUT = SQLSTATE;
EXECUTE IMMEDIATE p_stmt;
SET SQLSTATE_OUT = '00000';
END P1



When it executes CALL EXECUTE_STMT(.... that procedure returned -803 sqlcode and control goes to duplicate_handler and when i execute Update statement in that handler, it automatically jumps to its outer compount statement 's continue handler for sqlexception i.e,. SET v_failed_flag statement and then it goes out of stored procedure with return code as -803 with sqlstate as 23505. It is suppose to continue after CALL EXECUTE_STMT as i have declare continue handler for sql exception but itz not going to statement after CALL EXECUTE_STMT line. I don't know how to reset the value of sqlcode in handler so that atleast i can continue execution even if negative sqlcode comes.

I hope I am able to explain. If not please drop a reply so that I will try to explain better.

Thanks very much for spending time on this issue.

Thanks again.
Gopal.