Re: Delete Huge Number Of Rows Failed

Dear Sir ,
Thanks for your immediate reply .

Here is my situation , i am trying to delete based on a specific condition
as follows :
delete from MQSISIS.ASI_REQ_LOG where DAYS(current timestamp)-DAYS(DATE_0)>2
iw ant to delete all the transactions that are 2 days history
and keep the other inside the table.
Estimate no of rows is 1 million and the deleted is about 250000 every day

So how can i produce a procedure to handle this request

Best regards

abdelhakeem Sabih wrote:

> Dear Sir ,
> Thanks for your immediate reply .
>
> Here is my situation , i am trying to delete based on a specific condition
> as follows :
> delete from MQSISIS.ASI_REQ_LOG where DAYS(current
> timestamp)-DAYS(DATE_0)>2 iw ant to delete all the transactions that are 2
> days history and keep the other inside the table.
> Estimate no of rows is 1 million and the deleted is about 250000 every day
>
> So how can i produce a procedure to handle this request

A couple ideas that I had immediately:

DECLARE c1 CURSOR WITH HOLD FOR SELECT ...
OPEN c1
while
DELETE WHERE CURRENT OF c1
COMMIT every n records


Another idea is to select just a few rows and delete those. Repeat that
until nothing gets deleted any longer:

loop until #affected rows = 0
DELETE
FROM ...
WHERE id IN ( SELECT id
FROM ...
WHERE ...
FETCH FIRST n ROWS ONLY )

Or you can use the row_number function instead of FETCH FIRST n ROWS ONLY.

Yet another approach could be range partitioning tables. A simple
DETACH/DROP could take care of the DELETE operation. But that may result
in more administrative overhead to maintain the various ranges on your
resolution.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Dear Sir ,

Thanks For your reply .
would you please write this procedure for me, becuase i am new at DB2
i will appreciate this a lot .

Regards

Urgent Call ,
Can Anybody Help My In Producing a Stored Procedure That Can Delete Million Record


Regards

taking the approach of Knut here is your procedure:



-- file: MQSISIS_del_history.sql
--
-- make with: db2 -td@ -f MQSISIS_del_history.sql
--
-- execute with: db2 "call MQSISIS.del_history ()"

create procedure MQSISIS.del_history ()
language sql

begin
declare commit_count integer default 3;
declare row_no integer default 0;

for f1 as c1 cursor with hold for select *
from MQSISIS.ASI_REQ_LOG
where DAYS(current timestamp)-DAYS(DATE_0)>2
do
set row_no = row_no + 1;
delete from MQSISIS.ASI_REQ_LOG where current of c1;
if (row_no = commit_count) then
set row_no = 0;
commit;
end if;
end for;
end@


Don't forget to put in your desired value for commit_count


cheers
aka.