Home > IT Answers > Database > Dynamic sql with delete and commit
Help

Question

  Asked: Jul 6 2005   11:18 AM GMT
  Asked by: sramki

Dynamic sql with delete and commit


DB2, DB2 Administration, DB2 Everyplace

Hi,

I need to delete rows from different db2 tables in COBOL with commit in between after some 100 or 200 rows. Is there any away I can restrict my delete statement for some particular rows as in Select for fetch first 100 rows only. The values of table names will be known only during the execution of the program. I thought of trying first select with fetch first 100 rows and use delete where current of option. So I prepared my dynamic sql of select with fetch first 100 rows only and executed fetch statement. Now when I run my delete statement with current of using Execute Immediate, the delete statement is failing. It?s not able to point to the row fetched before. I am executing my delete statement only before the closing the cursor. Without dynamic sql this select and delete current of works fine. It would be a great help if you could give a solution for this.

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Jul 6 2005  9:53 PM GMT  by TheQuigs




I don't know what platform you're running on, but DB2 UDB for OS/400 or i5/OS has no 'fetch first' clause -- you aren't fetching the rows in a delete statement. If you knew the tables ahead of time, I would say a stored procedure might work. For Dynamic SQL, I have no idea how you'll handle it.

Sorry. :-(
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Stanton  |   Jul 7 2005  8:15AM GMT

I don’t work in COBOL any more so I don’t have a way to test this out, but I believe you could fetch a set number of rows by fetching into an array. That way you could fetch 100 rows do whatever processing you need to do on them and then fetch the next 100 rows and so on.

Stanton

 

MFleming  |   Jul 7 2005  10:16AM GMT

There are two types of DELETE, the searched DELETE and the positioned DELETE. When you use DELETE WHERE CURRENT OF, you’re using the positioned DELETE. It will DELETE only the current row you are positioned on. Your FETCH FIRST 100 ROWS ONLY clause will limit the number of rows to at most 100. You still have to fetch a row, issue the DELETE WHERE CURRENT OF statement, and repeat until all rows have been fetched and deleted. Is this the process you’re following? You cannot issue the DELETE WHERE CURRENT OF once and expect it to delete all 100 rows of the cursor; it doesn’t work that way. A searched DELETE could, but it would not be limited to 100 rows. It would be limited only by the WHERE clause, and if the granularity of your predicates qualifies to 500 rows instead of 100, you’d be deleting 500 rows. To control the number of rows deleted, you’ve got to use the positioned delete. Hope this helps.

 

Msenatore@fisa  |   Jun 25 2008  8:06PM GMT

Hi Sramki,

Why are you deleteing rows only 100 to 200 rows at a time?
What platform are your running on?
If it is Z/os and you eventually need to delete all rows and your program is only deleting ( meaning no other processing or reporting) then do the following:

1) If the tablespace contining the table is simple. do a dummy load “log no noreuse” - most efficient - no logging.
2) If the tablespace is segmented (seg size > 0) do a mass delete. it will take a sub second, logging
occurs in the background.
3) If the tablespace is partitioned, dummy load each partition independantly.

After the delete operation, don;t forget to run a reorg and a runstat utility.

Naturally, try this in development first.