Re: Turn Transactions on?

Darin Strait (71540.3036@compuserve.com) wrote: : Why would I not want to turn on the transaction log? (I assume that this : option was not exercised for some good reason and isn't the result of : someone forgetting about it.) I can see no mandatory reason for not having transaction logging in a database. However, if you want to convert from unlogged to logged status, you have to take the following things into consideration: 1) When transactions are used, any SQL DML statement that is not carried out in an explicit transaction (wrapped in BEGIN WORK/COMMIT WORK|ROLLBACK WORK) will be an implicit transaction by itself 2) At the end of a transaction, all locks held by the transaction are automatically released. I am not totally sure whether it is possible to have other locks which are not affected by the transaction, but I believe that it is not possible to have more than one transaction open in a single application at any given time. 3) At the end of a transaction, all cursors opened by the transaction are automatically closed. To have a cursor survive the scope of the transaction, it has to be declared "WITH HOLD". Also, a cursor declared "FOR UPDATE" can only be opened inside a transaction. When you want to migrate towards transaction processing, you have to scan your code for occurences of cursors and locks and check whether they will be affected. : If I was going to turn on the transaction log, how would I go about : doing it? Start "isql <dbname>". Then in "Query Language", use "New" to enter the following statements: close database; start database <dbname> with log in "<full pathname of transaction logfile>"; From now on, you have transactions available. Beware of the fact that the logfile is not purged automatically, you have to do housekeeping from time to time by entering "cat /dev/null <logfile>" after a backup of the database directory. : With Informix SE, if transactions are implemented on a per-database : level, how can I find out if each (or any) of the three databases have : transactions turned on, besides writing a program that starts some : simple-minded transaction? You can find out with the following SQL statement: SELECT dirpath FROM systables WHERE tabname = "syslog"; If there are transactions turned on, it will give you the name of the logfile. If there are none, nothing will be returned. BTW: If you ever want to turn off transaction logging (might be a good idea when you do large bulk inserts, for performance reasons), you issue: DELETE FROM systables WHERE tabname = "syslog"; Don't forget to turn logging back on afterwards with the "START DATABASE" statement. Hope this helps, Richard -- +----------------------------+-------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413 | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | | +----------------------------+-------------------------------------------+