Migrating Db2 data through Amazon S3 to Amazon RDS for Db2 - Amazon Relational Database Service
Saving your data to Amazon S3Loading your data into RDS for Db2 tables

Migrating Db2 data through Amazon S3 to Amazon RDS for Db2

With this migration approach, you first save data from a single table into a data file that you place in an Amazon S3 bucket. Then, you use the LOAD command to load the data from that data file into a table in your Amazon RDS for Db2 database. For more information about using Amazon S3, see Integrating an Amazon RDS for Db2 DB instance with Amazon S3.

Saving your data to Amazon S3

To save data from a single table to Amazon S3, use a database utility to extract the data from your database management system (DBMS) into a CSV file. Then, upload the data file to Amazon S3.

For storing data files on Amazon S3, you need the following AWS components:

Loading your data into RDS for Db2 tables

After you save your data files to Amazon S3, you can load the data from these files into individual tables on your RDS for Db2 DB instance.

To load your Db2 table data into your RDS for Db2 DB database table

  1. Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.

    db2 connect to rdsadmin user master_username using master_password

  2. Catalog a storage access alias that points to the Amazon S3 bucket where your saved files are stored. Take note of the name of this alias for use in the next step. You only need to perform this step once if you plan to load multiple tables from data files stored in the same Amazon S3 bucket.

    The following example catalogs an alias named my_s3_alias that grants a user named jorge_souza access to a bucket named amzn-s3-demo-bucket.

    db2 "call rdsadmin.catalog_storage_access(?, 'my_s3_alias', 'amzn-s3-demo-bucket', 'USER', 'jorge_souza')"

    For more information about this stored procedure, See rdsadmin.catalog_storage_access.

  3. Run the LOAD command using the storage access alias that points to your Amazon S3 bucket.

    Note

    If the LOAD command returns an error, then you might need to create a VPC gateway endpoint for Amazon S3 and add outbound rules to the security group. For more information, see File I/O error.

    The following example loads data from a data file named my_s3_datafile.csv into a table named my_db2_table. The example assumes that the data file is in the Amazon S3 bucket that the alias named my_s3_alias points to.

    db2 "load from db2remote://my_s3_alias//my_s3_datafile.csv of DEL insert into my_db2_table";

    The following example loads LOBs from a data file named my_table1_export.ixf into a table named my_db2_table. The example assumes that the data file is in the Amazon S3 bucket that the alias named my_s3_alias points to.

    db2 "call sysproc.admin_cmd('load from "db2remote://my_s3_alias//my_table1_export.ixf" of ixf
        lobs from "db2remote://my_s3_alias//" xml from "db2remote://my_s3_alias//"
        modified by lobsinfile implicitlyhiddeninclude identityoverride generatedoverride periodoverride transactionidoverride
        messages on server
        replace into "my_schema"."my_db2_table"
                               nonrecoverable
        indexing mode incremental allow no access')"

    Repeat this step for each data file in the Amazon S3 bucket that you want to load into a table in your RDS for Db2 DB instance.

    For more information about the LOAD command, see LOAD command.