Initial Loading of OLTP Data from Other Database Servers

This scenario loads data into HCL OneDB™ for the first time, as you might do when you migrate from a different database server.

About this task

In this scenario, the table to load will be used for OLTP, so you need logged transactions, rollback, and recoverability.

Note:

In versions of the database server earlier than 11.70, the DELUXE or 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 choose between DELUXE and EXPRESS mode in load operations where both modes are supported.

To load OLTP data initially from a different database server using the CREATE EXTERNAL TABLE statement:

Procedure

  1. Create the table as type RAW to take advantage of light appends and to avoid the overhead of logging during the load. 
    CREATE RAW TABLE tab1 ...
  2. Describe the external data file to the database server with the CREATE EXTERNAL TABLE statement specifying the EXPRESS in the USING clause.Describe the external data file to the database server with the CREATE EXTERNAL TABLE statement.
  3. Load the table.
    INSERT INTO tab1 SELECT * FROM ext_tab

    The table loads quickly, and the operation uses very little log space.

  4. Verify the integrity of the data.
  5. Perform a level-0 backup to provide a point from which to recover.
  6. Change the table to type STANDARD. 
    ALTER TABLE tab1 TYPE(STANDARD);
  7. Create indexes on the table so that queries run more quickly.
  8. Enable constraints on the table to preserve the integrity of the data.