Re: adding rowids to a frag'd table

Hey Guys, Thanks for the responses. You are doing well to have been out of it for a while! I'm in a trans so I can lock the table in excluse to avoid blowing the locks. It's a test system. There is little or no activity on the box. If the physical log is filling and forcing a checkpoint it shouldn't cause a long trans. Right? It should flush dirty pages back to disk, pull more pages in, etc. But, if the table is raw, why would/should it pull before images. It's raw, no logging, no rollback, etc. Isnt' that the point of raw? Is there a way to avoid blowing locks without the explicit trans? ======================== Darren Jacobs Sr Database Analyst Darren_Jacobs@carmax.com 804.747.0422 x3221 ======================== Andrew Clarke <aclarke@civica.c om.au> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: adding rowids to a frag'd table 11/03/2009 06:49 PM > Fernando wrote: > > > When the physical log hits 75% (very quickly if it's small) then it will > > force a checkpoint. If there are any open transactions from anyone on > > the engine, then .... now I'm starting to lose the plot. > > Wow... You must get the plot! :) > I swear, I haven't really touched an engine in earnest since version 9.3. I can probably legitimately tell the young whipper-snappers that I've forgotten more than they've learned.... /adjusts bifocals, braces and seat donut. > Having open transactions at checkpoint time is normal/usual/trivial/etc... > Yeah, but the bit I'm missing is what exactly will happen if the physical log is the choke-point. Dammit. > > I liked your previous post better :) > A LTX is a transaction that "has seen" LTXHWM % logs advance since it > started. A simple "begin work;" without anything else, will surely be > considered a long tx.... just give it enough time and some engine activity. > I'm trying, fuzzily, to picture how other sessions might be screwing with his process or vice-versa. But the physical log can easily recycle if the checkpoint succeeds. A little help here? What could be happening to him? PS - browsed your page/blog the other day. Nice collection of stuff. bookmarked! _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

On 4 Nov, 13:22, Darren_Jac...@carmax.com wrote: > Hey Guys, > > Thanks for the responses.  You are doing well to have been out of it for a > while! > > I'm in a trans so I can lock the table in excluse to avoid blowing the > locks. > > It's a test system.  There is little or no activity on the box.  If the > physical log is filling and forcing a checkpoint it shouldn't cause a long > trans.  Right?  It should flush dirty pages back to disk, pull more pages > in, etc.  But, if the table is raw, why would/should it pull before images. > It's raw, no logging, no rollback, etc.  Isnt' that the point of raw? > > Is there a way to avoid blowing locks without the explicit trans? > > ======================== > Darren Jacobs > Sr Database Analyst > Darren_Jac...@carmax.com > 804.747.0422 x3221 > ======================== > >              Andrew Clarke >              <acla...@civica.c >              om.au>                                                     To >              Sent by:                  informix-l...@iiug.org >              informix-list-bou                                          cc >              n...@iiug.org >                                                                    Subject >                                        Re: adding rowids to a frag'd table >              11/03/2009 06:49 >              PM > > > Fernando wrote: > > > > When the physical log hits 75% (very quickly if it's small) then it > will > > > force a checkpoint. If there are any open transactions from anyone on > > > the engine, then .... now I'm starting to lose the plot. > > > Wow... You must get the plot! :) > > I swear, I haven't really touched an engine in earnest since version 9.3. I > can probably legitimately tell the young whipper-snappers that I've > forgotten more than they've learned.... /adjusts bifocals, braces and seat > donut.> Having open transactions at checkpoint time is > > normal/usual/trivial/etc... > > Yeah, but the bit I'm missing is what exactly will happen if the physical > log is the choke-point. Dammit. > > > I liked your previous post better :) > > A LTX is a transaction that "has seen" LTXHWM % logs advance since it > > started. A simple "begin work;" without anything else, will surely be > > considered a long tx.... just give it enough time and some engine > activity. > > I'm trying, fuzzily, to picture how other sessions might be screwing with > his process or vice-versa. But the physical log can easily recycle if the > checkpoint succeeds. A little help here? What could be happening to him? > PS - browsed your page/blog the other day. Nice collection of stuff. > bookmarked! > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list If it is raw then I believe it should not be writing before images to the physical log. Use onstat -l to get the current position in the physical log and use oncheck to dump the pages and see what is there. Which version of the server are you running? FYI 9+ have onconfig parameter PLOG_OVERFLOW_PATH and if the physical log overflows on recovery it will overflow into there.

