remove large number of rows

[Henry J.]

I'm DB2 newbie. I need to remove large number of rows from a table.
I don't want to fill up the transaction log space. I have the below
SQL that I feed to the db2 command to repeatly remove a fixed set of
rows until there is no more to remove. However db2 complains about
bad syntax. Can anybody advise how I get it right?

Thanks!

DECLARE rows_deleted INT default 1000;
WHILE rows_deleted 0 DO
delete from rcdb.risk_values rv
where rv.PRODUCT_ID_I in
(select pr.PRODUCT_ID_I from
rcdb.risk_values_status rvs, rcdb.PRODUCTS pr
where rvs.UND_SYM_C = pr.UND_SYM_C
fetch first 1000 rows only);
commit;
get diagnostics rows_deleted = row_count;
END WHILE;

[jefftyzzer]

On Jul 22, 3:39 pm, "Henry J." <[e-mail]:

I'm DB2 newbie. I need to remove large number of rows from a table.
I don't want to fill up the transaction log space. I have the below
SQL that I feed to the db2 command to repeatly remove a fixed set of
rows until there is no more to remove. However db2 complains about
bad syntax. Can anybody advise how I get it right?

Thanks!

DECLARE rows_deleted INT default 1000;
WHILE rows_deleted 0 DO
delete from rcdb.risk_values rv
where rv.PRODUCT_ID_I in
(select pr.PRODUCT_ID_I from
rcdb.risk_values_status rvs, rcdb.PRODUCTS pr
where rvs.UND_SYM_C = pr.UND_SYM_C
fetch first 1000 rows only);
commit;
get diagnostics rows_deleted = row_count;
END WHILE;

You'll have to do it in either a stored procedure or a BEGIN
ATOMIC...END block.

--Jeff

[Serge Rielau]

jefftyzzer wrote:

On Jul 22, 3:39 pm, "Henry J." <[e-mail]:

>I'm DB2 newbie. I need to remove large number of rows from a table.
I don't want to fill up the transaction log space. I have the below
SQL that I feed to the db2 command to repeatly remove a fixed set of
rows until there is no more to remove. However db2 complains about
bad syntax. Can anybody advise how I get it right?

Thanks!

DECLARE rows_deleted INT default 1000;
WHILE rows_deleted 0 DO
delete from rcdb.risk_values rv
where rv.PRODUCT_ID_I in
(select pr.PRODUCT_ID_I from
rcdb.risk_values_status rvs, rcdb.PRODUCTS pr
where rvs.UND_SYM_C = pr.UND_SYM_C
fetch first 1000 rows only);
commit;
get diagnostics rows_deleted = row_count;
END WHILE;

You'll have to do it in either a stored procedure or a BEGIN
ATOMIC...END block.

Because of the COMMIT it can't be in a dynamic compound (BEGIN ATOMIC)
Anyway: Which version/platform of DB2 and what is the exact syntax error?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab