Can't get CONTINUE Handler to CONITINUE with code (next statement)

[pmcginni999]

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:MI:SS');
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:MI:SS');
INSERT INTO CEUD.BATCH_MESSAGES (BAM_JOB_NAME, BAM_ERROR_MESSAGE, CREATION_DATE) VALUES (v_job_name, v_proc_position, SYSDATE);
COMMIT;


END

 

[mikrom]

Have you looked at the SQLSTATE and are you sure that it's SQLEXCEPTION and not SQLWARNING or NOT FOUND ?
Maybe instead of

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
...

try to use

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
...