IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Delete Multiple rows in chunks by using Cursor


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
dharmaraok

New User


Joined: 10 Mar 2011
Posts: 45
Location: india

PostPosted: Tue Sep 11, 2018 8:44 pm
Reply with quote

I want to delete the records in a table in chunks by using cursor.

Cursor normally fetches the records one by one and delete the records one by one. but I need to fetch the records in Chunk - Say Example fetch the 100 records at one go and delete the records at one GO.

Is there any syntax available in the Cursor. I have checked the manuals but i did not got any such of syntax.

Could you please help us for my Query
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Sep 11, 2018 9:35 pm
Reply with quote

If you want to delete multiple rows then you can simply write:
Code:
DELETE FROM TABLE
WHERE COLUMN > :WS-VAR1
  AND COLUMN < :WS-VAR2

The number of rows deleted will depend on the range you provide with WS-VAR1 and WS-VAR2.

If you want multiple chunks to be deleted then just keep executing this code with different values of WS-VAR1 and WS-VAR2.

Why do you think you want to use the Cursor?

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Sep 11, 2018 11:56 pm
Reply with quote

Quote:
Cursor normally fetches the records one by one and delete the records one by one. but I need to fetch the records in Chunk - Say Example fetch the 100 records at one go and delete the records at one GO.

Is there any syntax available in the Cursor. I have checked the manuals but i did not got any such of syntax.

This is what you need to for what you ask (Multi ROW Fetch). Read the last bullet point..

Code:
WHERE CURRENT OF cursor-name Identifiesthe cursor to be used in the delete operation. cursor-name must identify a declared cursor as explained in the description of the DECLARE CURSOR statement in DECLARE CURSOR. If the DELETE statement is embedded in a program, the DECLARE CURSOR statement must include select-statement rather than statement-name.
The table or view named must also be named in the FROM clause of the SELECT statement of the cursor, and the result table of the cursor must be capable of being deleted. For an explanation of read-only result tables, see Read-only cursors. Note that the object of the DELETE statement must not be identified as the object of the subquery in the WHERE clause of the SELECT statement of the cursor.

If the cursor is ambiguous and the plan or package was bound with CURRENTDATA(NO), DB2 might return an error to the application if DELETE WHERE CURRENT OF is attempted for any of the following:
•A cursor that is using block fetching
•A cursor that is using query parallelism
•A cursor that is positioned on a row that has been modified by this or another application process

When the DELETE statement is executed, the cursor must be open and positioned on a row or rowset of the result table.
•If the cursor is positioned on a single row, that row is the one deleted, and after the deletion the cursor is positioned before the next row of its result table. If there is no next row, the cursor positioned after the last row.
•If the cursor is positioned on a rowset, all rows corresponding to the rows of the current rowset are deleted, and after the deletion the cursor is positioned before the next rowset of its result table. If there is no next rowset, the cursor positioned after the last rowset.
A positioned DELETE must not be specified for a cursor that references a view on which an instead of delete trigger is defined, even if the view is an updatable view.

How to do Multi Row Fetch
How to Delete Multiple Row
Back to top
View user's profile Send private message
dharmaraok

New User


Joined: 10 Mar 2011
Posts: 45
Location: india

PostPosted: Wed Sep 12, 2018 8:54 am
Reply with quote

Hi ,

Can any one provide the sample pgm for

Multi Row Fetch and after that Delete Multiple Row
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Sep 12, 2018 8:57 am
Reply with quote

No unfortunately and that’s not how it works and even if someone does and gives you the code , how should it help you to learn anything anyway when you don’t try anything yet by yourself ?
You got to spend time reading the above links and figure out the coding by yourself. You have got it enough to start with and spend time to do research on internet and find out ready made programs or code which satisfies what you looking for in case if that’s simpler.

On the other note if you got to be too lazy then implement what RahulG31 has suggested and he has given the code too.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Sep 12, 2018 6:29 pm
Reply with quote

dharmaraok,

You could find some info here on Declare, Open, Fetch, Positioned Delete and Close rowset cursors, with some examples. Good luck.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts DELETE SPUFI DB2 1
No new posts To get the count of rows for every 1 ... DB2 3
No new posts DSNTIAUL driven delete IBM Tools 0
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
Search our Forums:

Back to Top