i see in this forum to make this routine using

"DELETE FROM (SELECT 1 FROM <name> WHERE <condition> FETCH FIRST <n>
ROWS
ONLY)
Prepare this one ONCE then EXECUTE in the loop. "

it works .

but now i find it's a bad performance when the data is scattering and
no suitable index is defined.

i think the reason is it will make a tablescan for each loop .

in these case i use another procedure which use a cursor to fetch rows
, so it only perform one table scan .

but the problem is : i have to define a static cursor for each table I
want to delete . 'cause it note that " the curcor XXX is not define"
in running time , after i create the cursor dynamicly with a string and
compile it well . it's so weird .

heming_g (AT) hotmail (DOT) com wrote:

Something like that should do...

CREATE PROCEDURE ....
BEGIN
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SET txt = 'SELECT 1 FROM .... FOR UPDATE';
PREPARE stm FROM txt;
OPEN cur;
....
END


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab