remove large number of rows using SQL only

[Henry J.]

I'm new to DB2. I want to remove large number of rows in a table
without filling up the transaction log space. To this end we can
repeatedly remove a fixed number of rows followed by commit. In
Sybase, one can write a simple transact SQL using rowcount and while
loop to. I saw several people suggesting similar ways in DB2 to
achieve this. I tried to write a compound SQL but failed to get it
work. Haven't been able to get the syntax right.

Can anybody please advise how to get it right? Thanks!


BEGIN ATOMIC

DECLARE rows_deleted INT default 1;

WHILE rows_deleted 0 DO

delete from myTable t

where t.prodID in

(select p.prodID from tableB b

where t.vendor = b.vendor

fetch first 1000 rows only);

commit;

get diagnostics rows_deleted = row_count;

END WHILE;

END

[db2dude]

Re: remove large number of rows using SQL only

You cannot commit inside an atomic compound statement (begin atomic).

Try doing the same inside a stored procedure (with "begin" instead of
"begin atomic" ).

something on these lines...

create procedure xx.yyyy ()
language sql
begin
declare delcount integer default 0;

repeat
delete from (
select 1
from <schema>.<table >
fetch first 1000 rows only
);
get diagnostics delcount = row_count;
commit;
until delcount = 0
end repeat;


end;


Hope that helps.

-SA

[Henry J.]

Re: remove large number of rows using SQL only


Thanks a lot! Is it possible to do the same without creating a stored
proc?

On Jul 23, 4:34 pm, db2dude <abhyan...@gmai l.comwrote:

You cannot commit inside an atomic compound statement (begin atomic).
>
Try doing the same inside a stored procedure (with "begin" instead of
"begin atomic" ).
>
something on these lines...
>
create procedure xx.yyyy ()
language sql
begin
   declare delcount integer default 0;
>
   repeat
   delete from (
             select 1
             from <schema>.<table >
             fetch first 1000 rows only
   );
   get diagnostics delcount = row_count;
   commit;
until delcount = 0
end repeat;
>
end;
>
Hope that helps.
>
-SA

[Henry J.]

Re: remove large number of rows using SQL only


Thanks a lot! Is it possible to do the same without creating a stored
proc?

On Jul 23, 4:34 pm, db2dude <abhyan...@gmai l.comwrote:

You cannot commit inside an atomic compound statement (begin atomic).
>
Try doing the same inside a stored procedure (with "begin" instead of
"begin atomic" ).
>
something on these lines...
>
create procedure xx.yyyy ()
language sql
begin
   declare delcount integer default 0;
>
   repeat
   delete from (
             select 1
             from <schema>.<table >
             fetch first 1000 rows only
   );
   get diagnostics delcount = row_count;
   commit;
until delcount = 0
end repeat;
>
end;
>
Hope that helps.
>
-SA

[db2dude]

Re: remove large number of rows using SQL only

On Jul 24, 12:36 pm, "Henry J." <tank209...@yah oo.comwrote:

Thanks a lot!  Is it possible to do the same without creating a stored
proc?
>
On Jul 23, 4:34 pm, db2dude <abhyan...@gmai l.comwrote:
>

You cannot commit inside an atomic compound statement (begin atomic).

>

Try doing the same inside a stored procedure (with "begin" instead of
"begin atomic" ).

>

something on these lines...

>

create procedure xx.yyyy ()
language sql
begin
   declare delcount integer default 0;

>

   repeat
   delete from (
             select 1
             from <schema>.<table >
             fetch first 1000 rows only
   );
   get diagnostics delcount = row_count;
   commit;
until delcount = 0
end repeat;

>

end;

>

Hope that helps.

>

-SA

I dont think that is possible since you would need to issue a 'commit'
statement you will not be able to do that inside a dynamic compound
sql (begin atomic .. end)

- SA

[Serge Rielau]

Re: remove large number of rows using SQL only

You can drive the loop from the client in any language you wish.


Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab