I am writing a DB2 batch program to delete rows of a table sharred by CICS. In the batch program I open a cursor for first 100 rows- then I issue a fetch for each row, enqueue on the row to communicate with cics, and then delete the row. When I hit 100 rows enqueued and deleted, I commit the changes and dequeue the rows. Then I reopen the cursor and continue with the first 100 rows again doing fetch enqueue delete and dequeue.
The problem I am encountering, is tht DB2 does not appear to give me all the qualifying rows before I hit end of table - sqlcode = 100. I know this because after my program runs to delete all rows of a certain criteria, when I run a querry, there are lots of qualifying rows that were not fetched and deleted. Does anyone have any idea why my approach might not be working? The open cursor for first 100 rows should give me the first 100 rows meeting my criteria. When I issue the commit after deleting 100 rows, they should all be deleted so when the program opens the cursor again for the first 100 rows (because the commit closes the cursor) I should get the next 100 qualifying rows.
Anyone have any ideas on why I am not getting all the qualifying rows?
Thank you.
Steve

I think the problem is on the line 47 of your batch program.

why keep opening/closing cursor? Why not run cursor, delete where current of and then issue periodic commit. As to why you are not getting all rows? How about you fetched you last 100 rows you only fetched 50 and deleted them, but in the meantime more rows were inserted? Then again it could still be in line 47(loved that one Nick).
Dave

Hi Dave,
I have a table with thousands of rows. I want to delete some of the rows using a COBOL MVS batch program. A CICS system application is accessing this table also, but we do not want to this program to impact the CICS application. This COBOL batch program does cursor open fetch/delete processing. After about 100 deletes I issue a commit then reopen the table (because the commit closes it) and continue the fetch/delete processing. Unfortunately, after 500 to 700 deletes the fetch senses an end of table sqlcode 100 and the program ends because DB2 says it is at the end of table. However, I then run a spufi sql querry and find there a thousands of records left which meet my criteria. This is a test system where there is almost not activity (no one is adding rows). I run the batch program again and it deletes 500 to 700 more rows then senses an end of table after the fetch and closes down again. The commit causes the cursor to be closed, so I am opening the cursor again after every commit which is issued after every 100 deletes. I cannot understand why the fetch encounters an end of table when there are more rows left which meet the select criteria. I am not a db2 specialist, but feel there must be some system parameter, partitioning parameter or something which is causing DB2 to sense end of table even though it has not processed all the rows. If you have any experise in DB2 I would appreciate hearing any ideas you might have regarding this problem.
Thanks,
Steve

Try with hold onyour cursor declaration, your cursor will stay open through the commits.
Dave

Hi, my name is Alvaro i from Spain. Could you pass me the code of JCL??. My problem is that i need delete rows with the query for the several tables.

thanks for all.

You can use Delete... where current of

You got +100, but your last rowset is not full and not empty.

You can use after +100 BEFORE LAST ROWSET...

DECLARE cursor-name SENSITIVE SCROLL CURSOR...

Lenny