Loading Data in Express Mode

Express® mode supports rapid loading of data into tables that have no indexes. In logged databases, only RAW tables can use this mode.

Warning: Express-mode loads are not allowed for STANDARD tables in databases that support transaction logging.

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.

You can use express mode for any newly created table with no data if you define the table as type RAW and do not define any indexes until after you load the data. Choose RAW tables if you do not want to use logging in a database that supports transaction logging.

To prepare an existing table for express-mode load, drop all indexes, and make sure the table type is RAW.

Data loaded from an external table into a raw table is not logged; therefore, you must perform a level-0 backup before the database can be dropped. If you try to drop the database before you perform a level-0 backup, the database server issues ISAM error -197, as follows:

Partition recently appended to; can't open for write or logging

Consider a table with the following schema:

TABLE employee (
   name CHAR(18),
   hiredate DATE,
   address CHAR(40),
   empno INTEGER);

To use express-mode load on an existing table

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

Create the external table description.

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

Load the table.
```
INSERT INTO employee SELECT * FROM emp_ext;
```
If the database server chooses express mode, the load stops with an error message if the destination table contains indexes, constraints, or any other problem conditions.
Create 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.

Note: 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 insert rows at the end of the table, and do not reuse the empty space inside the table. (Whether or not you specify EXPRESS mode, the loader might choose DELUXE mode to fill in the space if a table has many deleted rows.)