Transactions & Auto Commits - Advice needed

Hi All Some advice appreciated. Note: The system is Informix, but I assume all DB's are fairly similar. I recently joined a firm using Informix (.. sadly all there Informix 4GL designers left on bad terms ). I have since learned that a table update causes an auto commit. This seems to leave us rather exposed ie a single transaction is made up of 10+ updates to different tables. What happens if the system crashes 1/2 way. We have 5 tables committed but out of sync with the other 5. Seems to me we are screwed. Any Advice appreciated. Perhaps we just rewrite the whole system. Can we tell Informix to commit all associated transaction table updates together ? Any other ideas (Its a big DB). Cheers Bill

Bill_Tolman wrote: > Hi All > > ... > > I have since learned that a table update causes an auto commit. What you've learnt is not quite right. Informix support the usual transaction control statement like BEGIN WORK, ROLLBACK WORK & COMMIT WORK. If your database is in the ANSI mode (see the CREATE DATABASE statement for details) , the BEGIN WORK statement is implicit. COMMIT WORK must be done explicitly. Additionally, if your database is not logged, transactions are not supported. However, its in easy matter to turn on logging. There is excellent documentation for Informix products (one of the many advantages of Informix) at www.informix.com. You may want to download the relevant manuals and keep them as a handy reference. > > > This seems to leave us rather exposed ie a single transaction is made up > of 10+ updates to different tables. > > What happens if the system crashes 1/2 way. > We have 5 tables committed but out of sync with the other 5. > Seems to me we are screwed. > > Any Advice appreciated. > Perhaps we just rewrite the whole system. Ha! That's a good one. > > Can we tell Informix to commit all associated transaction table updates > together ? > > Any other ideas (Its a big DB). > > Cheers > Bill > BTW, Welcome to the world of Informix. You're gonna love it! Rudy