Re: The dreaded long transaction error.

> We recently had to call informix in order to "fix" our database after a > longtransaction error. The logical logs had filled as a result of a > "simple"unload from a large table which did group by's, order by's and > also had amin(unique..) thrown in there for good measure! > The logs couldn't be backed up as the job was still regarded as runnning > bythe database (even though it wasn't). > You should have been able to back up all logs other than the current one!! > A few questions : > > 1. Why would the above example even use the logical logs? The above example uses the logs to store details of temporary tables created in doing the select statement. Probably to do with the order by, the group by, etc. > > 2. Is there any way to use software to automatically (and selectively!) > aborta job which is threatening to fill the logs? No good way other than to make sure all logs are empty before a "very large" job is run, all users are off the system. It's not perfect even then. > > 3. Any good rule of thumb for how big the logs should be? (we dont use > transaction logging). The only way of estimating this - if transcation logging really isn't used - is to run the query immediately following an UPDATE STATISTICS, and then use the tblog utility to show how much data is being used up in the logs. I would think it could be about 32 * ((estimated number of row returned *2*row size returned+ 25%)/16k) That is only a guess - it could be more. And that figure would have to be less than approx 70% of the total logical log size. > > 4. Does anyone know what Informix did to free the logs? INFORMIX probably cleared the logical logs. This would jeopardise your recovery procedures which you don't have a you don't use logging. Malcolm Weallans Online Database Consultancy Phone 01628-72154 Fax 01628-37463 CIX - onlinedbc