How to update a huge table in smaller chunks
Is there any command in Informix that is equivalent to "SET ROWCOUNT" of Sybase/MS Sql Server ? Basically I am trying to update a column in a huge table (50 million rows) without a WHERE clause (need to set a flag in all the rows). In Sybase, I can do "SET ROWCOUNT 1000" and write a small WHILE loop as below..... SET ROWCOUNT 1000; WHILE << some condition>> UPDATE <table> SET flag = 'Y'; commit; END WHILE; Effectively, this updates 1000 rows at a time so that we will not run into transaction log fillup issues. What is the best way of doing this in Informix ? Thanks in advance.
you could do this in spl:
WARNING UNTESTED CODE ahead....
ex:
create procedure dodummy()
define commit_cnt int;
set isolation to dirty read;
set lock mode to wait 10;let commit_cnt = 0;
-- no exception handling yet, simply wait for a lock if fails
-- rerun the spl.
begin work;
foreach mycur with hold
for
select somecol from sometable
let commit_cnt = commit_cnt + 1;
update yourtable set somecol = someval where current of mycur;
if mod(commit_cnt,1000) = 999 then
commit work;
begin work;
end if;
end foreach;
commit work;
end procedure;
which i guess is as fast as it is going to get/be in a logged db.
or make the table raw??? so stuff does not get logged.....
Or change the logging mode to no logging in case you have a
(un)bufferd logged database
Superboer
On 22 aug, 16:46, Veeru71 <m_ad...@hotmail.com> wrote:
> Is there any command in Informix that is equivalent to "SET ROWCOUNT"
> of Sybase/MS Sql Server ?
>
> Basically I am trying to update a column in a huge table (50 million
> rows) without a WHERE clause (need to set a flag in all the rows).
>
> In Sybase, I can do "SET ROWCOUNT 1000" and write a small WHILE loop
> as below.....
>
> SET ROWCOUNT 1000;
> WHILE << some condition>>
> UPDATE <table> SET flag = 'Y';
> commit;
> END WHILE;
>
> Effectively, this updates 1000 rows at a time so that we will not run
> into transaction log fillup issues.
> What is the best way of doing this in Informix ?
> Thanks in advance.