Re: esql/c Batch Updates

Art Kagel said: > 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: He did? Reading is essential. :op The original problem was that he has a text file full of updates he wants to apply. You can prepare an update statement, and I think that this is what he wants to achieve. > 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. > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Bye now, Obnoxio http://obotheclown.blogspot.com/

On Oct 17, 5:32 pm, "Obnoxio The Clown" <obno...@serendipita.com> wrote: > Art Kagel said: > > > 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: > > He did? Reading is essential. :op > > The original problem was that he has a text file full of updates he wants > to apply. You can prepare an update statement, and I think that this is > what he wants to achieve. > > > > > 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. > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > -- > Bye now, > Obnoxio > > http://obotheclown.blogspot.com/ Yip that was the intent of my question. I am trying to improve the loads times of some 4gl apps that use an "insert -> fails -> update" logic. I have been playing around with the load2 library (loadinc tool) from the iiug software repository with some good results. During my testing I noticed the library also has a load tool which has a cursor switch which makes a big difference in load times. I was wondering if I could do the same for updates too hence my post. On that note is feasible/recommend to create a load app that has this logic: bulk insert (or update depending on stats) via cursor -> fails - > processing failed batch without cursor? Our environment is such that we have base source data arriving for the same table (different columns) in overlapping time slots so there is no way that when your load starts you can determine update/insert buckets as during the load process another load may have started. The obvious solution would be to stage the data but that's out of my control, I'm purely trying to optimize load times within the current paradigm. Cheers guys and thank for our input Angus

<SNIP> OOO That's a different question! PLEASE everyone ask the question you want to ask and describe the problem you are having DO NOT present the solution you think you need and ask us how to make it work. You will get the wrong solution EVERY TIME! OK, insert -- failure -- update logic is sub-optimal if more than about 25-30% of the rows that need to be affected already exist. In that case it will be far faster to UPDATE -- no rows updated -- INSERT. This is because a failed update on a table with any indexes (and the more indexes the more this is true) has to insert the row, update all of the indexes then fail on the unique index, unique key, or primary key and rollback. An update of a non-existent row is detected immediately and cheaply in which case you can then insert the missing row. Just be careful! Updating a row that does not exist in NOT AN ERROR (except in an ANSI LOGGED database), so you have to check the number of rows affected (sqlca.sqlerrd[2] in ESQL/C or sqlca.sqlerrd[3] in 4GL) byt the update. If that is zero then the update "failed" and you have to insert the row. I've submitted a proposal to do a session on this very subject (Developing better IDS applications) at the next IIUG Conference. You - and every other Informix developer, DBA, and manager should plan to attend the Conference in Lenexa KS this year the week of April 26, 2009 - and not just to attend my sessions. There will be many IBM Informix developers and managers there so attendence will give you access to people and training that you cannot get any other way and much that you would have to pay much more for. Art > Yip that was the intent of my question. I am trying to improve the > loads times of some 4gl apps that use an "insert -> fails -> update" > logic. I have been playing around with the load2 library (loadinc > tool) from the iiug software repository with some good results. During > my testing I noticed the library also has a load tool which has a > cursor switch which makes a big difference in load times. I was > wondering if I could do the same for updates too hence my post. > > On that note is feasible/recommend to create a load app that has this > logic: bulk insert (or update depending on stats) via cursor -> fails - > > processing failed batch without cursor? Our environment is such that > we have base source data arriving for the same table (different > columns) in overlapping time slots so there is no way that when your > load starts you can determine update/insert buckets as during the load > process another load may have started. The obvious solution would be > to stage the data but that's out of my control, I'm purely trying to > optimize load times within the current paradigm. > > Cheers guys and thank for our input > Angus > > > _______________________________________________ > 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.

Art, I can't remember the last time c.d.i actually had a discussion on update strategies. This is really good stuff. Seems like this kind of mechanics is going to become more important to understand for big database operations where huge volumes of data are being worked on in a short amount of time. Do you or anyone else here have any comments regarding 'UPSERT' kinds of schemes? Just curious. See also: http://en.wikipedia.org/wiki/Upsert http://lists.mysql.com/mysql/159795 -- Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. Terry Pratchett Art Kagel wrote: > <SNIP> > > OOO That's a different question! PLEASE everyone ask the question you > want to ask and describe the problem you are having DO NOT present the > solution you think you need and ask us how to make it work. You will > get the wrong solution EVERY TIME! > > OK, insert -- failure -- update logic is sub-optimal if more than about > 25-30% of the rows that need to be affected already exist. In that case > it will be far faster to UPDATE -- no rows updated -- INSERT. This is > because a failed update on a table with any indexes (and the more > indexes the more this is true) has to insert the row, update all of the > indexes then fail on the unique index, unique key, or primary key and > rollback. An update of a non-existent row is detected immediately and > cheaply in which case you can then insert the missing row. > > Just be careful! Updating a row that does not exist in NOT AN ERROR > (except in an ANSI LOGGED database), so you have to check the number of > rows affected (sqlca.sqlerrd[2] in ESQL/C or sqlca.sqlerrd[3] in 4GL) > byt the update. If that is zero then the update "failed" and you have > to insert the row. > > I've submitted a proposal to do a session on this very subject > (Developing better IDS applications) at the next IIUG Conference. You - > and every other Informix developer, DBA, and manager should plan to > attend the Conference in Lenexa KS this year the week of April 26, 2009 > - and not just to attend my sessions. There will be many IBM Informix > developers and managers there so attendence will give you access to > people and training that you cannot get any other way and much that you > would have to pay much more for. > > Art > > > > Yip that was the intent of my question. I am trying to improve the > loads times of some 4gl apps that use an "insert -> fails -> update" > logic. I have been playing around with the load2 library (loadinc > tool) from the iiug software repository with some good results. During > my testing I noticed the library also has a load tool which has a > cursor switch which makes a big difference in load times. I was > wondering if I could do the same for updates too hence my post. > > On that note is feasible/recommend to create a load app that has this > logic: bulk insert (or update depending on stats) via cursor -> fails - > > processing failed batch without cursor? Our environment is such that > we have base source data arriving for the same table (different > columns) in overlapping time slots so there is no way that when your > load starts you can determine update/insert buckets as during the load > process another load may have started. The obvious solution would be > to stage the data but that's out of my control, I'm purely trying to > optimize load times within the current paradigm. > > Cheers guys and thank for our input > Angus > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org <mailto:Informix-list@iiug.org> > http://www.iiug.org/mailman/listinfo/informix-list > > > > > -- > Art S. Kagel > Oninit (www.oninit.com <http://www.oninit.com>) > IIUG Board of Directors (art@iiug.org <mailto: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. >

