Insert Cursor in a Transaction

Hi All, I'm using an insert cursor within a transaction and am wondering how will this affect the Logical Log. The Informix manual says Insert cursor will write the data to the disk when either the buffer is full or a FLUSH is issued but how does this "insert buffer" differ from the Logical log/buffer? Can I avoid filling up the logical space in a long transaction if I flush the insert buffer regularly? Or Commit is the only way to free the logical log? Please advise. TIA, -- Vince.

AFAICS, the part about "write the data to disk" is a documentation error in the Informix manual. The Insert Cursor buffer is a session-specific buffer, unlike the logical log buffer which is Instance-specific. It can be used to defer writes to the Database (and thus, to the logical log & Database buffers). So, rather than have communication between the session and the database server with every insert, one can pile up a set of rows in the Insert cursor and pass them to the DBServer in a single communication. Logical log records will be written for every row inserted in the (logged) Database, irrespective of the use of an Insert cursor. However, they will be written later (when the Insert cursor is flushed) rather than sooner. Long transactions will not be affected at all as they have nothing to do with buffering; they only depend on how much log space the open transaction spans. Yes, frequent COMMITs is the way to reduce the possibility of a long transaction. Rudy Vincent Ng wrote: > Hi All, > > I'm using an insert cursor within a transaction and am wondering how > will this affect the Logical Log. > The Informix manual says Insert cursor will write the data to the disk > when either the buffer is full or a FLUSH is issued but how does this > "insert buffer" differ from the Logical log/buffer? > > Can I avoid filling up the logical space in a long transaction if I > flush the insert buffer regularly? Or Commit is the only way to free > the logical log? > > Please advise. > > TIA, > -- > Vince.

Vincent Ng wrote: > I'm using an insert cursor within a transaction and am wondering how > will this affect the Logical Log. In the same way that using INSERT statements affect the logical log. When the data in the cursor is sent to the server, the inserts will all be recorded in the logical log. > The Informix manual says Insert cursor will write the data to the disk > when either the buffer is full or a FLUSH is issued That's correct. > but how does this > "insert buffer" differ from the Logical log/buffer? In just about every conceivable way. The insert buffer is in your application; the logical log buffer is in the database. And the two buffers have completely unrelated purposes: the insert cursor buffer cuts down on the number of messages sent between a single application and the database; the logical log buffer is a common resource used by all applications in all databases served by the instance of IDS. Pretty much everything else is a consequence of these rather enormous differences. > Can I avoid filling up the logical space in a long transaction if I > flush the insert buffer regularly? No. The LTX (long transaction) occurs if the gap between BEGIN WORK and COMMIT WORK is too long, regardless of what you do with your insert cursor. > Or Commit is the only way to free the logical log? Well, COMMIT is an important part of the process; you also have to consider the logical log backup and so on. > Please advise. > > TIA, > -- > Vince. -- 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!"

I'd like to say thank's to Rudy and Jonathan for their comments and explanations which have clarified most of my doubts. Thank's, Vincent. Jonathan Leffler wrote: > Vincent Ng wrote: > > I'm using an insert cursor within a transaction and am wondering how > > will this affect the Logical Log. > > In the same way that using INSERT statements affect the logical log. > When the data in the cursor is sent to the server, the inserts will all > be recorded in the logical log. > > > The Informix manual says Insert cursor will write the data to the disk > > when either the buffer is full or a FLUSH is issued > > That's correct. > > > but how does this > > "insert buffer" differ from the Logical log/buffer? > > In just about every conceivable way. > > The insert buffer is in your application; the logical log buffer is in > the database. And the two buffers have completely unrelated purposes: > the insert cursor buffer cuts down on the number of messages sent > between a single application and the database; the logical log buffer is > a common resource used by all applications in all databases served by > the instance of IDS. > > Pretty much everything else is a consequence of these rather enormous > differences. > > > Can I avoid filling up the logical space in a long transaction if I > > flush the insert buffer regularly? > > No. The LTX (long transaction) occurs if the gap between BEGIN WORK and > COMMIT WORK is too long, regardless of what you do with your insert > cursor. > > > Or Commit is the only way to free the logical log? > > Well, COMMIT is an important part of the process; you also have to > consider the logical log backup and so on. > > > Please advise. > > > > TIA, > > -- > > Vince. > > -- > 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!" -- Vincent Ng - vng@crtc.corp.mot.com vng@ieee.org