AWS Prescriptive Guidance
Patterns

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

R Type :RePlatform

source :Databases: Relational

target :Aurora PostgreSQL

tags :ibm db2, amazon aurora postgresql, aws dms, aws sct

categories :Independent Software Vendor, LifeCycle, Pattern Provenance, Pattern Type, Software Infrastructure

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 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. 

AWS DMS and AWS SCT support several types of source and target databases. For details, see Sources for Data Migration and Targets for Data Migration in the AWS DMS documentation.

Assumptions and Prerequisites

 Prerequisites

  • An active AWS account 

  • A source IBM Db2 database on an EC2 instance

Architecture

Source technology stack 

  • DB2/LINUXX8664 (version 11.1.4.4 and later) on an EC2 instance  

Target technology stack

  • An Aurora PostgreSQL DB instance

Source and target architecture 

Tools Used

Aurora PostgreSQL - Amazon Aurora is a relational database service that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases. The PostgreSQL-compatible edition of Aurora delivers up to three times the throughput of standard PostgreSQL running on the same hardware, which enables your existing PostgreSQL applications and tools to run without requiring modification. 

 

AWS DMS - AWS 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. 

 

DB2/LINUXX8664 11.1.4.4 on Amazon EC2- IBM Db2on Cloud is a cloud database built for robust performance and provides a high availability option. You can scale up or down as needed, and apply rolling security updates. It is flexible, highly available, and consistently secure. 

 

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

Migrate

Tasks

Title Description Skills Predecessor
Create an Aurora PostgreSQL DB instance. The Aurora PostgreSQL DB instance should be in the same virtual private cloud (VPC) as the source IBM Db2 database. Amazon RDS Migration process analysis and description - Create an IBM Db2 database on an EC2 instance using an IBM Db2 AMI
Create a replication instance and endpoints for IBM Db2. You must create separate endpoints for the source and target databases. For Aurora PostgreSQL, 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 DB instance. This replication instance must be in the same VPC as the source and target DB instances. AWS DMS Migration process analysis and description - Create an Aurora PostgreSQL instance
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 database. AWS DMS Migration process analysis and description - Create a replication instance and endpoints for IBM Db2
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 Migration process analysis and description - Create an AWS DMS task to fully load the source IBM Db2 tables to target PostgreSQL schema

References and Help

References

Tutorials and videos

Contact and help 

Migration Pattern Library Support: aws-mpl@amazon.com