gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi,
I am trying to implement a generic db2 stored procedure which will take parameters like tablename, where condition and will perform delete using cursor and will do commits baesd on commit frequency passed as parameter.
I have coded the below procedure, i am getting SQLCODE as -510 - THE TABLE DESIGNATED BY THE CURSOR OF THE UPDATE OR DELETE STATEMENT CANNOT BE MODIFIED.
I am not sure what I am missing here. Can you please help me out?
| Code: |
CREATE PROCEDURE XXXXX.DELETE_WITH_COMMIT_COUNT
( IN v_TABLE_NAME VARCHAR(50),
IN v_COMMIT_COUNT INTEGER,
IN v_WHERE_CONDITION VARCHAR(1024),
OUT v_SQLCODE INTEGER,
OUT v_SQLSTATE CHAR(05),
OUT v_ERROR_TXT VARCHAR(1000),
OUT v_ROWS_DELETED INTEGER,
OUT v_DELETE_QUERY VARCHAR(2000)
)
VERSION V1
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
DYNAMIC RESULT SETS 0
ALLOW DEBUG MODE
PACKAGE OWNER XXX
WLM ENVIRONMENT FOR DEBUG MODE XXXXXX
DYNAMICRULES RUN
ISOLATION LEVEL CS
DEGREE 1
P1: BEGIN
-- DECLARE Statements
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(05) DEFAULT '00000';
DECLARE FETCHCOL INTEGER;
DECLARE w_COUNTER INTEGER DEFAULT 0;
DECLARE END_TABLE INTEGER DEFAULT 0;
DECLARE w_Total INTEGER DEFAULT 0;
DECLARE w_DELETE_QUERY VARCHAR(1024);
DECLARE w_DELETE_QUERY_d VARCHAR(1024);
DECLARE w_WHERE_CONDITION VARCHAR(1024);
DECLARE w_DELETE_STATEMENT STATEMENT;
DECLARE S1 CURSOR WITH HOLD FOR w_DELETE_STATEMENT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
L1: BEGIN
GET DIAGNOSTICS CONDITION 1
v_ERROR_TXT = MESSAGE_TEXT,
v_SQLCODE = DB2_RETURNED_SQLCODE,
v_SQLSTATE = RETURNED_SQLSTATE;
SELECT w_DELETE_QUERY INTO v_DELETE_QUERY
FROM SYSIBM.SYSDUMMY1;
END L1;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = 1;
SET w_WHERE_CONDITION = REPLACE(v_WHERE_CONDITION,'!@','''');
SET w_DELETE_QUERY = 'SELECT 1 FROM ' ||
v_TABLE_NAME ||
' WHERE 0 = 0 ' ||
w_WHERE_CONDITION ||
' ; ';
PREPARE w_DELETE_STATEMENT FROM w_DELETE_QUERY;
OPEN S1;
FETCH S1 INTO FETCHCOL;
SET w_DELETE_QUERY_d = 'DELETE FROM ' ||
v_TABLE_NAME ||
' WHERE CURRENT OF S1; ';
WHILE END_TABLE = 0 DO
EXECUTE IMMEDIATE w_DELETE_QUERY_d;
SET w_COUNTER=w_COUNTER + 1;
IF (w_COUNTER = v_COMMIT_COUNT) THEN
COMMIT;
SET w_Total = w_Total + w_COUNTER;
SET w_COUNTER = 0;
END IF;
END WHILE;
COMMIT;
SET w_Total = w_Total + w_COUNTER;
CLOSE S1;
SET v_SQLCODE = SQLCODE;
SET v_ROWS_DELETED = w_Total;
SET v_DELETE_QUERY = w_DELETE_QUERY;
END P1 |
|
|
