Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2016
    Posts
    1

    Unanswered: Db2 continue handler not continuing with code - help!!!

    Hello all, as I come from Oracle , I'm now having difficulty with DB2 Exception Handling.

    I can't get the CONTINUE Handler below to CONTINUE with code, it is EXITING instead of CONTINUING when getting an SQL error.

    Can someone review the below and let me know what the heck I'm missing here?

    Thanks so very much for any assistance you can provide.

    Sincerely,

    Pat



    ************************************************** ************************************************** ************


    CREATE or replace
    PROCEDURE ceud.log_err(p_job_name char, p_proc_position char)
    LANGUAGE SQL
    AUTONOMOUS
    BEGIN
    DECLARE v_SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE v_SQLCODE INT;

    declare y varchar(1);

    ROLLBACK;
    INSERT INTO CEUD.BATCH_LOG_NEW (batch_job_name, batch_error_message, creation_date) VALUES (p_job_name,p_proc_position,sysdate);
    COMMIT;

    END

    CREATE or replace
    PROCEDURE ceud.MERGE_PCO()
    --SPECIFIC read_emp
    LANGUAGE SQL
    BEGIN

    DECLARE v_commit_count INTEGER DEFAULT 1;
    DECLARE v_record_count INTEGER DEFAULT 0;
    DECLARE v_pco_count INTEGER;
    DECLARE v_active_address VARCHAR(1);
    declare x varchar(1000);
    declare v_proc_position varchar(500);
    declare v_site_addr_valid_from DATE;
    declare v_site_addr_valid_to DATE;
    Declare v_zip varchar(50);
    Declare v_zip_ext varchar(50);
    declare v_ext_pos integer;
    declare v_zipext_len integer;
    declare v_job_name varchar(50) DEFAULT 'MERGE_PCO';

    DECLARE v_SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE v_SQLCODE INT;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN

    CALL ceud.LOG_ERR(v_job_name, v_proc_position);

    END;



    set v_proc_position ='start: '||to_char(SYSDATE,'MM/DD/YY HH:MIS');
    INSERT INTO CEUD.BATCH_MESSAGES (BAM_JOB_NAME, BAM_ERROR_MESSAGE, CREATION_DATE) VALUES (v_job_name, v_proc_position, SYSDATE);
    COMMIT;

    FOR r_delta AS cur_delta CURSOR WITH HOLD FOR SELECT * FROM CEUD.PCO_FILE_LOAD
    DO

    SET v_record_count = v_record_count + 1;
    SET v_proc_position = concat('PREMISE :',r_delta.premisenumber);
    SET v_pco_count = 0;

    SELECT COUNT(1)
    INTO v_pco_count
    FROM CEUD.PREMISE_CONNECTION
    WHERE PREMISE_NUMBER = r_delta.PREMISENUMBER
    AND INSTALLATION_NUMBER = r_delta.installation_number;

    IF v_pco_count = 0 THEN

    IF r_delta.dmlcmd IN ('I','U') THEN

    INSERT INTO CEUD.PREMISE_CONNECTION
    (
    PREMISE_NUMBER,
    INSTALLATION_NUMBER,
    CONNECTION_OBJECT,
    PREMISE_TYPE,
    PREMISE_TYPE_TEXT,
    LEGACY_PREMISE_ID,
    CIRCUIT_NAME,
    SERVICE_TOWNSHIP_ID,
    SERVICE_CITY_ID,
    OAS_PRIORITY_CODE,
    OAS_PRIORITY_CODE_TEXT,
    SECURITY_CODE,
    SECURITY_CODE_TEXT,
    TAX_JURISDICTION_CODE,
    LONGITUDE,
    LATITUDE,
    CREATION_DATE,
    CREATION_USER_ID
    )
    VALUES
    (
    r_delta.PREMISENUMBER,
    r_delta.INSTALLATION_NUMBER,
    r_delta.connectionobject,
    r_delta.premisetype,
    r_delta.premise_type_text,
    r_delta.legacy_premise_id,
    r_delta.Circuit_Name_co_characteristic,
    r_delta.svc_township_id,
    r_delta.svc_city_id,
    r_delta.oas_priority_code,
    r_delta.oas_priority_code_text,
    r_delta.security_code,
    r_delta.security_code_text,
    r_delta.tax_jurisdiction_code,
    r_delta.Longitutude,
    r_delta.Latitude,
    SYSDATE,
    v_job_name);

    END IF;

    ELSE

    IF r_delta.dmlcmd IN ('I','U') THEN

    UPDATE CEUD.PREMISE_CONNECTION PC
    SET PC.INSTALLATION_NUMBER = r_delta.INSTALLATION_NUMBER,
    PC.CONNECTION_OBJECT = r_delta.connectionobject,
    PC.PREMISE_TYPE = r_delta.premisetype,
    PC.PREMISE_TYPE_TEXT = r_delta.PREMISE_TYPE_TEXT,
    PC.LEGACY_PREMISE_ID = r_delta.LEGACY_PREMISE_ID,
    PC.CIRCUIT_NAME = r_delta.Circuit_Name_co_characteristic,
    PC.SERVICE_TOWNSHIP_ID = r_delta.svc_township_id,
    PC.SERVICE_CITY_ID = r_delta. svc_city_id,
    PC.OAS_PRIORITY_CODE = r_delta.oas_priority_code,
    PC.OAS_PRIORITY_CODE_TEXT = r_delta.OAS_PRIORITY_CODE_TEXT,
    PC.SECURITY_CODE = r_delta.SECURITY_CODE,
    PC.SECURITY_CODE_TEXT = r_delta.SECURITY_CODE_TEXT,
    PC.TAX_JURISDICTION_CODE = r_delta.TAX_JURISDICTION_CODE,
    PC.LONGITUDE = r_delta.Longitutude,
    PC.LATITUDE = r_delta.LATITUDE,
    PC.MODIFICATION_DATE = SYSDATE,
    PC.MODIFICATION_USER_ID = v_job_name
    WHERE PREMISE_NUMBER = r_delta.PREMISENUMBER
    AND INSTALLATION_NUMBER = r_delta.installation_number;

    END IF;

    END IF;


    set v_proc_position ='finish:'||to_char(SYSDATE,'MM/DD/YY HH:MIS');
    INSERT INTO CEUD.BATCH_MESSAGES (BAM_JOB_NAME, BAM_ERROR_MESSAGE, CREATION_DATE) VALUES (v_job_name, v_proc_position, SYSDATE);
    COMMIT;


    END

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    71
    Provided Answers: 9
    Hello,

    what's the error and which line of code generates the error?
    Regards,
    Mark.

  3. #3
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    Some DB2 SQL exceptions have implicit rollbacks , like -911 and related codes, so if you unconditionally continue you will get unexpected results. You might need to restart the whole transaction, or take other actions.

    Your code just assumes that all SQL-exceptions will run your autonomous sproc and things will just continue running and that seems unsafe.

    You might want to pass the retrieved sqlcode and sqlstate from an exception to the logging sproc for it to be meaningful.

    Compare what happens if the logging sproc is not autonomous and does not have the initial rollback, just to eliminate those as causes.

Posting Permissions

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