SEO

#1 (**permalink**) 05-11-2008, 08:41 PM

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

#2 (**permalink**) 05-12-2008, 09:08 AM

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

#3 (**permalink**) 05-12-2008, 09:08 AM

Michel Esber wrote:
> 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 ?
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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Update Statement and frequent commits