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.

Before you begin

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

About this task

Note:

In versions of the database server earlier than 11.70, the EXPRESS keyword specified how data records are loaded into the external table in SQL operations. Beginning in version 11.70, only command-line utilities can force the database server to use DELUXE mode or EXPRESS mode in load operations where both modes are supported.

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.

Whether or not you 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 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

Procedure

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. ,

Results

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.)