insert cursor
Paul Tilles wanted to speed up a row-by-row insert loop (which handles -239/-268 duplicate errors with an update) by using a buffered insert cursor, and asked whether Informix checks integrity on buffered rows. Replies clarified that no checking happens at PUT time; constraints are enforced when the buffer is flushed, with sqlca.sqlcode reporting the error and sqlca.sqlerrd[2] the number of rows successfully inserted. To use an insert cursor you must cache PUT rows, do explicit FLUSHes, handle the failing row, and re-PUT the rest (see Kagel's dbcopy.ec); alternatively use START/STOP VIOLATIONS TABLE and post-process it. Both Leffler and Kagel judged the effort rarely worthwhile versus singleton inserts.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I currently have an application which attempts to insert one row at a time into a table and then checks the return code. If the return code = -239 or -268, then I do an update of one field in the record. I would like to speed up the insert process by using the buffering capabilities of an insert cursor. When writing records to the buffer, does Informix check the "integrity" of records already in the buffer for such situations as duplicate records? Also, does Informix do any "integrity" checking against records already written to the table? I am running Version 7.30UC2. TIA Paul Tilles
Paul Tilles wrote: > I currently have an application which attempts to insert one row at a > time into a table and then checks the return code. If the return code = > -239 or -268, then I do an update of one field in the record. Sounds a bit like SQLUPLOAD, distributed (as alpha code) with SQLCMD. > I would like to speed up the insert process by using the buffering > capabilities of an insert cursor. > > When writing records to the buffer, does Informix check the "integrity" > of records already in the buffer for such situations as duplicate > records? Also, does Informix do any "integrity" checking against > records already written to the table? No. And No. > I am running Version 7.30UC2. Since you are expecting errors from your inserts, I don't think that using the INSERT cursor is a good idea. The only way to manage it would be to do keep tabs on all the records that have not yet been flushed so that when the flush occurs and detects an error, your code can deal with: * Some records were inserted OK. * One record failed and so needs to be updated. * The subsequent records were flushed but not processed and so need to be PUT again and flushed again. You probably want to avoid implicit flushes and ensure that the flushes occur strictly under program control. Speaking personally, I think that's sufficiently much harder than using the singleton inserts that I would have to be convinced that it really was worth spending the time coding and debugging the non-trivial code. -- Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"
Jonathan Leffler wrote: > > Paul Tilles wrote: > > I currently have an application which attempts to insert one row at a > > time into a table and then checks the return code. If the return code = > > -239 or -268, then I do an update of one field in the record. > > Sounds a bit like SQLUPLOAD, distributed (as alpha code) with SQLCMD. > > > I would like to speed up the insert process by using the buffering > > capabilities of an insert cursor. > > > > When writing records to the buffer, does Informix check the "integrity" > > of records already in the buffer for such situations as duplicate > > records? Also, does Informix do any "integrity" checking against > > records already written to the table? > > No. And No. > > > I am running Version 7.30UC2. > > Since you are expecting errors from your inserts, I don't think that > using the INSERT cursor is a good idea. The only way to manage it would > be to do keep tabs on all the records that have not yet been flushed so > that when the flush occurs and detects an error, your code can deal > with: > > * Some records were inserted OK. > * One record failed and so needs to be updated. > * The subsequent records were flushed but not processed > and so need to be PUT again and flushed again. > > You probably want to avoid implicit flushes and ensure that the flushes > occur strictly under program control. > > Speaking personally, I think that's sufficiently much harder than using > the singleton inserts that I would have to be convinced that it really > was worth spending the time coding and debugging the non-trivial code. And I've forgotten to mention violations tables again. This would use a different processing scheme -- you'd create an empty violations table (START VIOLATIONS TABLE), you'd stuff everything into the database via the insert cursor, use STOP VIOLATIONS TABLE for the table you're working on, and then post-process the now non-empty violations table to do the updates. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"
God, I hope so! Paul Tilles wrote: > I currently have an application which attempts to insert one row at a > time into a table and then checks the return code. If the return code = > -239 or -268, then I do an update of one field in the record. > > I would like to speed up the insert process by using the buffering > capabilities of an insert cursor. > > When writing records to the buffer, does Informix check the "integrity" > of records already in the buffer for such situations as duplicate > records? Also, does Informix do any "integrity" checking against > records already written to the table? > > I am running Version 7.30UC2. > > TIA > > Paul Tilles
Hi, please see my comments below. Jonathan Leffler wrote: > Paul Tilles wrote: > > I currently have an application which attempts to insert one row at a > > time into a table and then checks the return code. If the return code = > > -239 or -268, then I do an update of one field in the record. > > Sounds a bit like SQLUPLOAD, distributed (as alpha code) with SQLCMD. > > > I would like to speed up the insert process by using the buffering > > capabilities of an insert cursor. > > > > When writing records to the buffer, does Informix check the "integrity" > > of records already in the buffer for such situations as duplicate > > records? Also, does Informix do any "integrity" checking against > > records already written to the table? > > No. And No. Yes. And I don't know. When insert cursors are used the engine performs the same integrity checks that it performs for single row inserts. Let's take an example: You perform 10 PUT operations and then a FLUSH. The row you try to insert with the 5th PUT is actually a duplicate of a row already existing in the database (as defined by some unique index or primary constraint). The FLUSH will return with sqlcode -239 and sqlca.sqlerrd[2] contains the number of successfully inserted rows (4 in the example). Now the application could repeat PUT operations 6 to 10 to try to insert the remaining rows (which would involve some - not too complicated) buffering scheme. For completeness it should be noted that not only a FLUSH but also PUT operations can return above error codes (that is when the PUT operation needs to flush buffers to the database to free memory space for the next PUT operation). Again, sqlcode would indicate success of the overall operation and sqlerrd[2] indicates how many rows have been successfully processed. As for integrity checking, I don't know what you are actually referring to. Again, the database backend handles inserts through insert cursors not any different than singleton inserts. Any integrity checks performed for a single row insert would be performed for rows inserted as part of a insert cursor as well. > > I am running Version 7.30UC2. > > Since you are expecting errors from your inserts, I don't think that > using the INSERT cursor is a good idea. The only way to manage it would > be to do keep tabs on all the records that have not yet been flushed so > that when the flush occurs and detects an error, your code can deal > with: > > * Some records were inserted OK. > * One record failed and so needs to be updated. > * The subsequent records were flushed but not processed > and so need to be PUT again and flushed again. You are indicating here that integrity checks are actually performed. So your "No" above is a little bit misleading. Anyway, hope this helps, Heiko > > > You probably want to avoid implicit flushes and ensure that the flushes > occur strictly under program control. > > Speaking personally, I think that's sufficiently much harder than using > the singleton inserts that I would have to be convinced that it really > was worth spending the time coding and debugging the non-trivial code. > > -- > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!"
Paul Tilles wrote: > > I currently have an application which attempts to insert one row at a > time into a table and then checks the return code. If the return code = > -239 or -268, then I do an update of one field in the record. > > I would like to speed up the insert process by using the buffering > capabilities of an insert cursor. > > When writing records to the buffer, does Informix check the "integrity" > of records already in the buffer for such situations as duplicate > records? Also, does Informix do any "integrity" checking against > records already written to the table? The checking only happens at FLUSH time not at PUT time. What you have to do is cache the records you are putting and when a FLUSH occurs (implicit FLUSH is detectable by watching the sqlca.sqlcode and sqlca.sqlerrd fields to see if there are more than zero rows inserted or zero rows but an error, either condition indicates that the buffer was flushed as errors cannot occur while PUTting to the buffer). It is best, as Jonathan points out, to keep track of how many rows you have PUT and their total size and do an explicit FLUSH before the buffer fills and forces an implicit FLUSH, this way you KNOW when a FLUSH has occurred. Then you check the sqlca.sqlcode for errors and if one has occurred look back throught the cache for the row after the last one successfully inserted (the number of rows inserted prior to the error is returned in the sqlca structure) and handle it but updating or logging more serious errors, then re-PUT all the rows following it in the cache, then go back to processing input until the next FLUSH. Look at the code for my dbcopy.ec utility in utils2_ak for a rather robust (if I do say so myself) implementation of this scheme. However, as Jonathan says it is rarely worth the effort. Art S. Kagel > I am running Version 7.30UC2. > > TIA > > Paul Tilles
"Art S. Kagel" wrote: > Paul Tilles wrote: > > > > I currently have an application which attempts to insert one row at a > > time into a table and then checks the return code. If the return code = > > -239 or -268, then I do an update of one field in the record. > > > > I would like to speed up the insert process by using the buffering > > capabilities of an insert cursor. > > > > When writing records to the buffer, does Informix check the "integrity" > > of records already in the buffer for such situations as duplicate > > records? Also, does Informix do any "integrity" checking against > > records already written to the table? > > The checking only happens at FLUSH time not at PUT time. What you > have to do is cache the records you are putting and when a FLUSH > occurs (implicit FLUSH is detectable by watching the sqlca.sqlcode and > sqlca.sqlerrd fields to see if there are more than zero rows inserted > or zero rows but an error, either condition indicates that the buffer > was flushed as errors cannot occur while PUTting to the buffer). It > is best, as Jonathan points out, to keep track of how many rows you > have PUT and their total size and do an explicit FLUSH before the > buffer fills and forces an implicit FLUSH, this way you KNOW when a > FLUSH has occurred. Each PUT returns information if an implicit FLUSH operation occurred. I.e. for a PUT with an implicit flush (due to buffer full condition) sqlca.sqlcode will indicate if there was any problem and sqlca.sqlerrd[2] will indicate how many rows have been successfully flushed. Hope this helps, Heiko > Then you check the sqlca.sqlcode for errors and > if one has occurred look back throught the cache for the row after the > last one successfully inserted (the number of rows inserted prior to > the error is returned in the sqlca structure) and handle it but > updating or logging more serious errors, then re-PUT all the rows > following it in the cache, then go back to processing input until the > next FLUSH. Look at the code for my dbcopy.ec utility in utils2_ak > for a rather robust (if I do say so myself) implementation of this > scheme. However, as Jonathan says it is rarely worth the effort. > > Art S. Kagel > > > I am running Version 7.30UC2. > > > > TIA > > > > Paul Tilles