I have been trying to dig through some of my ancient 4GL programs to find the PUT logic used for batch updating, but haven't had any luck yet. I'm trying to get beyond the online SYNTAX guide and back to how you actually use a PUT statement to update in batches--but maybe I'm wrong and thinking of something else--it's been too long ago. I thought it was something along the lines of GET/PUT, where you GET a lot of data, then PUT it in blocks but maybe I'm dreaming. If I find something relevant I'll post it. -- Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. Terry Pratchett angusm wrote: > On Oct 17, 5:32 pm, "Obnoxio The Clown" <obno...@serendipita.com> > wrote: >> Art Kagel said: >> >>> 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: >> He did? Reading is essential. :op >> >> The original problem was that he has a text file full of updates he wants >> to apply. You can prepare an update statement, and I think that this is >> what he wants to achieve. >> >> >> >>> 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. >>> _______________________________________________ >>> Informix-list mailing list >>> Informix-l...@iiug.org >>> http://www.iiug.org/mailman/listinfo/informix-list >> -- >> Bye now, >> Obnoxio >> >> http://obotheclown.blogspot.com/ > > Yip that was the intent of my question. I am trying to improve the > loads times of some 4gl apps that use an "insert -> fails -> update" > logic. I have been playing around with the load2 library (loadinc > tool) from the iiug software repository with some good results. During > my testing I noticed the library also has a load tool which has a > cursor switch which makes a big difference in load times. I was > wondering if I could do the same for updates too hence my post. > > On that note is feasible/recommend to create a load app that has this > logic: bulk insert (or update depending on stats) via cursor -> fails - >> processing failed batch without cursor? Our environment is such that > we have base source data arriving for the same table (different > columns) in overlapping time slots so there is no way that when your > load starts you can determine update/insert buckets as during the load > process another load may have started. The obvious solution would be > to stage the data but that's out of my control, I'm purely trying to > optimize load times within the current paradigm. > > Cheers guys and thank for our input > Angus > >

On Oct 17, 7:28 pm, "Art Kagel" <art.ka...@gmail.com> wrote: > <SNIP> > > OOO That's a different question!  PLEASE everyone ask the question you want > to ask and describe the problem you are having DO NOT present the solution > you think you need and ask us how to make it work.  You will get the wrong > solution EVERY TIME! > > OK, insert -- failure -- update logic is sub-optimal if more than about > 25-30% of the rows that need to be affected already exist.  In that case it > will be far faster to UPDATE -- no rows updated -- INSERT.  This is because > a failed update on a table with any indexes (and the more indexes the more > this is true) has to insert the row, update all of the indexes then fail on > the unique index, unique key, or primary key and rollback.  An update of a > non-existent row is detected immediately and cheaply in which case you can > then insert the missing row. > > Just be careful!  Updating a row that does not exist in NOT AN ERROR (except > in an ANSI LOGGED database), so you have to check the number of rows > affected (sqlca.sqlerrd[2] in ESQL/C or sqlca.sqlerrd[3] in 4GL) byt the > update.  If that is zero then the update "failed" and you have to insert the > row. > > I've submitted a proposal to do a session on this very subject (Developing > better IDS applications) at the next IIUG Conference.  You - and every other > Informix developer, DBA, and manager should plan to attend the Conference in > Lenexa KS this year the week of April 26, 2009 - and not just to attend my > sessions.  There will be many IBM Informix developers and managers there so > attendence will give you access to people and training that you cannot get > any other way and much that you would have to pay much more for. > > Art > > > > > Yip that was the intent of my question. I am trying to improve the > > loads times of some 4gl apps that use an "insert -> fails -> update" > > logic. I have been playing around with the load2 library (loadinc > > tool) from the iiug software repository with some good results. During > > my testing I noticed the library also has a load tool which has a > > cursor switch which makes a big difference in load times. I was > > wondering if I could do the same for updates too hence my post. > > > On that note is feasible/recommend to create a load app that has this > > logic: bulk insert (or update depending on stats) via cursor -> fails - > > > processing failed batch without cursor? Our environment is such that > > we have base source data arriving for the same table (different > > columns) in overlapping time slots so there is no way that when your > > load starts you can determine update/insert buckets as during the load > > process another load may have started. The obvious solution would be > > to stage the data but that's out of my control, I'm purely trying to > > optimize load times within the current paradigm. > > > Cheers guys and thank for our input > > Angus > > > _______________________________________________ > > 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 again Art

InDeep wrote: > Art, > > I can't remember the last time c.d.i actually had a discussion on update > strategies. This is really good stuff. Seems like this kind of mechanics > is going to become more important to understand for big database operations > where huge volumes of data are being worked on in a short amount of time. > > Do you or anyone else here have any comments regarding 'UPSERT' kinds of > schemes? > It's already in DB2 called MERGE INTO, so maybe it will also pop up in IDS some day.