Darren_Jacobs@carmax.com wrote: > Hey Guys, > > Thanks for the responses. You are doing well to have been out of it for a > while! > > I'm in a trans so I can lock the table in excluse to avoid blowing the > locks. Oops... guilty... I forgot about that. > > It's a test system. There is little or no activity on the box. If the > physical log is filling and forcing a checkpoint it shouldn't cause a long > trans. Right? It should flush dirty pages back to disk, pull more pages > in, etc. But, if the table is raw, why would/should it pull before images. Forget about physical log... it will not cause a long tx. > It's raw, no logging, no rollback, etc. Isnt' that the point of raw? Yes but... Even on non-logging databases some actions must be logged. These are typically DDL statements... When you do some DML and something goes wrong you can have "half update" for instance (although you're giving up on ACID compliance of course). But when you're doing an alter table or a create index you can't have "half altered table" or "half index". The ADD ROWIDS is a slow alter, so that's probably the reason why you're having a long tx. But in order to be sure, please check the current log before the operation, the LTXHWM effective value, and the current log after the error. In order to get the effective value of LTXHWM run: dbaccess sysmaster <<EOF! SELECT * FROM syscfgtab WHERE cf_name = "LTXHWM";EOF! Assuming the engine is effectively consuming logical log space and as such is having a correct behavior you could do the following to solve the issue: 1) UNLOAD the table. Create a new one with ROWIDs and load the data into it using a raw table or periodic commits. 2) Create a new table with the correct definition and do an insert into select from ... 3)do number 1), but with HPL in paralell... this will be much quicker and will not consume logical logs if express mode is used... Regards. > > Is there a way to avoid blowing locks without the explicit trans? > > ======================== > Darren Jacobs > Sr Database Analyst > Darren_Jacobs@carmax.com > 804.747.0422 x3221 > ======================== > > > > Andrew Clarke > <aclarke@civica.c > om.au> To > Sent by: informix-list@iiug.org > informix-list-bou cc > nces@iiug.org > Subject > Re: adding rowids to a frag'd table > 11/03/2009 06:49 > PM > > > > > > > > >> Fernando wrote: >> >>> When the physical log hits 75% (very quickly if it's small) then it > will >>> force a checkpoint. If there are any open transactions from anyone on >>> the engine, then .... now I'm starting to lose the plot. >> Wow... You must get the plot! :) >> > I swear, I haven't really touched an engine in earnest since version 9.3. I > can probably legitimately tell the young whipper-snappers that I've > forgotten more than they've learned.... /adjusts bifocals, braces and seat > donut. >> Having open transactions at checkpoint time is > normal/usual/trivial/etc... > Yeah, but the bit I'm missing is what exactly will happen if the physical > log is the choke-point. Dammit. >> I liked your previous post better :) >> A LTX is a transaction that "has seen" LTXHWM % logs advance since it >> started. A simple "begin work;" without anything else, will surely be >> considered a long tx.... just give it enough time and some engine > activity. > I'm trying, fuzzily, to picture how other sessions might be screwing with > his process or vice-versa. But the physical log can easily recycle if the > checkpoint succeeds. A little help here? What could be happening to him? > PS - browsed your page/blog the other day. Nice collection of stuff. > bookmarked! > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > > >

> > Yes but... > Even on non-logging databases some actions must be logged. These are > typically DDL statements... When you do some DML and something goes > wrong you can have "half update" for instance (although you're giving up > on ACID compliance of course). But when you're doing an alter table or a > create index you can't have "half altered table" or "half index".> zing! that's what I was struggling to arrive at. It's got to be it.