Re: INSERT cursors

If you get problems with the insert cursor and using put another option is to use flush after every put. A long time ago I found that substantially faster than straight inserts and even executing a prepared insert. That was on SE and may not be applicable at all with IDS. I haven't tested this on IDS yet. We use it mostly when we need the value of a serial which isn't available after a put, only after the flush. On Fri, 21 Aug 1998 15:49:15 GMT, "Carlson@WHSmith" <carlson1@bellsouth.net> wrote: >Jonathan Leffler wrote: >> >> On Thu, 20 Aug 1998, Carlson@WHSmith wrote: >> > I've looked it up in DejaNews and I double-checked the manual [...] >> > I'm in the process of speeding up a very long batch job [...] >> > >> > I noticed that a certain function inserts rows into a given table. What >> > I determined was that an insert cursor might be a bit more effective. >> > So far (no surprise) I've found out that it's faster. >> > >> > The only problem is that . . . how can I verify that the number of rows >> > I PUT equals the number of rows I actually committed to the database? >> > For example, I know that I PUT 168 rows, and I can see that they were >> > committed, but how can my program verify this information as it runs? >> >> With some difficulty. The information is available in SQLCA.SQLERRD[3] >> after each PUT which actually flushes the buffer. The value in the >> field is zero after each PUT which does not flush the buffer, so you can >> simply sum the values as you go around the loop. The real fun occurs >> after the cursor is closed at the end. Of course, if one of the PUT >> statements fails, you can (if you're careful) determine which row it was >> that failed. I'm not clear about the status of the subsequent rows, but >> I assume that they are lost. Consequently, I prefer not to use insert >> cursors where there's a reasonable chance of failure, mainly because the >> error recovery is diabolical, especially in the absence of transactions. >> > >Here's the gist of my code . . . > >declare c_ins_cursor cursor with hold for > "insert into table_name (val1, val2, val3) values (?, ?, ?) > >................................................................... >open c_ins_cursor > >declare cursor with hold to get all store numbers >foreach store_num > compute_info_to_insert() > begin work > for i = 1 to some_number > put c_ins_cursor using pa_var1, pa_var2, pa_arrval1[i] > if sqlca.sqlcode < 0 then > display error message > set return code > exit for > end if > put c_ins_cursor using pa_var1, pa_var2, pa_arrval2[i] > if sqlca.sqlcode < 0 then > display error message > set return code > exit for > end if > end for > if return_code is OK > commit work > else > rollback work > exit foreach > end if >end foreach > >close c_ins_cursor >.................................................................... > >According to the manual, this would constitute an insert cursor, but >would this be a decent level of transaction protection? I'm committing >after each subset, so it appears that I'm safe. I'm checking sqlcode >after each PUT, so if something gets a bit flaky, I should be able to >catch it and rollback the transaction and executing a controlled abort >of the program. > >My understanding of the manual is that any uncommitted rows will be >written to the database on a COMMIT WORK or a CLOSE. Is this how it >works in real life? I attempted to check sqlca.sqlerrd[3] after the >COMMIT WORK, but nothing showed up. > >I guess if I need to do so, I could go back to straight inserts or maybe >a prepared insert. This way just looks so good, . . . . and it's fast. >I'll be manipulating almost 6 millions rows, so speed is a factor also. >It's just balancing speed with data integrity. > >. . . example code clipped . . . > > >Thanks all, > >John Carlson >Informix DBA >WH Smith, Inc. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)