Re: adding rowids to a frag'd table

Fernando, The process went through 41 logs prior to the rollback. I have 114 logs with ltxhwm set to 45. It should have made it another 10 logs before blowing! Anyway, it appears I have not other choice but to unload, drop, recreate w/rowids, and reload. Seems there need to be a way to lock a raw table outside of a transaction to avoid using logical logs. Thanks to all for the responses ======================== Darren Jacobs Sr Database Analyst Darren_Jacobs@carmax.com 804.747.0422 x3221 ======================== Fernando Nunes <domusonline@gmai l.com> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: adding rowids to a frag'd table 11/04/2009 05:50 PM 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 > > > > _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Darren_Jacobs@carmax.com wrote: > Fernando, > > The process went through 41 logs prior to the rollback. I have 114 logs > with ltxhwm set to 45. It should have made it another 10 logs before > blowing! Anyway, it appears I have not other choice but to unload, drop, > recreate w/rowids, and reload. 114 * 0.45 = 51.3 (unless my Windows calculator is buggy ;) ) In any case that would be a little more than what you've got... Which makes me go back to theBP question: are all your logical logs the same size? > Seems there need to be a way to lock a raw table outside of a transaction > to avoid using logical logs. I can't think of a solution to your situation outside what I mentioned before. But keep in mind that most of the times you would: BEGIN WORK; LOCK TABLE ... IN EXCLUSIVE MODE do some *DML* -- which would not consume logs COMMIT WORK; So, in most cases you'd not face the issue... Regards.

OP has to accept that rewriting a table is logged. Table rebuilds end up consuming new space. Creating a raw table of the requisite shape and then pumping rows into it is probably the only way to achieve this. He's expecting too much of RAW

I've given up on the alter. I've created a new tbl w/rowids, insert into select from, created the indexes in about 45 min. Unless a way exists to lock the table outside of a transaction this is the way to go. Thanks again for your feedback and advice. peace ======================== Darren Jacobs Sr Database Analyst Darren_Jacobs@carmax.com 804.747.0422 x3221 ======================== Fernando Nunes <domusonline@gmai l.com> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: adding rowids to a frag'd table 11/05/2009 06:15 PM Darren_Jacobs@carmax.com wrote: > Fernando, > > The process went through 41 logs prior to the rollback. I have 114 logs > with ltxhwm set to 45. It should have made it another 10 logs before > blowing! Anyway, it appears I have not other choice but to unload, drop, > recreate w/rowids, and reload. 114 * 0.45 = 51.3 (unless my Windows calculator is buggy ;) ) In any case that would be a little more than what you've got... Which makes me go back to theBP question: are all your logical logs the same size? > Seems there need to be a way to lock a raw table outside of a transaction > to avoid using logical logs. I can't think of a solution to your situation outside what I mentioned before. But keep in mind that most of the times you would: BEGIN WORK; LOCK TABLE ... IN EXCLUSIVE MODE do some *DML* -- which would not consume logs COMMIT WORK; So, in most cases you'd not face the issue... Regards. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list