How to output error record as part of DB2 Stored Procedure standard output
0
Sometimes when we execute the error record we get bad data and the below stored procedure errors out with error code as
"sql> call REFLOADER.SP_ALL_ES_CODE()
[2018-11-20 10:14:01] [22004][-87] A null value was specified in a context where a null value is not allowed.. SQLCODE=-87, SQLSTATE=22004, DRIVER=4.23.42".
As such I would like to **split out the error code along with the error values/record set that is failing in the below script. can you please help with the code where and how can i get the error record output.**
enter code here
CREATE OR REPLACE PROCEDURE SCHEMA1.SP_ALL_CODE ()
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_stmt VARCHAR(5000);
DECLARE l_dynamic_sql VARCHAR(5000);
DECLARE l_uuid CHAR(36);
DECLARE l_ID VARCHAR(255);
DECLARE l_table VARCHAR(100);
DECLARE l_exists integer;
DECLARE l_changed integer;
DECLARE l_CID VARCHAR(255);
DECLARE l_Tt VARCHAR(255);
DECLARE l_TL VARCHAR(255);
DECLARE l_DateStart DATE;
DECLARE l_DateEnd DATE;
DECLARE l_DateEnd_Format DATE;
P2: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT c.ID , REPLACE(CID,'''',''''''), REPLACE(tt,'''',''''''), TL ,DateStart ,DateEnd , tablename
FROM SCHEMA1.ALL_CODE c, SCHEMA1.ID_TABLENAME t
WHERE c.id = t.id
and t.tabletype = 1
-- Access Location/Site (ignore end dated)
--and (c.oid != '2.16.840.1.113883.3.2390.2.2.26' OR DateEnd is null)
and (t.tablename != 'FREQUENCY' OR DateEnd is null)
and (t.tablename != 'ORDER_FREQUENCY' OR DateEnd is null)
Order By c.ID , CID;
-- Cursor left open for client application
OPEN cursor1;
FETCH FROM cursor1 INTO
l_ID , l_CID , l_Tt , l_TL , l_DateStart , l_DateEnd, l_table ;
WHILE(SQLSTATE = '00000')
DO
--Insert or Update
IF (l_DateStart IS NULL) THEN
SET l_DateStart = '1900-01-01';
END IF;
--exception
-- L_TEST (smart) 216840
If l_id = '216840' Then
SET l_CID = LPAD(l_CID,4,'0' );
End if;
SET l_exists = 0;
SET l_dynamic_sql = 'select count(*) from m_ref.' || l_table || ' where c_id =' || '''' || l_id || ':' || l_cId || '''' ;
--INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_dynamic_sql);
PREPARE p_exist_cursor from l_dynamic_sql;
P3: BEGIN
DECLARE exist_cursor CURSOR FOR p_exist_cursor;
OPEN exist_cursor;
FETCH exist_cursor INTO l_exists;
CLOSE exist_cursor;
END P3;
IF (l_exists = 0)
THEN
SET l_uuid = RANDOMUUID();
SET l_stmt = 'Insert into SCHEMA1.' || l_table ||
' (ID, CID, DISPLAY_NAME, END_DATE, START_DATE) values (' ||
'''' || l_uuid || '''' || ',' ||
'''' || l_oid || ':' || l_cId || '''' || ',' ||
'''' || l_Tt || '''' || ',';
IF (l_DateEnd IS NULL) THEN
SET l_stmt = l_stmt || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' || ',' ;
END IF;
SET l_stmt = l_stmt || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' || ');' ;
if (l_stmt is null) then
set l_stmt = 'YYY';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
-- Exceptions
-- CC
If l_table = 'CARECAT' Then
SET l_stmt = 'Update SCHEMA1.CARE_CATEGORY SET CONTEXT_NAME = ' || '''' || 'NOTE' || '''';
SET l_stmt = l_stmt || ' WHERE cid = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'BBB';
end if;
INSERT INTO REFLOADER.dml_ins_upd VALUES (l_stmt);
End if;
-- LTEST (Test) 2168401
If l_id = '2168401' Then
SET l_stmt = 'Update SCHEMA1.LTEST SET IS_FLAG = 0';
SET l_stmt = l_stmt || ' WHERE code_id = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'XXX';
end if;
INSERT INTO REFLOADER.dml_ins_upd VALUES (l_stmt);
End if;
-- LAB_TEST (Panel) 2168401
If l_id = '2168401' Then
SET l_stmt = 'Update SCHEMA1.LTEST SET IS_FLAG = 1';
SET l_stmt = l_stmt || ' WHERE code_id = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'TTT';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
End if;
------------------------------
END IF;
IF (l_exists != 0)
THEN
SET l_changed = 0;
SET l_dynamic_sql = 'select count(*) ' ||
' from SCHEMA1.' || l_table ||
' where cid = ' || '''' || l_id || ':' || l_cId || '''' ;
if (l_termshort is null) then
SET l_dynamic_sql = l_dynamic_sql ||
' and (display_name != null ';
else
SET l_dynamic_sql = l_dynamic_sql ||
' and (display_name != ' || '''' || l_tt || '''' ;
end if;
--Exception FREQUENCY
If (l_table = 'FREQUENCY') Then
if (l_tl is null) then
SET l_dynamic_sql = l_dynamic_sql ||
' OR numeric_value is not null ';
else
SET l_dynamic_sql = l_dynamic_sql ||
' OR numeric_value is null ' ||
' OR numeric_value != ' || l_tl ;
end if;
End IF;
---
SET l_dynamic_sql = l_dynamic_sql ||
' OR date(start_date) != ' || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' ;
if (l_DateEnd is null) then
SET l_dynamic_sql = l_dynamic_sql || ' OR end_date is not null';
else
SET l_dynamic_sql = l_dynamic_sql || ' OR end_date is null OR date(end_date) != ' || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' ;
end if;
SET l_dynamic_sql = l_dynamic_sql || ')';
--INSERT INTO REFLOADER.dml_ins_upd VALUES (l_dynamic_sql);
PREPARE p_changed_cursor from l_dynamic_sql;
P4: BEGIN
DECLARE change_cursor CURSOR FOR p_changed_cursor;
OPEN change_cursor;
FETCH change_cursor INTO l_changed;
CLOSE change_cursor;
END P4;
IF (l_changed = 1)
THEN
SET l_stmt = 'Update SCHEMA1.' || l_table || ' SET display_name = ' || '''' || l_TS || '''' || ',';
--Exception FREQUENCY
If (l_table = 'FREQUENCY') Then
IF (l_TL IS NULL) THEN
SET l_stmt = l_stmt || ' numeric_value = ' || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || ' numeric_value = ' || l_tl || ',' ;
END IF;
End IF;
---
IF (l_DateEnd IS NULL) THEN
SET l_stmt = l_stmt || ' end_date = ' || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || ' end_date = ' || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' || ',' ;
END IF;
SET l_stmt = l_stmt || ' start_date = ' || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' ;
SET l_stmt = l_stmt || ' WHERE cid = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'XXX';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
END IF;
END IF;
-- Fetch Next
FETCH FROM cursor1 INTO
l_ID , l_CID , l_Tt , l_TL , l_DateStart , l_DateEnd, l_table;
END WHILE;
CLOSE cursor1;
END P2;
commit work;
END P1;
stored-procedures error-handling db2-luw
asked Nov 20 '18 at 18:05
sunny babausunny babau
167
add a comment |
0
Sometimes when we execute the error record we get bad data and the below stored procedure errors out with error code as
"sql> call REFLOADER.SP_ALL_ES_CODE()
[2018-11-20 10:14:01] [22004][-87] A null value was specified in a context where a null value is not allowed.. SQLCODE=-87, SQLSTATE=22004, DRIVER=4.23.42".
As such I would like to **split out the error code along with the error values/record set that is failing in the below script. can you please help with the code where and how can i get the error record output.**
enter code here
CREATE OR REPLACE PROCEDURE SCHEMA1.SP_ALL_CODE ()
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_stmt VARCHAR(5000);
DECLARE l_dynamic_sql VARCHAR(5000);
DECLARE l_uuid CHAR(36);
DECLARE l_ID VARCHAR(255);
DECLARE l_table VARCHAR(100);
DECLARE l_exists integer;
DECLARE l_changed integer;
DECLARE l_CID VARCHAR(255);
DECLARE l_Tt VARCHAR(255);
DECLARE l_TL VARCHAR(255);
DECLARE l_DateStart DATE;
DECLARE l_DateEnd DATE;
DECLARE l_DateEnd_Format DATE;
P2: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT c.ID , REPLACE(CID,'''',''''''), REPLACE(tt,'''',''''''), TL ,DateStart ,DateEnd , tablename
FROM SCHEMA1.ALL_CODE c, SCHEMA1.ID_TABLENAME t
WHERE c.id = t.id
and t.tabletype = 1
-- Access Location/Site (ignore end dated)
--and (c.oid != '2.16.840.1.113883.3.2390.2.2.26' OR DateEnd is null)
and (t.tablename != 'FREQUENCY' OR DateEnd is null)
and (t.tablename != 'ORDER_FREQUENCY' OR DateEnd is null)
Order By c.ID , CID;
-- Cursor left open for client application
OPEN cursor1;
FETCH FROM cursor1 INTO
l_ID , l_CID , l_Tt , l_TL , l_DateStart , l_DateEnd, l_table ;
WHILE(SQLSTATE = '00000')
DO
--Insert or Update
IF (l_DateStart IS NULL) THEN
SET l_DateStart = '1900-01-01';
END IF;
--exception
-- L_TEST (smart) 216840
If l_id = '216840' Then
SET l_CID = LPAD(l_CID,4,'0' );
End if;
SET l_exists = 0;
SET l_dynamic_sql = 'select count(*) from m_ref.' || l_table || ' where c_id =' || '''' || l_id || ':' || l_cId || '''' ;
--INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_dynamic_sql);
PREPARE p_exist_cursor from l_dynamic_sql;
P3: BEGIN
DECLARE exist_cursor CURSOR FOR p_exist_cursor;
OPEN exist_cursor;
FETCH exist_cursor INTO l_exists;
CLOSE exist_cursor;
END P3;
IF (l_exists = 0)
THEN
SET l_uuid = RANDOMUUID();
SET l_stmt = 'Insert into SCHEMA1.' || l_table ||
' (ID, CID, DISPLAY_NAME, END_DATE, START_DATE) values (' ||
'''' || l_uuid || '''' || ',' ||
'''' || l_oid || ':' || l_cId || '''' || ',' ||
'''' || l_Tt || '''' || ',';
IF (l_DateEnd IS NULL) THEN
SET l_stmt = l_stmt || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' || ',' ;
END IF;
SET l_stmt = l_stmt || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' || ');' ;
if (l_stmt is null) then
set l_stmt = 'YYY';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
-- Exceptions
-- CC
If l_table = 'CARECAT' Then
SET l_stmt = 'Update SCHEMA1.CARE_CATEGORY SET CONTEXT_NAME = ' || '''' || 'NOTE' || '''';
SET l_stmt = l_stmt || ' WHERE cid = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'BBB';
end if;
INSERT INTO REFLOADER.dml_ins_upd VALUES (l_stmt);
End if;
-- LTEST (Test) 2168401
If l_id = '2168401' Then
SET l_stmt = 'Update SCHEMA1.LTEST SET IS_FLAG = 0';
SET l_stmt = l_stmt || ' WHERE code_id = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'XXX';
end if;
INSERT INTO REFLOADER.dml_ins_upd VALUES (l_stmt);
End if;
-- LAB_TEST (Panel) 2168401
If l_id = '2168401' Then
SET l_stmt = 'Update SCHEMA1.LTEST SET IS_FLAG = 1';
SET l_stmt = l_stmt || ' WHERE code_id = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'TTT';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
End if;
------------------------------
END IF;
IF (l_exists != 0)
THEN
SET l_changed = 0;
SET l_dynamic_sql = 'select count(*) ' ||
' from SCHEMA1.' || l_table ||
' where cid = ' || '''' || l_id || ':' || l_cId || '''' ;
if (l_termshort is null) then
SET l_dynamic_sql = l_dynamic_sql ||
' and (display_name != null ';
else
SET l_dynamic_sql = l_dynamic_sql ||
' and (display_name != ' || '''' || l_tt || '''' ;
end if;
--Exception FREQUENCY
If (l_table = 'FREQUENCY') Then
if (l_tl is null) then
SET l_dynamic_sql = l_dynamic_sql ||
' OR numeric_value is not null ';
else
SET l_dynamic_sql = l_dynamic_sql ||
' OR numeric_value is null ' ||
' OR numeric_value != ' || l_tl ;
end if;
End IF;
---
SET l_dynamic_sql = l_dynamic_sql ||
' OR date(start_date) != ' || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' ;
if (l_DateEnd is null) then
SET l_dynamic_sql = l_dynamic_sql || ' OR end_date is not null';
else
SET l_dynamic_sql = l_dynamic_sql || ' OR end_date is null OR date(end_date) != ' || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' ;
end if;
SET l_dynamic_sql = l_dynamic_sql || ')';
--INSERT INTO REFLOADER.dml_ins_upd VALUES (l_dynamic_sql);
PREPARE p_changed_cursor from l_dynamic_sql;
P4: BEGIN
DECLARE change_cursor CURSOR FOR p_changed_cursor;
OPEN change_cursor;
FETCH change_cursor INTO l_changed;
CLOSE change_cursor;
END P4;
IF (l_changed = 1)
THEN
SET l_stmt = 'Update SCHEMA1.' || l_table || ' SET display_name = ' || '''' || l_TS || '''' || ',';
--Exception FREQUENCY
If (l_table = 'FREQUENCY') Then
IF (l_TL IS NULL) THEN
SET l_stmt = l_stmt || ' numeric_value = ' || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || ' numeric_value = ' || l_tl || ',' ;
END IF;
End IF;
---
IF (l_DateEnd IS NULL) THEN
SET l_stmt = l_stmt || ' end_date = ' || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || ' end_date = ' || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' || ',' ;
END IF;
SET l_stmt = l_stmt || ' start_date = ' || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' ;
SET l_stmt = l_stmt || ' WHERE cid = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'XXX';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
END IF;
END IF;
-- Fetch Next
FETCH FROM cursor1 INTO
l_ID , l_CID , l_Tt , l_TL , l_DateStart , l_DateEnd, l_table;
END WHILE;
CLOSE cursor1;
END P2;
commit work;
END P1;
stored-procedures error-handling db2-luw
asked Nov 20 '18 at 18:05
sunny babausunny babau
167
add a comment |
0
0
0
Sometimes when we execute the error record we get bad data and the below stored procedure errors out with error code as
"sql> call REFLOADER.SP_ALL_ES_CODE()
[2018-11-20 10:14:01] [22004][-87] A null value was specified in a context where a null value is not allowed.. SQLCODE=-87, SQLSTATE=22004, DRIVER=4.23.42".
As such I would like to **split out the error code along with the error values/record set that is failing in the below script. can you please help with the code where and how can i get the error record output.**
enter code here
CREATE OR REPLACE PROCEDURE SCHEMA1.SP_ALL_CODE ()
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_stmt VARCHAR(5000);
DECLARE l_dynamic_sql VARCHAR(5000);
DECLARE l_uuid CHAR(36);
DECLARE l_ID VARCHAR(255);
DECLARE l_table VARCHAR(100);
DECLARE l_exists integer;
DECLARE l_changed integer;
DECLARE l_CID VARCHAR(255);
DECLARE l_Tt VARCHAR(255);
DECLARE l_TL VARCHAR(255);
DECLARE l_DateStart DATE;
DECLARE l_DateEnd DATE;
DECLARE l_DateEnd_Format DATE;
P2: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT c.ID , REPLACE(CID,'''',''''''), REPLACE(tt,'''',''''''), TL ,DateStart ,DateEnd , tablename
FROM SCHEMA1.ALL_CODE c, SCHEMA1.ID_TABLENAME t
WHERE c.id = t.id
and t.tabletype = 1
-- Access Location/Site (ignore end dated)
--and (c.oid != '2.16.840.1.113883.3.2390.2.2.26' OR DateEnd is null)
and (t.tablename != 'FREQUENCY' OR DateEnd is null)
and (t.tablename != 'ORDER_FREQUENCY' OR DateEnd is null)
Order By c.ID , CID;
-- Cursor left open for client application
OPEN cursor1;
FETCH FROM cursor1 INTO
l_ID , l_CID , l_Tt , l_TL , l_DateStart , l_DateEnd, l_table ;
WHILE(SQLSTATE = '00000')
DO
--Insert or Update
IF (l_DateStart IS NULL) THEN
SET l_DateStart = '1900-01-01';
END IF;
--exception
-- L_TEST (smart) 216840
If l_id = '216840' Then
SET l_CID = LPAD(l_CID,4,'0' );
End if;
SET l_exists = 0;
SET l_dynamic_sql = 'select count(*) from m_ref.' || l_table || ' where c_id =' || '''' || l_id || ':' || l_cId || '''' ;
--INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_dynamic_sql);
PREPARE p_exist_cursor from l_dynamic_sql;
P3: BEGIN
DECLARE exist_cursor CURSOR FOR p_exist_cursor;
OPEN exist_cursor;
FETCH exist_cursor INTO l_exists;
CLOSE exist_cursor;
END P3;
IF (l_exists = 0)
THEN
SET l_uuid = RANDOMUUID();
SET l_stmt = 'Insert into SCHEMA1.' || l_table ||
' (ID, CID, DISPLAY_NAME, END_DATE, START_DATE) values (' ||
'''' || l_uuid || '''' || ',' ||
'''' || l_oid || ':' || l_cId || '''' || ',' ||
'''' || l_Tt || '''' || ',';
IF (l_DateEnd IS NULL) THEN
SET l_stmt = l_stmt || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' || ',' ;
END IF;
SET l_stmt = l_stmt || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' || ');' ;
if (l_stmt is null) then
set l_stmt = 'YYY';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
-- Exceptions
-- CC
If l_table = 'CARECAT' Then
SET l_stmt = 'Update SCHEMA1.CARE_CATEGORY SET CONTEXT_NAME = ' || '''' || 'NOTE' || '''';
SET l_stmt = l_stmt || ' WHERE cid = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'BBB';
end if;
INSERT INTO REFLOADER.dml_ins_upd VALUES (l_stmt);
End if;
-- LTEST (Test) 2168401
If l_id = '2168401' Then
SET l_stmt = 'Update SCHEMA1.LTEST SET IS_FLAG = 0';
SET l_stmt = l_stmt || ' WHERE code_id = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'XXX';
end if;
INSERT INTO REFLOADER.dml_ins_upd VALUES (l_stmt);
End if;
-- LAB_TEST (Panel) 2168401
If l_id = '2168401' Then
SET l_stmt = 'Update SCHEMA1.LTEST SET IS_FLAG = 1';
SET l_stmt = l_stmt || ' WHERE code_id = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'TTT';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
End if;
------------------------------
END IF;
IF (l_exists != 0)
THEN
SET l_changed = 0;
SET l_dynamic_sql = 'select count(*) ' ||
' from SCHEMA1.' || l_table ||
' where cid = ' || '''' || l_id || ':' || l_cId || '''' ;
if (l_termshort is null) then
SET l_dynamic_sql = l_dynamic_sql ||
' and (display_name != null ';
else
SET l_dynamic_sql = l_dynamic_sql ||
' and (display_name != ' || '''' || l_tt || '''' ;
end if;
--Exception FREQUENCY
If (l_table = 'FREQUENCY') Then
if (l_tl is null) then
SET l_dynamic_sql = l_dynamic_sql ||
' OR numeric_value is not null ';
else
SET l_dynamic_sql = l_dynamic_sql ||
' OR numeric_value is null ' ||
' OR numeric_value != ' || l_tl ;
end if;
End IF;
---
SET l_dynamic_sql = l_dynamic_sql ||
' OR date(start_date) != ' || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' ;
if (l_DateEnd is null) then
SET l_dynamic_sql = l_dynamic_sql || ' OR end_date is not null';
else
SET l_dynamic_sql = l_dynamic_sql || ' OR end_date is null OR date(end_date) != ' || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' ;
end if;
SET l_dynamic_sql = l_dynamic_sql || ')';
--INSERT INTO REFLOADER.dml_ins_upd VALUES (l_dynamic_sql);
PREPARE p_changed_cursor from l_dynamic_sql;
P4: BEGIN
DECLARE change_cursor CURSOR FOR p_changed_cursor;
OPEN change_cursor;
FETCH change_cursor INTO l_changed;
CLOSE change_cursor;
END P4;
IF (l_changed = 1)
THEN
SET l_stmt = 'Update SCHEMA1.' || l_table || ' SET display_name = ' || '''' || l_TS || '''' || ',';
--Exception FREQUENCY
If (l_table = 'FREQUENCY') Then
IF (l_TL IS NULL) THEN
SET l_stmt = l_stmt || ' numeric_value = ' || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || ' numeric_value = ' || l_tl || ',' ;
END IF;
End IF;
---
IF (l_DateEnd IS NULL) THEN
SET l_stmt = l_stmt || ' end_date = ' || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || ' end_date = ' || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' || ',' ;
END IF;
SET l_stmt = l_stmt || ' start_date = ' || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' ;
SET l_stmt = l_stmt || ' WHERE cid = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'XXX';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
END IF;
END IF;
-- Fetch Next
FETCH FROM cursor1 INTO
l_ID , l_CID , l_Tt , l_TL , l_DateStart , l_DateEnd, l_table;
END WHILE;
CLOSE cursor1;
END P2;
commit work;
END P1;
stored-procedures error-handling db2-luw
asked Nov 20 '18 at 18:05
sunny babausunny babau
167
Sometimes when we execute the error record we get bad data and the below stored procedure errors out with error code as
"sql> call REFLOADER.SP_ALL_ES_CODE()
[2018-11-20 10:14:01] [22004][-87] A null value was specified in a context where a null value is not allowed.. SQLCODE=-87, SQLSTATE=22004, DRIVER=4.23.42".
As such I would like to **split out the error code along with the error values/record set that is failing in the below script. can you please help with the code where and how can i get the error record output.**
enter code here
CREATE OR REPLACE PROCEDURE SCHEMA1.SP_ALL_CODE ()
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_stmt VARCHAR(5000);
DECLARE l_dynamic_sql VARCHAR(5000);
DECLARE l_uuid CHAR(36);
DECLARE l_ID VARCHAR(255);
DECLARE l_table VARCHAR(100);
DECLARE l_exists integer;
DECLARE l_changed integer;
DECLARE l_CID VARCHAR(255);
DECLARE l_Tt VARCHAR(255);
DECLARE l_TL VARCHAR(255);
DECLARE l_DateStart DATE;
DECLARE l_DateEnd DATE;
DECLARE l_DateEnd_Format DATE;
P2: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT c.ID , REPLACE(CID,'''',''''''), REPLACE(tt,'''',''''''), TL ,DateStart ,DateEnd , tablename
FROM SCHEMA1.ALL_CODE c, SCHEMA1.ID_TABLENAME t
WHERE c.id = t.id
and t.tabletype = 1
-- Access Location/Site (ignore end dated)
--and (c.oid != '2.16.840.1.113883.3.2390.2.2.26' OR DateEnd is null)
and (t.tablename != 'FREQUENCY' OR DateEnd is null)
and (t.tablename != 'ORDER_FREQUENCY' OR DateEnd is null)
Order By c.ID , CID;
-- Cursor left open for client application
OPEN cursor1;
FETCH FROM cursor1 INTO
l_ID , l_CID , l_Tt , l_TL , l_DateStart , l_DateEnd, l_table ;
WHILE(SQLSTATE = '00000')
DO
--Insert or Update
IF (l_DateStart IS NULL) THEN
SET l_DateStart = '1900-01-01';
END IF;
--exception
-- L_TEST (smart) 216840
If l_id = '216840' Then
SET l_CID = LPAD(l_CID,4,'0' );
End if;
SET l_exists = 0;
SET l_dynamic_sql = 'select count(*) from m_ref.' || l_table || ' where c_id =' || '''' || l_id || ':' || l_cId || '''' ;
--INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_dynamic_sql);
PREPARE p_exist_cursor from l_dynamic_sql;
P3: BEGIN
DECLARE exist_cursor CURSOR FOR p_exist_cursor;
OPEN exist_cursor;
FETCH exist_cursor INTO l_exists;
CLOSE exist_cursor;
END P3;
IF (l_exists = 0)
THEN
SET l_uuid = RANDOMUUID();
SET l_stmt = 'Insert into SCHEMA1.' || l_table ||
' (ID, CID, DISPLAY_NAME, END_DATE, START_DATE) values (' ||
'''' || l_uuid || '''' || ',' ||
'''' || l_oid || ':' || l_cId || '''' || ',' ||
'''' || l_Tt || '''' || ',';
IF (l_DateEnd IS NULL) THEN
SET l_stmt = l_stmt || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' || ',' ;
END IF;
SET l_stmt = l_stmt || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' || ');' ;
if (l_stmt is null) then
set l_stmt = 'YYY';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
-- Exceptions
-- CC
If l_table = 'CARECAT' Then
SET l_stmt = 'Update SCHEMA1.CARE_CATEGORY SET CONTEXT_NAME = ' || '''' || 'NOTE' || '''';
SET l_stmt = l_stmt || ' WHERE cid = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'BBB';
end if;
INSERT INTO REFLOADER.dml_ins_upd VALUES (l_stmt);
End if;
-- LTEST (Test) 2168401
If l_id = '2168401' Then
SET l_stmt = 'Update SCHEMA1.LTEST SET IS_FLAG = 0';
SET l_stmt = l_stmt || ' WHERE code_id = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'XXX';
end if;
INSERT INTO REFLOADER.dml_ins_upd VALUES (l_stmt);
End if;
-- LAB_TEST (Panel) 2168401
If l_id = '2168401' Then
SET l_stmt = 'Update SCHEMA1.LTEST SET IS_FLAG = 1';
SET l_stmt = l_stmt || ' WHERE code_id = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'TTT';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
End if;
------------------------------
END IF;
IF (l_exists != 0)
THEN
SET l_changed = 0;
SET l_dynamic_sql = 'select count(*) ' ||
' from SCHEMA1.' || l_table ||
' where cid = ' || '''' || l_id || ':' || l_cId || '''' ;
if (l_termshort is null) then
SET l_dynamic_sql = l_dynamic_sql ||
' and (display_name != null ';
else
SET l_dynamic_sql = l_dynamic_sql ||
' and (display_name != ' || '''' || l_tt || '''' ;
end if;
--Exception FREQUENCY
If (l_table = 'FREQUENCY') Then
if (l_tl is null) then
SET l_dynamic_sql = l_dynamic_sql ||
' OR numeric_value is not null ';
else
SET l_dynamic_sql = l_dynamic_sql ||
' OR numeric_value is null ' ||
' OR numeric_value != ' || l_tl ;
end if;
End IF;
---
SET l_dynamic_sql = l_dynamic_sql ||
' OR date(start_date) != ' || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' ;
if (l_DateEnd is null) then
SET l_dynamic_sql = l_dynamic_sql || ' OR end_date is not null';
else
SET l_dynamic_sql = l_dynamic_sql || ' OR end_date is null OR date(end_date) != ' || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' ;
end if;
SET l_dynamic_sql = l_dynamic_sql || ')';
--INSERT INTO REFLOADER.dml_ins_upd VALUES (l_dynamic_sql);
PREPARE p_changed_cursor from l_dynamic_sql;
P4: BEGIN
DECLARE change_cursor CURSOR FOR p_changed_cursor;
OPEN change_cursor;
FETCH change_cursor INTO l_changed;
CLOSE change_cursor;
END P4;
IF (l_changed = 1)
THEN
SET l_stmt = 'Update SCHEMA1.' || l_table || ' SET display_name = ' || '''' || l_TS || '''' || ',';
--Exception FREQUENCY
If (l_table = 'FREQUENCY') Then
IF (l_TL IS NULL) THEN
SET l_stmt = l_stmt || ' numeric_value = ' || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || ' numeric_value = ' || l_tl || ',' ;
END IF;
End IF;
---
IF (l_DateEnd IS NULL) THEN
SET l_stmt = l_stmt || ' end_date = ' || 'NULL' || ',' ;
ELSE
SET l_stmt = l_stmt || ' end_date = ' || '''' || VARCHAR_FORMAT(l_DateEnd, 'YYYY-MM-DD') || '''' || ',' ;
END IF;
SET l_stmt = l_stmt || ' start_date = ' || '''' || VARCHAR_FORMAT(l_DateStart, 'YYYY-MM-DD') || '''' ;
SET l_stmt = l_stmt || ' WHERE cid = ' || '''' || (l_id || ':' || l_cId) || '''' || ';' ;
if (l_stmt is null) then
set l_stmt = 'XXX';
end if;
INSERT INTO SCHEMA1.dml_ins_upd VALUES (l_stmt);
END IF;
END IF;
-- Fetch Next
FETCH FROM cursor1 INTO
l_ID , l_CID , l_Tt , l_TL , l_DateStart , l_DateEnd, l_table;
END WHILE;
CLOSE cursor1;
END P2;
commit work;
END P1;
stored-procedures error-handling db2-luw
stored-procedures error-handling db2-luw
asked Nov 20 '18 at 18:05
sunny babausunny babau
167
asked Nov 20 '18 at 18:05
sunny babausunny babau
167
asked Nov 20 '18 at 18:05
sunny babausunny babau
167
asked Nov 20 '18 at 18:05
sunny babausunny babau
167
asked Nov 20 '18 at 18:05
sunny babausunny babau
167
167
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
Your tool must be able to display so called SQLCA structure returned by the database manager on your statement. DB2 Command Line Processor displays this structure with '-a' parameter. The line of code with an error is in the sqlerrd(3) field (see the example at the link).
answered Nov 22 '18 at 9:40
Mark BarinsteinMark Barinstein
1,916125
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398956%2fhow-to-output-error-record-as-part-of-db2-stored-procedure-standard-output%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Your tool must be able to display so called SQLCA structure returned by the database manager on your statement. DB2 Command Line Processor displays this structure with '-a' parameter. The line of code with an error is in the sqlerrd(3) field (see the example at the link).
answered Nov 22 '18 at 9:40
Mark BarinsteinMark Barinstein
1,916125
add a comment |
0
Your tool must be able to display so called SQLCA structure returned by the database manager on your statement. DB2 Command Line Processor displays this structure with '-a' parameter. The line of code with an error is in the sqlerrd(3) field (see the example at the link).
answered Nov 22 '18 at 9:40
Mark BarinsteinMark Barinstein
1,916125
add a comment |
0
0
0
Your tool must be able to display so called SQLCA structure returned by the database manager on your statement. DB2 Command Line Processor displays this structure with '-a' parameter. The line of code with an error is in the sqlerrd(3) field (see the example at the link).
answered Nov 22 '18 at 9:40
Mark BarinsteinMark Barinstein
1,916125
Your tool must be able to display so called SQLCA structure returned by the database manager on your statement. DB2 Command Line Processor displays this structure with '-a' parameter. The line of code with an error is in the sqlerrd(3) field (see the example at the link).
answered Nov 22 '18 at 9:40
Mark BarinsteinMark Barinstein
1,916125
answered Nov 22 '18 at 9:40
Mark BarinsteinMark Barinstein
1,916125
answered Nov 22 '18 at 9:40
Mark BarinsteinMark Barinstein
1,916125
answered Nov 22 '18 at 9:40
Mark BarinsteinMark Barinstein
1,916125
1,916125
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398956%2fhow-to-output-error-record-as-part-of-db2-stored-procedure-standard-output%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
