Logging TYPE Options

Use the Logging TYPE options to specify that the table has particular characteristics that can improve various bulk operations on it.

This syntax fragment is part of the ALTER TABLE statement.
Read syntax diagramSkip visual syntax diagram
Logging TYPE Options


Here STANDARD, the default option of the CREATE TABLE statement, specifies a logged table, and RAW specifies an unlogged table.

A table can have any of the following logging characteristics.

Logging table that allows rollback, recovery, and restoration from archives. This is the default. Use this type for recovery and constraints functionality on OLTP databases.
Nonlogging table that do not support primary key constraints, unique constraints, or referential constraints. RAW tables can have NOT NULL constraints and NULL constraints (but not on the same set of columns). They can be indexed and updated. Use this type for quickly loading data.
Warning: Use RAW tables for fast loading of data. It is recommended that you alter the logging type to STANDARD and perform a level-0 backup before you use the table in a transaction or modify the data within the table. If you must use a RAW table within a transaction, either set the isolation level to Repeatable Read or lock the table in exclusive mode to prevent concurrency problems.

The Logging TYPE option can convert a non-logging table, such as a RAW table, to a STANDARD table that supports transaction logging. If you use this feature, you should be aware that the database server does not check to see whether a level 0 archive has been performed on the table.

Operations on a RAW table are not logged and are not recoverable, so RAW tables are always at risk. When the database server converts a table that is not logged to a STANDARD table type, it is your responsibility to perform a level-0 backup before using the table in a transaction or modifying data in the table. Failure to do this might lead to recovery problems in the event of a server crash.

For more information on these logging types of tables, refer to your IBM® Informix® Administrator's Guide.

The Logging TYPE options have the following restrictions:
  • You must perform a level-0 archive before the logging type of a table can be altered to STANDARD from any other logging type.
  • The table cannot be a TEMP table, and you cannot change any of these types of tables to a TEMP table.
The following example changes a nonlogging table to a table that uses transaction logging:
The following example changes a logging table to a nonlogging table.