Long Transaction aborted

Hi Friends, Following is environment details. Informix version = IBM Informix Dynamic Server Version 11.50.FC6 Table name = daily_records Total Columns = 210 No of rows = 64 Millions Size of Logical Logs = 10 GB Transaction/minutes = 50 Existing no of indexes = 24 Reference/referenced tables = 0 OnConfig Settings related to long transaction: LTXHWM 50 LTXEHWM 60 we were creating online index on two columns i-e date (datetime),status(char(1)). After 14 hours we got error long transaction aborted error. Here are my two questions 1. How to calculate INDEX size in informix? (if possible share any formula) 2. What steps should we do to avoid long transaction issue? Increase logical logs or modify some parameters of onconfig file? 3. Any other things we can look? Regards, Waseem

There was also a message from Waseem's colleague asking: > We are using IDS 11.50.FC6 on Sun OS Sparc. > > We were executing alter table command (we were creating index using online > command), but received Long Transactions Aborted message. > There were more than 4 GB's logs were still available (that were un-used), > but > still we got this error. > > Is there any particular way out for this ? > On Tue, Mar 15, 2011 at 04:27, WASEEM NOORI <wnoori@i2cinc.com> wrote: > Following is environment details. > > Informix version = IBM Informix Dynamic Server Version 11.50.FC6 > Table name = daily_records > Total Columns = 210 > No of rows = 64 Millions > Size of Logical Logs = 10 GB > Transaction/minutes = 50 > Existing no of indexes = 24 > Reference/referenced tables = 0 > > OnConfig Settings related to long transaction: > > LTXHWM 50 > LTXEHWM 60> > we were creating online index on two columns i-e date > (datetime),status(char(1)). After 14 hours we got error long transaction > aborted error. Here are my two questions > > 1. How to calculate INDEX size in informix? (if possible share any formula) > 2. What steps should we do to avoid long transaction issue? Increase > logical > logs or modify some parameters of onconfig file? > 3. Any other things we can look? > One part of the question says 'ALTER TABLE' but another mentions 'creating online index'. There's a big difference between simply creating an index on a table and altering a table wholesale. The latter requires all the indexes to be built - in general. So, you need to be more precise about what you were really doing. You don't indicate the row size. This is a key factor in the size calculations for ALTER TABLE. You also don't discuss the size of the columns being indexed; that is a crucial factor in determining the size of an index. You have 24 indexes on the table already; that seems like an astronomical number. If you are doing ALTER TABLE, then IDS might have to rebuild all 24 existing indexes -- it depends on the statement you were actually executing. (Adding a constraint is one thing - and doesn't require a rebuild.) When calculating index size, you need to know the size of the key, the amount of repetition in the key (is it almost unique, or are there 10 records per key value, on average, or 100, or 1000). There's a couple of big fudge factors in the calculation, but number of rows * (key size + overhead) * fudge-factor is the main component. The fudge-factor is larger than 1; try 2 as a starting point. The overhead is about 12 bytes, I think (slot = 4 bytes; plus the placement information for the data row). If you are rebuilding all indexes at once, then you need this much space per index. If you are simply adding one index, and the key size is, say, 20 bytes, then you need 64M * (20+12) * 2 = 4096 M log space. That's a very crude calculation; I'm not sure that I'd trust the fudge-factor all that much (it could be an underestimate). It suggests that maybe you should not have run out of space - but there are major guesses (key size, notably) in there. If you were rebuilding the entire table, then you need vastly more log space to build each of 24 indexes. Since you have 10 GB log space and LTXEHWM at 60%, you should have gotten the LTX when there was about 4 GB spare. That part of the system worked perfectly. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --0023545bdbbc0652a3049e85aff1

Unless you have any HDR or RSS secondaries, you should disable LOG_INDEX_BUILDS (which you can do using onmode -wm) until after the index is built. Then only the fact of the index build will be logged, not all of the page modifications that attend that. That should help. Otherwise, add more logical log space. The size of the index is the key length plus 4 times 1.5 times the number of rows. Divide that by 2020 to get total pages. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 15, 2011 at 7:27 AM, WASEEM NOORI <wnoori@i2cinc.com> wrote: > Hi Friends, > > Following is environment details. > > Informix version = IBM Informix Dynamic Server Version 11.50.FC6 > Table name = daily_records > Total Columns = 210 > No of rows = 64 Millions > Size of Logical Logs = 10 GB > Transaction/minutes = 50 > Existing no of indexes = 24 > Reference/referenced tables = 0 > > OnConfig Settings related to long transaction: > > LTXHWM 50 > LTXEHWM 60> > we were creating online index on two columns i-e date > (datetime),status(char(1)). After 14 hours we got error long transaction > aborted error. Here are my two questions > > 1. How to calculate INDEX size in informix? (if possible share any formula) > 2. What steps should we do to avoid long transaction issue? Increase > logical > logs or modify some parameters of onconfig file? > 3. Any other things we can look? > > Regards, > Waseem > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f3816287b9e049e871bf3