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:
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?
There are some ways to avoid the long transaction:
- increase logical logs, you could create some in your datadbs
- increase LTXHWM and LTXEHWM
- set the log mode to no log, create index and set the log mode back
- create a new empty table with the index and then copy the data to the new table
- little crazy way: set LTXHWM and LTXEHWM to 100 and DYNAMIC_LOGS to 2
I think there is no easy way to calculate the index size because it depends on the data.