Database - IBM Informix - Turning On/Off Transactional Database

A transactional database is a DBMS where write transactions on the database are able to be rolled back if they are not completed properly (e.g. due to power or connectivity loss). This is to enforce each database transaction comply with ACID (atomic, consistent, isolated and durable) properties.

IBM Informix database server make use of transaction logging to keep a record of each change that is applied to the database during a transaction. If transaction is cancel due to unforeseen circumstances, it can restored to the same state as before the transaction started. This is call a rollback.

Now, if you see error such as transaction not available when using Informix, it can means that the database administrator could have turn off transaction logging or create a database without logging. Switching transaction log off will make a database "un-transactional".

To fix this, you can either

1)  Turn on transaction logging with ontape command

The following will add unbuffered logging to the database

ontape -s -U your_database

2) Create database with regular logging

The following will create a database with regular unbuffered logging

CREATE DATABASE your_database WITH LOG

Both ways are sufficient for most database. In the event of a failure, you lose only uncommitted transactions.

Reference :
1. http://en.wikipedia.org/wiki/Database_transaction
2. http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst161.htm
3. http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm.admin.doc%2Fids_admin_0671.htm
4. http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi73.htm