Mass delete using cursors in COBOL DB2 program.

[Mohit]

Hi All,

In an interview, I have been asked that - is it possible to do mass delete the rows of a table using cursor? I was not sure about the answer, can you please help.

[zprogrammer]

Yes Delete could be done using WHERE current of CURSOR and also doing a frequent commits is needed.

something like

Code: Select all

Open Cursor 
Fetch cursor 
perform until end of cursor 
     Delete 
     Fetch
end-perform

[Mohit]

But is it really a mass delete as curosr will work on sngle row at a time?

[zprogrammer]

Many a times when you do a mass delete there is more probablity of making the table resources unavailable for other applications..
I would regard above solution with frequent commits makes it available for other applications while execution..
Note : Adding a frequent commit should also be accompanied by making your cursor WITH HOLD

Also if you are looking for a solution outside of the application programming check the REORG DISCARD option as well

[Mohit]

No I'm not looking for a solution outside application programming, it's an interview question. I still wonder if cursor can be used for mass delete...

[zprogrammer]

IMHO No

[Chandan Yadav]

Hi Mohit,

I am not sure what you meant by mass delete, is it deleting all rows in one go which satisfy criteria of WHERE clause of DECLARE statement or deleting multiple rows using cursor?

If you are using Cursor with rowset positioning you can delete multiple rows with one WHERE CURRENT OF execution. This is what you meant to say by mass delete?

Please refer below link for more details

http://www-01.ibm.com/support/knowledge ... ursor.dita

Reagrds,
Chandan

[zprogrammer]

I agree with Chandan , If you are looking for an option to Delete multiple rows using ROWSET option at one go that is a better option...

To reiterate his question : will you consider/regard it as a mass delete?

[Chandan Yadav]

I guess as it was an Interview question, Mohit also might not be aware what Interviewer was expecting.

But yes we can answer using rowset as an option if such question comes in future