Loading data to IBM Cloud

You can load data from a data file in a delimited format (CSV or TXT) located on a local network or in an object store (Amazon S3 or IBM Cloud Object Storage) to IBM® Db2® Warehouse on Cloud. You can even migrate your data from an on-premises system.

Loading data from an object store

To load data from Amazon S3 or the IBM Cloud Object Storage, select one of the following methods:
  • From the web console. Load > Amazon S3.
  • For improved performance, the Db2 LOAD command can also be used to load data from Amazon S3 using the following example command:
    CALL SYSPROC.ADMIN_CMD('LOAD FROM "S3::<amazon-s3-URL>::<s3-access-key-id>::<s3-secret-access-key>:
:<s3-bucket-name>::<path-to-data-file>" OF <filetype> <additional-load-options> INTO <table-name>)
    The following is an example usage of the Db2 LOAD command:
    CALL SYSPROC.ADMIN_CMD('load from "S3::s3-us-west-2.amazonaws.com::<s3-access-key-id>:
:<s3-secret-access-key>::ibm-state-store::bdidata2TB/web_site.dat" of DEL modified by codepage=1208 
coldel0x7c WARNINGCOUNT 1000 MESSAGES ON SERVER INSERT into BDINSIGHTS2.web_site ');

    For supported command options. see: LOAD command.

  • External Tables directly. The following is an example SQL statement:
    INSERT INTO <table-name> SELECT * FROM EXTERNAL '<mys3file.txt>' USING
  (CCSID 1208 s3('s3.amazonaws.com', 
  '<S3-access-key-ID>',
  '<S3-secret-access-key>', 
  '<my_bucket>'
     )
  )
    To load data from IBM Cloud Object Storage by using External Tables directly, the following is an example SQL statement:
    INSERT INTO <table-name> SELECT * FROM EXTERNAL '<mys3file.txt>' USING
  (CCSID 1208 s3('s3-api.us-geo.objectstorage.softlayer.net', 
  '<S3-access-key-ID>',
  '<S3-secret-access-key>', 
  '<my_bucket>'
     )
  )
    Note: For IBM Cloud Object Storage, to create HMAC credentials when creating new service credentials, specify {"HMAC:true"} in the Add Inline Configuration Parameters field.

For more information about loading data from a data file on Amazon S3, see: Loading data from a file on Amazon S3.

For a guided demo about loading data from IBM Cloud Object Storage, see: IBM® Db2® Warehouse on Cloud guided demo: Explore data loading.

Migrating data from on-premises system

To migrate your data from an on-premises system, choose one of the following methods depending on the size of your data set:

Lift
Lift is an application that you can use without charge to migrate your data to the IBM Cloud from the various data sources listed in Table 1.
Table 1. Migration data sources for IBM Db2 Warehouse on Cloud
Target database on IBM Cloud Data source
IBM Db2 Warehouse on Cloud IBM Db2
  IBM Db2 Warehouse
  IBM Integrated Analytics System
  IBM PureData® System for Analytics
  Oracle Database
  Microsoft SQL Server
  CSV file format

To download and install Lift, see:Download Lift

For step-by-step instructions about migrating your data to the IBM Cloud by using Lift, see: Migrate data to IBM Db2 Warehouse on Cloud or IBM Db2 on Cloud in 5 minutes.

Tutorial: Migrating data from on-premises relational databases

This tutorial demonstrates how to migrate data from on-premises relational databases into Db2 Warehouse on Cloud for business analytics applications: Hybrid data warehousing with Db2 Warehouse on Cloud