esql/c Batch Updates
The poster asked whether ESQL/C (Informix 10) supports an "update cursor" analogous to an insert cursor, so a large set of row-specific updates driven by a flat file could run as fast as buffered inserts. One suggestion to DECLARE a cursor directly on an UPDATE statement was corrected by Art Kagel: that isn't valid since UPDATE isn't iterative. The accepted approach is DECLARE CURSOR FOR SELECT ... FOR UPDATE OF <columns>, then FETCH each row, read the new values from the file, and issue UPDATE ... WHERE CURRENT OF <cursor>, which avoids re-searching for the row. For very large volumes, use a cursor WITH HOLD and commit every N rows. Sample ESQL/C code was posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Is there a way to do batch updates almost like an update cursor in esql/c? (we are on Informx v10) Cheers Angus
On Oct 17, 7:52 am, angusm <all4mil...@gmail.com> wrote: > Hi, > > Is there a way to do batch updates almost like an update cursor in > esql/c? (we are on Informx v10) > > Cheers > Angus Huh? Define what you mean by batch updates. But the answer could be yes.
angusm said: > Hi, > > Is there a way to do batch updates almost like an update cursor in > esql/c? (we are on Informx v10) Yes. Or possibly no. What are you asking? -- Bye now, Obnoxio http://obotheclown.blogspot.com/
On Oct 17, 3:05 pm, "Obnoxio The Clown" <obno...@serendipita.com> wrote: > angusm said: > > > Hi, > > > Is there a way to do batch updates almost like an update cursor in > > esql/c? (we are on Informx v10) > > Yes. Or possibly no. > > What are you asking? > > -- > Bye now, > Obnoxio > > http://obotheclown.blogspot.com/ Hi Guys I want to be able to get the same performance when doing updates as I do when using an insert cursor doing inserts. But I can find any docs on the existance of update cursor (when I say update cursor I mean same as insert cursor only doing udpates). I need to do a whole bunch of updates from data in a text file Cheers
If you're doing like updates, you can prepare a statment and a cursor. Declare the Cursor for Updates ? (See the SQL syntax) RFTM? > From: all4miller@gmail.com > Subject: Re: esql/c Batch Updates > Date: Fri, 17 Oct 2008 06:11:39 -0700 > To: informix-list@iiug.org > > On Oct 17, 3:05 pm, "Obnoxio The Clown" <obno...@serendipita.com> > wrote: > > angusm said: > > > > > Hi, > > > > > Is there a way to do batch updates almost like an update cursor in > > > esql/c? (we are on Informx v10) > > > > Yes. Or possibly no. > > > > What are you asking? > > > > -- > > Bye now, > > Obnoxio > > > > http://obotheclown.blogspot.com/ > > Hi Guys > > I want to be able to get the same performance when doing updates as I > do when using an insert cursor doing inserts. But I can find any docs > on the existance of update cursor (when I say update cursor I mean > same as insert cursor only doing udpates). I need to do a whole bunch > of updates from data in a text file > > Cheers > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Store, manage and share up to 5GB with Windows Live SkyDrive. http://skydrive.live.com/welcome.aspx?provision=1?ocid=TXT_TAGLM_WL_skydrive_102008
On Oct 17, 3:17 pm, Ian Michael Gumby <im_gu...@hotmail.com> wrote:
> If you're doing like updates, you can prepare a statment and a cursor.
>
> Declare the Cursor for Updates ? (See the SQL syntax)
> RFTM?
>
>
>
> > From: all4mil...@gmail.com
> > Subject: Re: esql/c Batch Updates
> > Date: Fri, 17 Oct 2008 06:11:39 -0700
> > To: informix-l...@iiug.org
>
> > On Oct 17, 3:05 pm, "Obnoxio The Clown" <obno...@serendipita.com>
> > wrote:
> > > angusm said:
>
> > > > Hi,
>
> > > > Is there a way to do batch updates almost like an update cursor in
> > > > esql/c? (we are on Informx v10)
>
> > > Yes. Or possibly no.
>
> > > What are you asking?
>
> > > --
> > > Bye now,
> > > Obnoxio
>
> > >http://obotheclown.blogspot.com/
>
> > Hi Guys
>
> > I want to be able to get the same performance when doing updates as I
> > do when using an insert cursor doing inserts. But I can find any docs
> > on the existance of update cursor (when I say update cursor I mean
> > same as insert cursor only doing udpates). I need to do a whole bunch
> > of updates from data in a text file
>
> > Cheers
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
>
> _________________________________________________________________
> Store, manage and share up to 5GB with Windows Live SkyDrive.http://skydrive.live.com/welcome.aspx?provision=1?ocid=TXT_TAGLM_WL_s...
If I read and understood the docs correctly you declare an update
cursor as a "declare ... select .... where ... for update". So
basically you are fetching all the records that match your select
statement and then you can scroll through them and update/delete etc
right....? How do I retro fit that to generate updates from a flat
file as every row has different values?
What Im trying to find out is if its possible to us the construct
below but for updates?
EXEC SQL declare ins_cur cursor for
insert into stock values
(:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);
angusm said: > On Oct 17, 3:05 pm, "Obnoxio The Clown" <obno...@serendipita.com> > wrote: >> angusm said: >> >> > Hi, >> >> > Is there a way to do batch updates almost like an update cursor in >> > esql/c? (we are on Informx v10) >> >> Yes. Or possibly no. > I want to be able to get the same performance when doing updates as I > do when using an insert cursor doing inserts. But I can find any docs > on the existance of update cursor (when I say update cursor I mean > same as insert cursor only doing udpates). I need to do a whole bunch > of updates from data in a text file You certainly can create an UPDATE cursor. It's in TFM. -- Bye now, Obnoxio http://obotheclown.blogspot.com/
On Oct 17, 3:55 pm, "Obnoxio The Clown" <obno...@serendipita.com> wrote: > angusm said: > > > On Oct 17, 3:05 pm, "Obnoxio The Clown" <obno...@serendipita.com> > > wrote: > >> angusm said: > > >> > Hi, > > >> > Is there a way to do batch updates almost like an update cursor in > >> > esql/c? (we are on Informx v10) > > >> Yes. Or possibly no. > > I want to be able to get the same performance when doing updates as I > > do when using an insert cursor doing inserts. But I can find any docs > > on the existance of update cursor (when I say update cursor I mean > > same as insert cursor only doing udpates). I need to do a whole bunch > > of updates from data in a text file > > You certainly can create an UPDATE cursor. It's in TFM. > > -- > Bye now, > Obnoxio > > http://obotheclown.blogspot.com/ well thats me told... rough day guys
angusm said:
> If I read and understood the docs correctly you declare an update
> cursor as a "declare ... select .... where ... for update". So
> basically you are fetching all the records that match your select
> statement and then you can scroll through them and update/delete etc
> right....? How do I retro fit that to generate updates from a flat
> file as every row has different values?
>
> What Im trying to find out is if its possible to us the construct
> below but for updates?
>
> EXEC SQL declare ins_cur cursor for
> insert into stock values
> (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);
EXEC SQL declare upd_cursor for
update stock_values SET f1 = :f1 where f2 = :f2;
??
--
Bye now,
Obnoxio
http://obotheclown.blogspot.com/
On Oct 17, 3:59 pm, "Obnoxio The Clown" <obno...@serendipita.com>
wrote:
> angusm said:
>
> > If I read and understood the docs correctly you declare an update
> > cursor as a "declare ... select .... where ... for update". So
> > basically you are fetching all the records that match your select
> > statement and then you can scroll through them and update/delete etc
> > right....? How do I retro fit that to generate updates from a flat
> > file as every row has different values?
>
> > What Im trying to find out is if its possible to us the construct
> > below but for updates?
>
> > EXEC SQL declare ins_cur cursor for
> > insert into stock values
> > (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);>
> EXEC SQL declare upd_cursor for
> update stock_values SET f1 = :f1 where f2 = :f2;>
> ??
>
> --
> Bye now,
> Obnoxio
>
> http://obotheclown.blogspot.com/
Thanks! been googling for a while now but never came across that sorry
to waste your time ;)
Don't thank the Clown yet, that's not right. You can't open a cursor
against an UPDATE statement like that because it is not iterative. If it
were allowed and worked the statement below would update every row of the
table where t2 = :t2 to the single value of the variable f2 at the time the
cursor is opened.
Gumby got it right! You:
EXEC SQL DECLARE updater CURSOR FOR
SELECT stock_no, manu_code
FROM stock_values
WHERE ....
FOR UPDATE OF price, description;
EXEC SQL BEGIN WORK;
EXEC SQL OPEN updater USING ....;
while (sqlca.sqlcode == 0) {
EXEC SQL FETCH updater INTO :stock_no, :manu_code;
if (sqlca.sqlcode != 0) break;
<Get update values from file>
EXEC SQL UPDATE stock_values
SET price = :new_price, description = :new_desc
WHERE CURRENT OF updater;
}
if (sqlca.sqlcode < 0) {
.....<Error handling>
EXEC SQL ROLLBACK WORK;
} e;se
EXEC SQL COMMIT WORK;
...
The UPDATE ... WHERE CURRENT OF <cursorname> (see TFM) updates the current
cursor row directly without having to search for it again. This is the
fastest way to update many rows with individualized values.
If you are updating a large number of rows look up CURSOR .... WITH HOLD and
commit every N updates.
Art
On Fri, Oct 17, 2008 at 10:02 AM, angusm <all4miller@gmail.com> wrote:
> On Oct 17, 3:59 pm, "Obnoxio The Clown" <obno...@serendipita.com>
> wrote:
> > angusm said:
> >
> > > If I read and understood the docs correctly you declare an update
> > > cursor as a "declare ... select .... where ... for update". So
> > > basically you are fetching all the records that match your select
> > > statement and then you can scroll through them and update/delete etc
> > > right....? How do I retro fit that to generate updates from a flat
> > > file as every row has different values?
> >
> > > What Im trying to find out is if its possible to us the construct
> > > below but for updates?
> >
> > > EXEC SQL declare ins_cur cursor for
> > > insert into stock values
> > > (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);> >
> > EXEC SQL declare upd_cursor for
> > update stock_values SET f1 = :f1 where f2 = :f2;> >
> > ??
> >
> > --
> > Bye now,
> > Obnoxio
> >
> > http://obotheclown.blogspot.com/
>
> Thanks! been googling for a while now but never came across that sorry
> to waste your time ;)
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Oct 17, 4:55 pm, "Art Kagel" <art.ka...@gmail.com> wrote:
> Don't thank the Clown yet, that's not right. You can't open a cursor
> against an UPDATE statement like that because it is not iterative. If it
> were allowed and worked the statement below would update every row of the
> table where t2 = :t2 to the single value of the variable f2 at the time the
> cursor is opened.
>
> Gumby got it right! You:
>
> EXEC SQL DECLARE updater CURSOR FOR
> SELECT stock_no, manu_code
> FROM stock_values
> WHERE ....> FOR UPDATE OF price, description;
>
> EXEC SQL BEGIN WORK;
> EXEC SQL OPEN updater USING ....;
>
> while (sqlca.sqlcode == 0) {
> EXEC SQL FETCH updater INTO :stock_no, :manu_code;
> if (sqlca.sqlcode != 0) break;
>
> <Get update values from file>
>
> EXEC SQL UPDATE stock_values
> SET price = :new_price, description = :new_desc
> WHERE CURRENT OF updater;}
>
> if (sqlca.sqlcode < 0) {
> .....<Error handling>
> EXEC SQL ROLLBACK WORK;} e;se
>
> EXEC SQL COMMIT WORK;
> ...
>
> The UPDATE ... WHERE CURRENT OF <cursorname> (see TFM) updates the current
> cursor row directly without having to search for it again. This is the
> fastest way to update many rows with individualized values.
>
> If you are updating a large number of rows look up CURSOR .... WITH HOLD and
> commit every N updates.
>
> Art
>
>
>
> On Fri, Oct 17, 2008 at 10:02 AM, angusm <all4mil...@gmail.com> wrote:
> > On Oct 17, 3:59 pm, "Obnoxio The Clown" <obno...@serendipita.com>
> > wrote:
> > > angusm said:
>
> > > > If I read and understood the docs correctly you declare an update
> > > > cursor as a "declare ... select .... where ... for update". So
> > > > basically you are fetching all the records that match your select
> > > > statement and then you can scroll through them and update/delete etc
> > > > right....? How do I retro fit that to generate updates from a flat
> > > > file as every row has different values?
>
> > > > What Im trying to find out is if its possible to us the construct
> > > > below but for updates?
>
> > > > EXEC SQL declare ins_cur cursor for
> > > > insert into stock values
> > > > (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);>
> > > EXEC SQL declare upd_cursor for
> > > update stock_values SET f1 = :f1 where f2 = :f2;>
> > > ??
>
> > > --
> > > Bye now,
> > > Obnoxio
>
> > >http://obotheclown.blogspot.com/
>
> > Thanks! been googling for a while now but never came across that sorry
> > to waste your time ;)
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (a...@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any entity
> with which I am affiliated nor those of the entities themselves.
Thanks Art for your detailed example much appreciated!
Google?
Why don't you just pick up a pdf of the manuals off the IBM site?
Makes life a lot easier.
> From: all4miller@gmail.com
> Subject: Re: esql/c Batch Updates
> Date: Fri, 17 Oct 2008 07:02:10 -0700
> To: informix-list@iiug.org
>
> On Oct 17, 3:59 pm, "Obnoxio The Clown" <obno...@serendipita.com>
> wrote:
> > angusm said:
> >
> > > If I read and understood the docs correctly you declare an update
> > > cursor as a "declare ... select .... where ... for update". So
> > > basically you are fetching all the records that match your select
> > > statement and then you can scroll through them and update/delete etc
> > > right....? How do I retro fit that to generate updates from a flat
> > > file as every row has different values?
> >
> > > What Im trying to find out is if its possible to us the construct
> > > below but for updates?
> >
> > > EXEC SQL declare ins_cur cursor for
> > > insert into stock values
> > > (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);> >
> > EXEC SQL declare upd_cursor for
> > update stock_values SET f1 = :f1 where f2 = :f2;> >
> > ??
> >
> > --
> > Bye now,
> > Obnoxio
> >
> > http://obotheclown.blogspot.com/
>
> Thanks! been googling for a while now but never came across that sorry
> to waste your time ;)
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
_________________________________________________________________
You live life beyond your PC. So now Windows goes beyond your PC.
http://clk.atdmt.com/MRT/go/115298556/direct/01/
On Oct 17, 7:29 pm, Ian Michael Gumby <im_gu...@hotmail.com> wrote:
> Google?
>
> Why don't you just pick up a pdf of the manuals off the IBM site?
> Makes life a lot easier.
>
>
>
> > From: all4mil...@gmail.com
> > Subject: Re: esql/c Batch Updates
> > Date: Fri, 17 Oct 2008 07:02:10 -0700
> > To: informix-l...@iiug.org
>
> > On Oct 17, 3:59 pm, "Obnoxio The Clown" <obno...@serendipita.com>
> > wrote:
> > > angusm said:
>
> > > > If I read and understood the docs correctly you declare an update
> > > > cursor as a "declare ... select .... where ... for update". So
> > > > basically you are fetching all the records that match your select
> > > > statement and then you can scroll through them and update/delete etc
> > > > right....? How do I retro fit that to generate updates from a flat
> > > > file as every row has different values?
>
> > > > What Im trying to find out is if its possible to us the construct
> > > > below but for updates?
>
> > > > EXEC SQL declare ins_cur cursor for
> > > > insert into stock values
> > > > (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);>
> > > EXEC SQL declare upd_cursor for
> > > update stock_values SET f1 = :f1 where f2 = :f2;>
> > > ??
>
> > > --
> > > Bye now,
> > > Obnoxio
>
> > >http://obotheclown.blogspot.com/
>
> > Thanks! been googling for a while now but never came across that sorry
> > to waste your time ;)
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
>
> _________________________________________________________________
> You live life beyond your PC. So now Windows goes beyond your PC.http://clk.atdmt.com/MRT/go/115298556/direct/01/
gonna do that now