Loading Data Warehousing Tables

The following scenario creates and loads a data warehouse table with data from outside of the Informix database server.

To load a table initially

Create the table as type RAW to take advantage of light appends and to avoid the overhead of logging during the load.
```
CREATE RAW TABLE tab1 ...
```
Describe the external data file to the Informix database server with the CREATE EXTERNAL TABLE statement.
Load the table in express mode.
```
INSERT INTO tab1 SELECT * FROM EXTERNAL TABLE ext_tab
```
The table loads quickly, and the operation consumes only a minuscule amount of log space.
Verify integrity of the data.
If you do not plan to update the table, change it to type STATIC.
```
ALTER TABLE tab1 TYPE(STATIC);
```
If you plan to update the table, change it to a logging type such as OPERATIONAL or STANDARD.
Create indexes on the table to speed queries against it.
Perform a level-0 backup now to enable you to restore the table later, if necessary. You do not need to perform this level-0 backup if you feel it would be just as easy to reload the table from the original source in the case of a catastrophe.

Refreshing Periodically

This scenario loads new data in a data warehouse table periodically from some other source. The scenario assumes that the table is type STATIC during normal operation and that the CREATE EXTERNAL TABLE statement has been previously executed.

To refresh a table periodically

Drop all indexes on the table.
Alter the table to type RAW.
```
ALTER TABLE tab1 TYPE(RAW);
```
Load the new data in the table in express mode.
```
INSERT INTO tab1 SELECT * FROM EXTERNAL TABLE ext_tab
```
This insert statement quickly appends new data to the end of the table, and the operation consumes only a minuscule amount of log space.
Verify integrity of the data.
Change the table to type STATIC.
```
ALTER TABLE tab1 TYPE(STATIC);
```
Re-create indexes on the table to speed queries against it.
Perform a level-0 backup now to enable you to restore the table later, if necessary. You do not need to perform this level-0 backup if you feel it would be just as easy to reload the table from the original source in the case of a catastrophe.

Refreshing Frequently or Continuously

In this scenario, you have a data warehouse table into which new data continuously trickles from some other source. You can easily reload the data from the original source, so it is not worth the trouble to do the backups.

To refresh a table frequently or continuously

Create the table as type OPERATIONAL
Optionally, use the TRUNCATE statement to quickly delete all the rows in the table before you load the new data, as the following example shows:
```
TRUNCATE tab1 
```
For more information on the TRUNCATE statement , see the IBM Informix: Guide to SQL Syntax.
Start the violations table if you want to check referential or unique constraints during the load. The load operation completes successfully even if constraint violations exist. (Do not set constraints to deferred when you use the violations table.)
(The database server detects and rejects check constraints in the external table.)
Use one of the following methods to load the new data:
1. Express mode for bulk inserts of new data if no indexes or referential or unique constraints exist
2. Ordinary inserts performed one record at a time
Create and drop indexes and constraints as necessary.

Initial Loading of OLTP Data from Other Database Servers

This scenario loads data in Extended Parallel Server for the first time, as you might do when you migrate from a different database server. In this scenario, the table to load will be used for OLTP, so you need logged transactions, rollback, and recoverability.

To load OLTP data initially from a different database server

Create the table as type RAW to take advantage of light appends and to avoid the overhead of logging during the load.
```
CREATE RAW TABLE tab1 ...
```
Describe the external data file to the Informix database server with the CREATE EXTERNAL TABLE statement.
Load the table in express mode.
```
INSERT INTO tab1 SELECT * FROM EXTERNAL TABLE ext_tab
```
The table loads quickly, and the operation consumes only a minuscule amount of log space.
Verify integrity of the data.
Perform a level-0 backup to provide a point from which to recover.
Change the table to type STANDARD.
```
ALTER TABLE tab1 TYPE(STANDARD);
```
Create indexes on the table to speed queries against it.
Enable constraints on the table to preserve the integrity of the data.

Tip:

For information on using external tables to load data from a Version 7.2 or Version 7.3 database server to a Version 8.3 database server, see the IBM Informix: Migration Guide.