IBM Integrated Analytics System

About this task

Create the table structures on your Db2 Warehouse SaaS database target.

To migrate table structures, use the lift ddl command instead of the Database Conversion Workbench (DCW) tool. Choose one of the following methods:

Procedure

  1. Migrate table structures using Lift without any intervention. This method involves a single command that extracts DDL from the source database and applies it to the target database. 
    % lift ddl --migrate --source-schema <source-schema-name> --source-object <source-object-name> --source-database <source-database-name> --source-user <source-user-name> --source-password <source-password> --source-host <source-database-host-name> --source-database-port <source-database-port> --source-database-type <source-database-type> --target-user <target-user-name> --target-password <target-password> --target-host <target-database-host-name>
  2. Extract the table DDL using Lift CLI, modify the generated DDL (optional) and apply the DDL to the target database. This method involves two command options:
    1. Generate the DDL
    2. Execute the generated DDL
    % lift ddl --generate --source-schema <source-schema-name> --source-object <source-object-name> --source-database <source-database-name> --source-user <source-user-name> --source-password <source-password> --source-host <source-database-host-name> --source-database-port <source-database-port> --source-database-type <source-database-type>[options]
    Use this method if you want to customize the DDL extracted from the source database.
    Run the lift ddl --help command for more available command options. Run the lift ddl --help command for more available command options.
    % lift ddl --execute --file <path-to-ddl-file> --target-user <target-user-name> --target-password <target-password> --target-host <target-database-host-name>

Move your data

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

  1. Extract the table to a CSV file by running the following lift extract command:
    % lift extract --source-schema <source-schema-name> --source-table <source-table-name> --source-database <source-database-name> --source-host <source-host-name> --source-user <source-user-name> --source-password <source-password> --source-database-port <source-database-port> --source-database-type <ias/db2/db2w> --file <path-to-csv-file>

    The ias, db2, and db2w settings for the –source-database-type command option are used to specify the particular source database type.

  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-ias/extract-db2/extract-db2w> --target-user <target-user-name> --target-password <target-password> --target-host <target-database-host-name>
  4. The extract-ias, extract-db2, and extract-db2w settings for the --file-origin command option are used to specify that the CSV file was extracted from a particular database by using the lift extract command.

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