Loading Data in Express Mode

Choose express mode for fast loading of data. Express-mode loads use light appends, which bypass the buffer pool. Light appends eliminate the overhead associated with buffer management but do not log the data. In express mode, the database server automatically locks the table exclusively. No other users can access the table.

If you do not use the DELUXE keyword, the database server uses express mode unless the target table has indexes or is a STANDARD table.

You can use express mode for any newly created table with no data if you define the table as type RAW or operational and do not define any indexes until after you load the data. Choose RAW tables if you do not want to use logging. Choose OPERATIONAL tables if you want to use logging.

To prepare an existing table for express-mode load, drop all indexes, and make sure the table type is either RAW or OPERATIONAL. For more information on table types, see Recoverability of Table Types.

To use express-mode load on an existing table

Alter the table type to allow fast loading.
```
ALTER TABLE employee TYPE (OPERATIONAL);
```

Create the external table description.

CREATE EXTERNAL TABLE emp_ext 
SAMEAS employee
USING (
   FORMAT 'DELIMITED',
   DATAFILES 
     ("DISK:cogroup_all:/work2/mydir/emp%c.dat"),
   REJECTFILE "/work2/mydir/emp%c.rej",
   EXPRESS
   );

Optionally begin a transaction.
```
BEGIN WORK;
```
Perform the load.
```
INSERT INTO employee SELECT * FROM emp_ext;
```
Because you specified express mode (step 2), the load stops with an error message if the destination table contains indexes, constraints, or any other problem conditions.
Commit the transaction if you started it with BEGIN WORK.
```
COMMIT WORK;
```
Perform a level-0 backup.
Because the data is not logged, you must perform a level-0 backup to allow data recovery. If a disk fails, you cannot recover the data automatically. You need to use the most recent level-0 backup files.

If the table type is RAW (nonlogging), omit the statements BEGIN WORK and COMMIT WORK.

You can define check constraints on the external table to filter out bad rows even if you load in express mode.

If you delete many rows from a table and then load many new rows into the table in EXPRESS mode, the table grows in size because light appends append to the end of the table and do not reuse the empty space inside the table. (If you do not specify EXPRESS mode, the loader might choose DELUXE mode to fill in the space if a table has many deleted rows.)