sunny babau
Reputation: 195
How to output error record as part of DB2 Stored Procedure standard output
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.
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;
Upvotes: 0
Views: 1270
Answers (1)
Mark Barinstein
Reputation: 12314
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).
Upvotes: 1
Related Questions
- DB2 Stored Procedure try catch
- DB2 Concatenate string to error inside stored procedure
- how to create SQL procedure which contains compile time errors?
- DB2 LUW - Get Error Line in Stored Procedure
- How to skip error lines in an insert db2?
- Getting SQLCODE and SQLSTATE from inner SQL call in procedure DB2
- sql error handling and output
- Propagate default SQL Exception from DB2 stored procedure exit handler
- Print error message in console from DB2 procedure
- Retrieve return value from DB2 stored procedure