Re: INSERT cursors

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 Actually, Jonathan, if there is an error in one of the PUT rows the sqlca structure will report it when the block containing that row was flushed. If there is an error ONLY the row(s) successfully inserted before the row in error are successfully flushed. To recover you need to be able to backtrack to the row following the one in error and insert it again and perhaps log the data in error for manual recovery later. By the way, to further speed up your application, Carlson, you can increase the size of the FETCH/INSERT buffer by changing the global variable FetBufSize which defaults to 4096 and can be increased to 32767 (actually the closest multiple of the rowsize less than or equal to 32767 is the effective maximum if the rowsize is smaller than 32767). My dbcopy utility, flushing and commiting each row as it is PUT runs ~30% faster with a 32767byte FETCH buffer and FETCH array coding (another subject) because the FETCH side runs faster. However, if you enable the buffer-at-a-time FLUSHing option it runs 3X faster! You just have to put in sophisticated recovery coding to get these gains successfully and be able to recover from errors. BTW the latest dbcopy has incomplete recovery code and I am working on full recover code for it now. Art S. Kagel