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
 
Delete Multiple rows in chunks by using Cursor

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

New User


Joined: 10 Mar 2011
Posts: 34
Location: india

PostPosted: Tue Sep 11, 2018 8:44 pm    Post subject: Delete Multiple rows in chunks by using Cursor
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: 442
Location: USA

PostPosted: Tue Sep 11, 2018 9:35 pm    Post subject: Reply to: Delete Multiple rows in chunks by using Cursor
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

Senior Member


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

PostPosted: Tue Sep 11, 2018 11:56 pm    Post subject:
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: 34
Location: india

PostPosted: Wed Sep 12, 2018 8:54 am    Post subject:
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

Senior Member


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

PostPosted: Wed Sep 12, 2018 8:57 am    Post subject:
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: 2407
Location: @my desk

PostPosted: Wed Sep 12, 2018 6:29 pm    Post subject:
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    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 Need help on the multiple files refor... wik123 DFSORT/ICETOOL 7 Wed Sep 19, 2018 10:47 am
No new posts Remove selected duplicate rows from t... hnharish DFSORT/ICETOOL 3 Wed Sep 05, 2018 3:11 am
No new posts Concatenaing multiple rows to single ... Vignesh Sid DFSORT/ICETOOL 1 Tue Aug 14, 2018 8:59 am
No new posts How to delete large data. Mounika Nemani DB2 3 Wed Jun 13, 2018 1:58 pm
No new posts Splitting one row into multiple Rows ... Rajan Moorthy DFSORT/ICETOOL 9 Mon May 14, 2018 10:58 pm

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