Dynamic sql with delete and commit

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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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. 🙁

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Stanton
    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
    0 pointsBadges:
  • MFleming
    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.
    0 pointsBadges:
  • Msenatore@fisa
    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.
    185 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: