Oracle Database

About this task

Lift uses the following different modes to extract your data:
  • Native
  • JDBCN

The native mode of the lift extract command leverages Oracle's native tools for extraction and is recommended for the best performance. If Oracle client tools cannot be installed or configured properly, Lift CLI automatically switches to JDBC mode that has relatively slower performance. For best performance, use Oracle client tools (Basic and Tools packages) that provide native extract tooling. Install and configure the Oracle client on the same box as Lift CLI and set the client path in the system environment variable so that Lift CLI can find and use it.

The native mode extract method uses server encoding irrespective of the encoding on the client machine. If the table name or column names contain multi byte character set (MBCS) characters, then the encoding of the client must match the encoding of the server so that the table and column names can be specified properly in the Lift CLI commands.

Recommended path for migration: 

  1. Migrate table structure to the target database
  2. Move data using LIFT CLI
  3. Optional: Migrate indexes and other constraints

Create the schema and table structures on your IBM Cloud target database (Db2 Warehouse SaaS or Db2 as a Service). You have several options to do this, but the most effective way is to download, install, and use the IBM Database Conversion Workbench (DWC). This tool can help you to convert your existing Oracle schema to one that's compatible with the Db2 Warehouse SaaS or Db2 as a Service engine database. After completing the conversion, the DCW produces a report that identifies which parts of your source DDL were automatically converted and which parts require manual intervention. For more information, see the included step-by-step DCW guide.

After your table structures are in place, you can start moving your Oracle tables. Start by extracting a table to a CSV file. Move that file over the network, stage it in the landing zone on Db2 Warehouse SaaS or on Db2 as a Service, and then load it into the database.

Procedure

  1. Extract the table to a CSV file by running the following lift extract command: 
    % lift extract --source-schema <oracle-schema-name> --source-table <oracle-table-name> --source-database <oracle-database-name> --source-host <oracle-host-name> --source-user <oracle-user-name> --source-password <oracle-password> --source-database-port <oracle-database-port> --source-database-type <oracle> --file <path-to-csv-file>
  2. Move your CSV file and stage it in the landing zone of the target database by running the following lift put command: 
    % lift put --file <path-to-csv-file> --target-user <target-user-name> --target-password <target-password> --target-host <target-database-host-name>
  3. Load your CSV file into the target engine database by running the following lift load command: 
    % lift load --filename <csv-file-name> --target-schema <target-schema-name> --target-table <target-table-name> --file-origin extract-oracle --target-user <target-user-name> --target-password <target-password> --target-host <target-database-host-name>

    The --file-origin extract-oracle option specifies that the CSV file was extracted by using the lift extract command.

    You're done. You can now run SQL queries on your data from the database console.

    Optionally, you can move indexes and other constraints from the source database to the target database after completing the data movement.