Update Statement and frequent commits

[Michel Esber]

Hello,

DB2 v8 FP15 luw.

Serge´s SQL on Fire has the following code for granular commits on
delete statements:

loop: LOOP
DELETE FROM (SELECT 1 FROM T WHERE ID = ? FETCH FIRST 1000 ROWS
ONLY) AS D;
IF SQLCODE = 100 THEN LEAVE loop;
END IF;
COMMIT;
END LOOP loop;

I want to achieve the same for Update statements on multi million
tables. My first try is:

loop: LOOP
update from T where ID IN

(
select ID from
(select ID, rownumber() over (partition by ID order by ID desc) as
RN from T where ID = ? ) as x
where rn <= 1000
)

IF SQLCODE = 100 THEN LEAVE loop;
END IF;
COMMIT;
END LOOP loop;

Any better suggestion or approach ?

Thanks, Michel

[Michel Esber]

Ok .. I know I sent a bad example. Here´s another try:

update T set field = 'new_value' where (ID) in (select ID from T where
field1='xyz' and field2 = 'foo' fetch first 1000 rows only)

However , the plan has two index scans. Is there any better solution
with a single scan ?

The delete statement posted before has only one index scan ... Can I
achieve the same with update ?

Thanks, Michel

[Serge Rielau]

Michel Esber wrote:
> Ok .. I know I sent a bad example. Here愀 another try:

>
> update T set field = 'new_value' where (ID) in (select ID from T where
> field1='xyz' and field2 = 'foo' fetch first 1000 rows only)
>
> However , the plan has two index scans. Is there any better solution
> with a single scan ?
>
> The delete statement posted before has only one index scan ... Can I
> achieve the same with update ?

Yes. The trick is to use the select as the update target, just as the
delete is.
UPDATE (SELECT ....) AS T SET ...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab