Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unhappy Unanswered: Exception handling stored procedure for negative SQLCODE -803 SQLSTATE 23505

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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

  3. #3
    Join Date
    Dec 2005
    Posts
    3

    Unhappy Removed Lable L_INNER

    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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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

  5. #5
    Join Date
    Dec 2005
    Posts
    3

    Unhappy complete stored procedure. with colors.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •