Informix Database Creation Using Various Log Modes

Find below CREATE DATABASE statement to create a new database in informix server.

Explore the database server offers the following choices for transaction logging:

• No logging at All: his is not a recommended choice. If you lose the database because of a hardware failure, you lose all data alterations since the last backup.

Syntax:
$dbaccess sysmaster <<EOF
> create database testdbs1;
>EOF

When you do not choose logging, BEGIN WORK and other SQL statements that are related to transaction processing are not permitted in the database. This situation affects the logic of programs that use the database.

•  Buffered Logging: If you lose the database, you lose few or possibly none of the most recent alterations. In return for this small risk, performance during alterations improves slightly.Buffered logging is best for databases that are updated frequently (so that speed of updating is important), but you can re-create the updates from other data in the event of a failure. Use the SET LOG statement to alternate between buffered and regular logging.

Syntax:
dbaccess sysmaster <<EOF
>create database testdbs2 with BUFFERED LOGGING
>EOF

• Regular Unbufferred Logging:This choice is best for most databases. In the event of a failure, you lose only uncommitted transactions.

Syntax:
dbaccess sysmaster <<EOF
>create database testdbs3 with LOG;
>EOF

• ANSI-compliant logging:This logging is the same as regular logging, but the ANSI rules for transaction processing are also enforced.The design of ANSI SQL prohibits the use of buffered logging. When you create an ANSI-compliant database, you cannot turn off transaction logging.

Syntax:
dbaccess sysmaster <<EOF
>create database testdbs4 with log mode ansi;
>EOF

Note:For Dynamic Server databases that are not ANSI-compliant, the database server administrator (DBA) can turn transaction logging on and off or change from buffered to unbuffered logging. For example, you might turn logging off before inserting a large number of new rows.

You might want to create an ANSI-compliant database for the following reasons:

•  Privileges and access to objects

ANSI rules govern privileges and access to objects such as tables and synonyms.

• Name isolation

The ANSI table-naming scheme allows different users to create tables in a database without name conflicts.

• Transaction isolation
• Data recovery

ANSI-compliant databases enforce unbuffered logging and implicit transactions for Dynamic Server.