Loading data in DELUXE mode

DELUXE mode combines fast parallel loading with evaluation of indexes and unique constraints. The database server chooses this mode for indexed target tables in all databases, and for STANDARD target tables in logged databases.

About this task

Note:

In versions of the database server earlier than 11.70, the DELUXE 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.

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.

You can specify DELUXE mode to override the default EXPRESS load mode for RAW target tables without indexes if the database is logged.

The following steps show you how to prepare a table for DELUXE mode load, create the internal table as type STANDARD, and create the external table.

Tip: The database server will automatically use DELUXE mode to load this type of table. You do not need to specify the DELUXE keyword when you define the external table. If you specify the EXPRESS keyword in the table definition, the database server will use DELUXE mode anyway and issue an informational message.

To use DELUXE-mode load on a table:

Procedure

If you want row locking, specify row locking in the CREATE TABLE statement. (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;
```

Define the external table.

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

Load the table.

INSERT INTO employee SELECT * FROM emp_ext;

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

Results

Important: Configure logical logs to allow maximum concurrent DELUXE load transactions to complete.