Migrate from IBM Db2 on Amazon EC2 to Aurora PostgreSQL using AWS DMS and AWS SCT - AWS Prescriptive Guidance

Migrate from IBM Db2 on Amazon EC2 to Aurora PostgreSQL using AWS DMS and AWS SCT

Created by Sirsendu Halder (AWS)

R Type: Re-architect

Source: Databases: Relational

Target: Aurora PostgreSQL

Created by: AWS

Environment: PoC or pilot

Technologies: Databases; Migration

Workload: IBM

AWS services: Amazon Aurora

Summary

This pattern provides guidance for migrating an IBM Db2 database on an Amazon Elastic Compute Cloud (Amazon EC2) instance to an Amazon Aurora PostgreSQL-Compatible Edition DB instance. This pattern uses AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT) for data migration and schema conversion.

The pattern targets an online migration strategy with little or no downtime for a multi-terabyte IBM Db2 database that has a high number of transactions. We recommend that you convert the columns in primary keys (PKs) and foreign keys (FKs) with the data type NUMERIC to INT or BIGINT in PostgreSQL for better performance. 

Prerequisites and limitations

Prerequisites

  • An active AWS account 

  • A source IBM Db2 database on an EC2 instance

Product versions

  • DB2/LINUXX8664 version 11.1.4.4 and later

Architecture

Source technology stack 

  • A Db2 database on an EC2 instance  

Target technology stack

  • An Aurora PostgreSQL-Compatible DB instance

Database migration architecture 

Tools

  • AWS DMSAWS Database Migration Service (AWS DMS) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. You can use AWS DMS to migrate your data to and from the most widely used commercial and open-source databases. AWS DMS supports heterogeneous migrations between different database platforms, such as IBM Db2 to Aurora PostgreSQL-Compatible. For details, see Sources for Data Migration and Targets for Data Migration in the AWS DMS documentation.

  • AWS SCT AWS Schema Conversion Tool (AWS SCT) makes heterogeneous database migrations predictable by automatically converting the source database schema and a majority of the database code objects, including views, stored procedures, and functions, to a format that's compatible with the target database. Any objects that are not automatically converted are clearly marked so that they can be manually converted to complete the migration. AWS SCT can also scan the application source code for embedded SQL statements and convert them. 

Epics

TaskDescriptionSkills required
Create an Aurora PostgreSQL-Compatible DB instance.

The Aurora PostgreSQL-Compatible DB instance should be in the same virtual private cloud (VPC) as the source IBM Db2 database.

Amazon RDS
Create a replication instance and endpoints for IBM Db2.

You must create separate endpoints for the source and target databases. For Aurora PostgreSQL-Compatible, the port will be 5432, and you'll need to get the server name from the DB instance’s endpoint. For the source database, the server name must be the public DNS of the EC2 instance where Db2 is running. For user name, you can use db2inst1 followed by the port, which will be 5000 for IBM Db2. You then create a replication instance. For the VPC security group, select both the EC2 instance for Db2 and the Aurora PostgreSQL-Compatible DB instance. This replication instance must be in the same VPC as the source and target DB instances.

AWS DMS
Create an AWS DMS task to fully load the source IBM Db2 tables to the target PostgreSQL schema.

For source and target, use the source and destination endpoint names. The type can be full load. For the schema rule, you can use the Db2 database’s “inst1” schema. For the table name, specify “%” to migrate all tables. When the load is complete, you’ll see the Db2 tables of the “inst1” schema appearing in the Aurora PostgreSQL-Compatible database.

AWS DMS
Use AWS SCT to migrate all other objects.

Configure AWS SCT to connect to the source and target DB instances. In AWS SCT, select the views, indexes, procedures, and functions from the “inst1” schema in the source Db2 database, to migrate them to the target database.

AWS SCT

References

Tutorials and videos