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

Logging TYPE Options:
 
|--TYPE--(--+-STANDARD---------------+--)-----------------------|
            +-RAW--------------------+
            |  (1)                   |
            '--------+-OPERATIONAL-+-'
                     '-STATIC------'
 

1. Extended Parallel Server only

Here STANDARD, the default option of the CREATE TABLE statement, is used for online transaction processing (OLTP) databases. The OPERATIONAL and STATIC options are used primarily to improve performance in data warehousing databases.

A table can have any of the following logging characteristics.

Option

Effect

STANDARD

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.

RAW

Nonlogging table that cannot have indexes or referential constraints but can be updated. Use this type for quickly loading data. In XPS, raw tables take advantage of light appends and avoid the overhead of logging, checking constraints, and building indexes.

OPERATIONAL (XPS only)

Logging table that uses light appends and cannot be restored from archive. Use this type on tables that are refreshed frequently. Light appends allow the quick addition of many rows.

STATIC (XPS only)

Nonlogging table that can contain index and referential constraints but cannot be updated. Use this type for read-only operations because there is no logging or locking overhead.

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.
• If you want to change the logging type of a table to RAW, you must drop all indexes on the table before you do so.
• If you have triggers defined on the table, you cannot change the logging type to RAW or STATIC. These nonlogging table types do not support triggers.
• The table cannot be a SCRATCH or TEMP table, and you cannot change any of these types of tables to a SCRATCH or TEMP table.
• The table cannot have a dependent generalized-key (GK) index.

Use the ADD TYPE clause to convert a table that is not based on a named ROW data type into a typed table. This clause is an extension to the ANSI/ISO standard for SQL.

ADD TYPE Clause:
 
|--ADD TYPE--row_type-------------------------------------------|
 

When you use the ADD TYPE clause, you assign the specified named ROW data type to a table whose columns match the fields of that data type.

In addition to the requirements common to all ALTER TABLE operations (namely DBA privilege on the database, Alter privilege on the table, and ownership of the table), all of the following must be also true when you use the ADD TYPE clause to convert an untyped table to the specified named ROW data type:

• The named ROW data type is already registered in the database.
• You hold the Usage privilege on the named ROW data type.
• There must be a 1-to-1 correspondence between the ordered set of column data types of the untyped table and the ordered set of field data types of the named ROW data type.
• The table cannot be a fragmented table that has rowid values.

You cannot combine the ADD TYPE clause with any clause that changes the schema of the table. No other ADD, DROP, or MODIFY clause is valid in the same ALTER TABLE statement that has the ADD TYPE clause. The ADD TYPE clause does not allow you to change column data types. (To change the data type of a column, use the MODIFY clause.)

ALTER TABLE supports only the following options for tables of ROW data types.

Typed-Table Options:
 
   .-,-------------------------------------------.
   V                                             |
|----+-----------------------------------------+-+--------------|
     |                           (1)           |
     +-| ADD CONSTRAINT Clause |---------------+
     |                            (2)          |
     +-| DROP CONSTRAINT Clause |--------------+
     |  (3)                               (4)  |
     +--------| MODIFY NEXT SIZE Clause |------+
     |  (3)                        (5)         |
     '--------| LOCK MODE Clause |-------------'
 

1. See page ADD CONSTRAINT Clause
2. See page DROP CONSTRAINT Clause
3. Use path no more than once
4. See page MODIFY NEXT SIZE Clause
5. See page LOCK MODE Clause