Enterprise Edition Home | Express Edition Home | Previous Page | Next Page   SQL Statements > CREATE TABLE >

Logging Options

Use the Logging Type options to specify logging characteristics that can improve performance in various bulk operations on the table. Other than the default option (STANDARD) that is used for OLTP databases, these logging options are used primarily to improve performance in data warehousing databases.

A table can have either of the following logging characteristics.

Logging Type
Effect
STANDARD
Logging table that allows rollback, recovery, and restoration from archives. This type is the default. Use this type of table for all the recovery and constraints functionality that OLTP databases require.
RAW
Nonlogging table that cannot have indexes or referential constraints but can be updated. Use this type of table for quickly loading data.

By using raw tables with Extended Parallel Server, you can take advantage of light appends and avoid the overhead of logging, checking constraints, and building indexes.

Warning:
Use raw tables for fast loading of data, but set 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.

Extended Parallel Server supports two additional logging type options.

Option
Effect
OPERATIONAL
Logging table that uses light appends; it cannot be restored from archive. Use this type on tables that are refreshed frequently, because light appends allow the quick addition of many rows.
STATIC
Nonlogging table that can contain index and referential constraints but cannot be updated. Use this type for read-only operations, because no logging or locking overhead occurs.

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

Column Definition

Use the column definition portion of CREATE TABLE to list the name, data type, default values, and constraints of a single column. 

Read syntax diagramSkip visual syntax diagramColumn Definition:
 
                         (1)
|--column--| Data Type |-------+-------------------------+------>
                               |                    (2)  |
                               '-| DEFAULT Clause |------'
 
>--+------------------------------------------+-----------------|
   |                                     (3)  |
   '-| Single-Column Constraint Format |------'

Notes:
  1. See page Data Type
  2. See page DEFAULT Clause
  3. See page Single-Column Constraint Format

Element Description Restrictions Syntax
column Name of a column in the table Must be unique in this table Identifier, p. Identifier

Because the maximum row size is 32,767 bytes, no more than approximately 195 columns in the table can be of the data types BYTE, TEXT, ROW, LVARCHAR, NVARCHAR, VARCHAR, and varying-length UDTs. (The upper limit on columns of these data types also depends on other data describing the table that the database server stores in the same partition.) No more than approximately 97 columns can be of COLLECTION data types (SET, LIST, and MULTISET).

As with any SQL identifier, syntactic ambiguities (and sometimes error messages or unexpected behavior) can occur if the column name is a keyword, or if it is the same as the table name, or the name of another table that you subsequently join with the table). For information about the keywords of Dynamic Server, see Appendix A. Reserved Words for IBM Informix Dynamic Server.

For more information on the keywords of Extended Parallel Server, see Appendix B. Reserved Words for IBM Informix Extended Parallel Server. For more information on the ambiguities that can occur, see Use of Keywords as Identifiers.

In Dynamic Server, if you define a column of a table to be of a named ROW type, the table does not adopt any constraints of the named ROW.

DEFAULT Clause

Use the DEFAULT clause to specify the default value for the database server to insert into a column when no explicit value for the column is specified. 

Read syntax diagramSkip visual syntax diagramDEFAULT Clause:
 
|--DEFAULT------------------------------------------------------>
 
>--+-+-NULL----+-----------------------------------------------+--|
   | +-literal-+                                               |
   | '-USER----'                                               |
   |  (1)                                                      |
   '--------+-CURRENT--+-----------------------------------+-+-'
            |          |                              (2)  | |
            |          '-| DATETIME Field Qualifier |------' |
            +-TODAY------------------------------------------+
            +-SITENAME---------------------------------------+
            '-DBSERVERNAME-----------------------------------'

Notes:
  1. Informix extension
  2. See page DATETIME Field Qualifier

Element Description Restrictions Syntax
literal String of alphabetic or numeric characters Must be an appropriate data type for the column. See Using a Literal as a Default Value. Expression, p. Expression

You cannot specify default values for SERIAL or SERIAL8 columns.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]