Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 Stored Procedure - Dynamic SQL - Cursor Delete

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Feb 17, 2016 5:10 pm    Post subject: DB2 Stored Procedure - Dynamic SQL - Cursor Delete
Reply with quote

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
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Feb 17, 2016 8:42 pm    Post subject:
Reply with quote

most likely :
Quote:
A delete was performed from a read-only view or for an update in which the cursor was not defined with the FOR UPDATE clause
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1806
Location: NY,USA

PostPosted: Wed Feb 17, 2016 9:44 pm    Post subject:
Reply with quote

Have you verified this ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Skip file from delete pema_yozer All Other Mainframe Topics 3 Tue Aug 29, 2017 3:19 pm
No new posts Delete IMS remote transaction when MO... Sam Singh IMS DB/DC 0 Wed Aug 23, 2017 9:12 pm
No new posts REXX - Dynamic file Creation d_sarlie CLIST & REXX 7 Tue Jun 27, 2017 7:30 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us