Select the type of logging

To specify a logging or nonlogging database, use the CREATE DATABASE statement. The database server offers the following choices for transaction logging:
  • No logging at all.
    This is not a recommended choice. If you lose the database because of a hardware failure, you lose all data alterations since the last backup.
    CREATE DATABASE db_with_no_log

    When you do not select 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.

  • Regular (unbuffered) logging.
    This choice is best for most databases. In the event of a failure, you lose only uncommitted transactions.
    CREATE DATABASE a_logged_db WITH LOG
  • 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.
    CREATE DATABASE buf_log_db WITH BUFFERED LOG

    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.

  • ANSI-compliant logging.
    This logging is the same as regular logging, but the ANSI rules for transaction processing are also enforced. For more information, see Use ANSI-compliant databases.
    CREATE DATABASE std_rules_db WITH LOG MODE ANSI

    The design of ANSI SQL prohibits the use of buffered logging. When you create an ANSI-compliant database, you cannot turn off transaction logging.

For 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 can use the ondblog and ontape utilities to change the logging status or buffering mode. For information about these tools, see the IBM® Informix® Administrator's Guide. You can also use the SET LOG statement to change between buffered and unbuffered logging. For information about SET LOG, see your IBM Informix Guide to SQL: Syntax.