Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1278
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: 1610
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 Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us