Logical logs filling up - blocks whole server

Hello We're having problems with some backup procedures that are not as reliable as we want them to be. I've got sql code that is running a bulk import and sometimes we're having failures with our backup system which mean that the informix server blocks all sql commands until logical logs get backed up and cleared down. This is good, but I would like a way of controlling this behaviour because I need responsiveness from the server (fail/succeed!) even if it cannot start a transaction. I've set "lock mode to wait 3" because this is a real-time system that needs to respond within 5 seconds. However, this doesn't rollback / abort the transactions when the logical logs have filled up. I've got routines and procedures to ensure that there isn't a problem if this timeout happens, however because the server locked up due to its logical logs filling up these didn't "kick in". Now, I will put something in the code that does the commands to automatically abort them if they take 5 seconds or longer, however I would also like to know whether there is a way of controlling this from the informix sql side - for example "set wait-for-logical-log-backup mode to wait 3" :^) - you can't go wrong with too many timeouts. Any suggestions would be appreciated. -- ben

Ben Gould <ben.gould@none.net> writes: > Hello > > We're having problems with some backup procedures that are not as > reliable as we want them to be. > > I've got sql code that is running a bulk import and sometimes we're > having failures with our backup system which mean that the informix > server blocks all sql commands until logical logs get backed up and > cleared down. If you have enough logs (in count and size) this shouldn't be a problem -if you continously back them up. > This is good, but I would like a way of controlling this > behaviour because I need responsiveness from the server (fail/succeed!) > even if it cannot start a transaction. > > I've set "lock mode to wait 3" because this is a real-time system that > needs to respond within 5 seconds. However, this doesn't rollback / > abort the transactions when the logical logs have filled up. I've got > routines and procedures to ensure that there isn't a problem if this > timeout happens, however because the server locked up due to its > logical logs filling up these didn't "kick in". > > Now, I will put something in the code that does the commands to > automatically abort them if they take 5 seconds or longer, however I > would also like to know whether there is a way of controlling this from > the informix sql side - for example "set wait-for-logical-log-backup > mode to wait 3" :^) - you can't go wrong with too many timeouts. > > Any suggestions would be appreciated. More logs, larger logs, continuous backup of logs? If you have continuous backup of logs but the backup fails I suggest you fix that problem. Thomas