Loading Data in Deluxe Mode

Deluxe-mode loads use regular single-row inserts, which add rows to a table that can contain indexes. The insert modifies each index for each row during the load. The insert also checks all constraints for each row. A deluxe-mode load allows you to keep the table unlocked during the load so other users can continue to use it.

You also can use deluxe mode on tables that do not contain indexes; for instance, if you want to have complete recoverability or maintain access to tables during a load.

To prepare a table for deluxe-mode load, create the internal table as type OPERATIONAL or STANDARD, and create the external table with the optional keyword DELUXE.

To use deluxe-mode load on a table

If you want row locking, specify row locking in CREATE TABLE. (Page locking is the default.) If you want other users to be able to read the table during the load, set the lock mode to share. Otherwise, set it to exclusive.
```
BEGIN WORK;
LOCK TABLE employee IN SHARE MODE;
```

Create the external table description.

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

Start the violations table if you want the load operation to complete successfully even if constraint violations exist.
You should not start a violations table and then set constraints to deferred. (You also cannot start a violations table when constraints are already set to deferred.) For more information on the violations table and deferred constraints, see the IBM Informix: Guide to SQL Syntax.

Perform the load.

INSERT INTO employee SELECT * FROM emp_ext;

Commit the load, releasing row or page locks.
```
COMMIT WORK;
```

Tip:

Configure logical logs to allow maximum concurrent deluxe-load transactions to complete.