Loading and Unloading Tables

Databases for decision-support applications are often created by periodically loading tables that have been unloaded from active OLTP databases. You can use one or more of the following methods to load large tables quickly:

For recovery information about standard and raw tables, see your IBM Informix: Dynamic Server Administrator's Guide.

Advantages of Logging Tables

The STANDARD type, which corresponds to a table in a logged database of previous versions, is the default. When you issue the CREATE TABLE statement without specifying the table type, you create a standard table.

OLTP applications usually use standard tables. OLTP applications typically have the following characteristics:

Advantages of Nonlogging Tables

The advantage of nonlogging tables is that you can load very large data warehousing tables quickly because they have following characteristics:

To quickly load a large, existing standard table

Drop indexes, referential constraints, and unique constraints.
Change the table to nonlogging.
The following sample SQL statement changes a STANDARD table to nonlogging:
```
ALTER TABLE largetab TYPE(RAW);
```
Load the table using a load utility such as dbexport or the High-Performance Loader (HPL).
For more information on dbexport and dbload, see the IBM Informix: Migration Guide. For more information on HPL, see the IBM Informix: High-Performance Loader User's Guide.
Perform a level-0 backup of the nonlogging table.
You must make a level-0 backup of any nonlogging table that has been modified before you convert it to STANDARD type. The level-0 backup provides a starting point from which to restore the data.
Change the nonlogging table to a logging table before you use it in a transaction.
The following sample SQL statement changes a raw table to a standard table:
```
ALTER TABLE largetab TYPE(STANDARD);
```
Warning:

It is recommended that you not use nonlogging tables within a transaction where multiple users can modify the data. If you need to use a nonlogging table within a transaction, either set Repeatable Read isolation level or lock the table in exclusive mode to prevent concurrency problems.

For more information on standard tables, see the previous section, Advantages of Logging Tables.
Re-create indexes, referential constraints, and unique constraints.

To quickly load a new, large table

Create a nonlogging table in a logged database.
The following sample SQL statements creates a nonlogging table:
```
CREATE DATABASE history WITH LOG;
CONNECT TO DATABASE history;
CREATE RAW TABLE history (...
);
```
Load the table using a load utility such as dbexport or the High-Performance Loader (HPL).
For more information on dbexport and dbload, see the IBM Informix: Migration Guide. For more information on HPL, see the IBM Informix: High-Performance Loader User's Guide.
Perform a level-0 backup of the nonlogging table.
You must make a level-0 backup of any nonlogging table that has been modified before you convert it to STANDARD type. The level-0 backup provides a starting point from which to restore the data.
Change the nonlogging table to a logging table before you use it in a transaction.
The following sample SQL statement changes a raw table to a standard table:
```
ALTER TABLE largetab TYPE(STANDARD);
```
Warning:

It is recommended that you not use nonlogging tables within a transaction where multiple users can modify the data. If you need to use a nonlogging table within a transaction, either set Repeatable Read isolation level or lock the table in exclusive mode to prevent concurrency problems.

For more information on standard tables, see the previous section, Advantages of Logging Tables.
Create indexes on columns most often used in query filters.
Create any referential constraints and unique constraints, if needed.