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]

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

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

On Jul 23, 4:34*pm, db2dude <[e-mail]:

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

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

On Jul 23, 4:34*pm, db2dude <[e-mail]:

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]

On Jul 24, 12:36*pm, "Henry J." <[e-mail]:

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

On Jul 23, 4:34*pm, db2dude <[e-mail]:

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]

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab