Logging Options

The logging options of the CREATE DATABASE statement determine the type of transaction logging for the database. In the event of a failure, the database server uses the log to re-create all committed transactions in your database, unless CREATE DATABASE statement does not include the WITH LOG keywords.

To create a database that supports transaction logging, the CREATE DATABASE statement provides three syntax options:

WITH BUFFERED LOG
This option records transaction log information in a shared-memory buffer. This can allow the current transaction to complete sooner than in unbuffered logging mode, which requires that all the log information must be written to disk before the transaction can be completed. For a database using buffered logging, the database server does not write the contents of the logical-log buffer from shared memory to the logical log on disk until one of these events occurs:
- The buffer becomes full,
- or the connection is closed,
- or a checkpoint occurs,
- or a commit on a another database with unbuffered logging closes a distributed transaction, thereby flushing the buffer to disk.
The following example uses the WITH BUFFERED LOG option to create a database with buffered logging:
```
CREATE DATABASE bufDatabase WITH BUFFERED LOG;
```
WITH LOG
This option writes the log information directly to permanent storage, to reduce the risk of losing data if the server crashes while data rows are being modified. The following statement uses the WITH LOG keywords to create a database with unbuffered logging:
```
CREATE DATABASE unbufDatabase WITH LOG;
```
WITH LOG MODE ANSI
This option enables implicit transactions. This mode also imposes certain requirements of the ANSI/ISO standard for the SQL language that the database server does not enforce in unlogged databases, or in databases that use explicit transactions.
The following statement uses the WITH LOG MODE ANSI keywords to create an ANSI-compliant database with implicit transaction logging:
```
CREATE DATABASE ansiDatabase WITH LOG MODE ANSI;
```

Unlogged databases

If you do not specify the WITH LOG keywords Informix® creates an unlogged database that cannot use transactions, and cannot run these SQL statements that support transaction logging:

BEGIN WORK
COMMIT WORK
ROLLBACK WORK
RELEASE SAVEPOINT
ROLLBACK TO SAVEPOINT
SET IMPLICIT TRANSACTION
SET LOG
SET ISOLATION.

The following example creates an unlogged database:

CREATE DATABASE unlogDatabase;

An unlogged database can improve performance in contexts where the cost of lost data is small. Alternatively, to move existing rows outside a transaction, you can create a database that supports transaction logging, but use RAW tables or light appends for unlogged operations on data that can be recovered from backup sources, if for some reason your load or unload operation fails.

Restrictions on logging modes

Some distributed operations require participating databases to have the same logging mode as the database from which the operation is initiated.

You must use the WITH LOG option when you create a database on a secondary server in a high-availability cluster.