Migrate Db2 z/OS to Amazon Aurora PostgreSQL using AWS DMS and gateway server

Organizations running critical workloads on IBM Db2 databases hosted on z/OS mainframes often look to modernize their data infrastructure by migrating to open source, cloud-based databases such as Amazon Aurora PostgreSQL-Compatible Edition. Migrating to Amazon Aurora can help reduce licensing costs, improve scalability, and align with modern DevOps practices. However, migrating from Db2 on z/OS, especially with periodic full-load refresh requirements, poses unique challenges because of architectural and format differences.

To address these challenges, AWS provides a flexible migration architecture using AWS Database Migration Service (AWS DMS) in combination with a Db2 gateway server deployed on Amazon Elastic Compute Cloud (Amazon EC2). Organizations can use this setup to copy data from their on-premises Db2 on z/OS environment and replicate it on a scheduled basis to Aurora PostgreSQL-Compatible on AWS.

In this post, we show you how to use AWS DMS and a Db2 gateway server to migrate and replicate data from an on-premises Db2 z/OS database to Aurora PostgreSQL. The architecture includes an EC2-based gateway server that serves as a bridge between the mainframe and AWS. AWS DMS handles both the full load and the periodic full-load refresh. We walk you through the setup steps, highlight important configuration options, and use a sample dataset to validate data consistency and periodic full-load refresh synchronization.

Solution overview

This solution supports secure, scalable modernization of Db2 on z/OS workloads by migrating to Aurora PostgreSQL, keeping data synchronized with periodic full-load refresh, and using AWS DMS with a Db2 Connect Server on EC2.

The implementation of this solution consists of the following steps using various AWS services and components:

1. Set up an EC2 instance in a private subnet with secure connectivity to the on-premises Db2 z/OS system using a VPN or AWS Direct Connect. This EC2 instance acts as a Db2 gateway server.
2. Install and configure IBM Db2 gateway server on the EC2 instance to establish connectivity with the Db2 z/OS database.
3. Create an AWS DMS replication instance, source endpoint, and target endpoint, using the EC2 gateway server as a bridge to connect to the Db2 source and Aurora PostgreSQL as the target. For more information, see Choosing the right AWS DMS replication instance for your migration.
4. Create an AWS DMS migration task to perform a full load followed by periodic full-load refresh from Db2 to Aurora PostgreSQL.
5. Validate migration by running queries to compare row counts, schema objects, and other changes between source and target databases.

The following figure illustrates the data migration architecture with source on-premises Db2 on z/OS, Amazon EC2 based Db2 Connect Server, AWS DMS for data replication, and target Aurora PostgreSQL database cluster.

Figure 1: Data migration architecture from on-premises Db2 on z/OS to Amazon Aurora PostgreSQL using AWS DMS and an EC2 gateway server

Prerequisites

Before initiating the migration from Db2 on z/OS to Aurora PostgreSQL, make sure that the following AWS infrastructure and configurations are already in place with suggested versions:

1. An active AWS account with all required permissions.
2. Aurora PostgreSQL cluster (version 17.4).
3. AWS DMS (version 3.6.1).
4. EC2 instance with Amazon Linux 2023.
5. IBM Db2 Connect Server install file (version 11.5.9, valid IBM Db2 ODBC connect license required).
6. On-premises Db2 database on z/OS with necessary firewall rules configured.
7. Secure and encrypted on-premises infrastructure to AWS connectivity either using AWS Direct Connect to an AWS VPN or similar method. Also, we are using AWS Key Management Service (AWS KMS) with AWS DMS, Aurora PostgreSQL database and Amazon Elastic Block Store (Amazon EBS) volumes for robust encryption at rest.
8. Create an AWS Identity and Access Management (IAM) role for AWS Lambda to use later with this IAM policy:

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "DMSTaskAccess",
         "Effect": "Allow",
         "Action": [
           "dms:StartReplicationTask",
           "dms:DescribeReplicationTasks"
         ],
         "Resource": "arn:aws:dms:<region>:<account-id>:task:<replication-task-id>"
       },
       {
         "Sid": "CloudWatchLogsCreateGroup",
         "Effect": "Allow",
         "Action": "logs:CreateLogGroup",
         "Resource": "arn:aws:logs:<region>:<account-id>:*"
       },
       {
         "Sid": "CloudWatchLogsPutEvents",
         "Effect": "Allow",
         "Action": [
           "logs:CreateLogStream",
           "logs:PutLogEvents"
         ],
         "Resource": "arn:aws:logs:<region>:<account-id>:log-group:/aws/lambda/<lambda-function-name>:*"
       },
       {
         "Sid": "SNSPublishNotifications",
         "Effect": "Allow",
         "Action": "sns:Publish",
         "Resource": "arn:aws:sns:<region>:<account-id>:<sns-topic-name>"
       }
     ]
   }

EC2 instance

In this setup, the EC2 instance functions as a Db2 gateway server, acting as a secure gateway between the AWS environment and the on-premises Db2 database on z/OS system. This Amazon EC2 hosted gateway handles the protocol translation and communication with the mainframe. It hosts the IBM Db2 Connect client, which supports JDBC- or ODBC-based communication with the mainframe so that AWS DMS can pull data securely through this Amazon EC2 intermediary. This setup is essential because AWS DMS doesn’t inherently support direct connectivity to Db2 on z/OS.

Make sure you have an EC2 instance in place with Amazon Linux 2023 and that you have validated connectivity to it.

Install and configure IBM Db2 Connect Server

To access the EC2 instance, follow these steps:

1. On the Amazon EC2 console, choose Connect.
2. Choose Session Manager.
3. Choose Connect.

To install and configure IBM Db2 Connect Server, follow these steps:

1. Install required dependencies.
   1. Update base packages:

      dnf update -y
      sudo dnf install -y libxcrypt-compat
      dnf install -y wget unzip tar nfs-utils lvm2 glibc libstdc++ ksh pam libaio amazon-ssm-agent
      echo "Red Hat Enterprise Linux Server release 8.4 (Ootpa)" | sudo tee /etc/redhat-release
      sudo mount -o remount,size=1G /tmp

   2. Enable and start AWS Systems Manager Agent (SSM Agent):

      sudo systemctl enable amazon-ssm-agent
      sudo systemctl start amazon-ssm-agent

   3. Mount a 6 GB tmpfs:

      sudo mkdir -p /mnt/tmpfs
      sudo mount -t tmpfs -o size=6G tmpfs /mnt/tmpfs
      echo "tmpfs /mnt/tmpfs tmpfs defaults,size=6G 0 0" >> /etc/fstab

   4. Format and mount an Amazon EBS volume:

      sudo mkfs -t xfs /dev/xvdf
      sudo mkdir -p /data
      sudo mount /dev/xvdf /data
      echo "/dev/xvdf /data xfs defaults 0 0" >> /etc/fstab

   5. Install required dependencies:

      dnf groupinstall "Development Tools" -y
      dnf install -y libaio libaio-devel pam openssl ksh compat-openssl10 libxcrypt-compat perl-core zlib-devel cmake3 git curl

   6. Create redhat-release and expand /tmp:

      echo "Red Hat Enterprise Linux Server release 8.4 (Ootpa)" > /etc/redhat-release
      sudo mount -o remount,size=3G /tmp
      echo "tmpfs /tmp tmpfs defaults,size=1G 0 0" >> /etc/fstab

   7. Download the Db2 installer file (Customer needs valid IBM Db2 ODBC connect license to download this file from the vendor) from Amazon Simple Storage Service (Amazon S3).

      Customers must obtain the licensed Db2 Connect binary from IBM and stage it. AWS does not host or distribute the IBM binary.

      aws s3 cp s3://<your-s3-bucket>/<db2-installer>.tar.gz /tmp/

2. Extract the Db2 installer:

   tar -xvzf <db2-installer>.tar.gz
   cd /tmp/server_dec

3. Run the installer while bypassing the OS precheck.
   1. Run install:

      ./db2_install -f sysreq || true

   2. If you experience issues around the missing OpenSSL 1.1.1 dependency:

      curl -LO https://www.openssl.org/source/openssl-1.1.1s.tar.gz
      tar -xzf openssl-1.1.1s.tar.gz
      cd openssl-1.1.1s
      ./config --prefix=/usr/local/openssl11 --openssldir=/usr/local/openssl11 shared zlib
      make -j$(nproc)
      make install
      ls -l /usr/local/openssl11/lib/libcrypto.so.1.1
      ls -l /usr/local/openssl11/lib/libssl.so.1.1
      sudo ln -sf /usr/local/openssl11/lib/libcrypto.so.1.1 /usr/lib64/libcrypto.so.1.1
      sudo ln -sf /usr/local/openssl11/lib/libssl.so.1.1 /usr/lib64/libssl.so.1.1
      ls -l /usr/lib64/libcrypto.so.1.1
      ls -l /usr/lib64/libssl.so.1.1
      ldconfig

   3. Rerun Db2 install:

      cd /data/
      tar -xvf special_59605_v11.5.9_linuxx64_server_dec.tar
      cd /data/server_dec
      ./db2_install -f sysreq <<EOF
      yes
      yes
      SERVER
      no
      EOF

After successful installation, perform the following steps on that instance.

4. Create Db2 Instance users and groups.
   1. Add the Db2 port to services:

      echo "db2c_db2inst1 4472/tcp" >> /etc/services

   2. Create Db2 users and groups:

      groupadd db2iadm1
      groupadd db2fadm1
      groupadd db2asgrp
      useradd -g db2iadm1 -m -d /home/db2inst1 db2inst1
      useradd -g db2fadm1 -m -d /home/db2fenc1 db2fenc1
      useradd -g db2asgrp -m -d /home/dasusr1 dasusr1

5. Create the Db2 instance.
   1. Create the Db2 instance:

      /opt/ibm/db2/V11.5/instance/db2icrt -u db2fenc1 db2inst1

6. Configure the Db2 instance for TCP/IP.
   1. Switch to the Db2 instance user:

      su - db2inst1

   2. Enable TCP/IP:

      db2set DB2COMM=TCPIP

   3. Set service port to 4472 (or your preferred port):

      db2 update dbm cfg using SVCENAME 4472

   4. Verify settings:

      db2 get dbm cfg | grep SVCENAME
      db2set -all

7. Add service entry to /etc/services:

   sudo bash -c 'echo "db2c_db2inst1 4472/tcp" >> /etc/services'

8. Start the Db2 instance:

   su - db2inst1 -c "db2start"

   1. Verify port listening:

      netstat -an | grep 4472

   2. You should see a line like:

      tcp 0 0 0.0.0.0:4472 0.0.0.0:* LISTEN

9. Adjust firewall and security groups. In the AWS console, update your Amazon EC2 security group to allow inbound TCP 4472 from your AWS DMS replication instance.
10. Catalog the backend Db2 database if acting as a gateway.

    Example:

    db2 catalog tcpip node MDB2 remote <IP Address of your DB2 host> 4472
    db2 catalog dcs database MDB2 as MDB2
    db2 catalog database MDB2 as MDB2 at node MDB2 authentication dcs
    db2 connect to MDB2 user <your user ID> using <password>

Figure 2: Successful Db2 connection to the MDB2 database from the gateway server

Troubleshooting gateway server installation

Use the following guidance if you run into similar issues with your gateway installation on EC2:

• If you get any library issues, verify your OS has all the needed compatibility libraries installed.
• If you see /tmp size errors during installation, override TMPDIR:

  mkdir /home/ec2-user/tmp
  export TMPDIR=/home/ec2-user/tmp
  sudo ./db2_install -f sysreq

Create AWS DMS resources

In this section, we show you how to prepare the AWS DMS components, including the replication instance and source and target endpoints. Consider rightsizing the replication instance according to your data volume.

Prerequisites

Before you begin make sure the following is in place:

1. VPC with on-premises connectivity using AWS Direct Connect or VPN.
2. EC2 data connect gateway.
3. Aurora PostgreSQL instance deployed and reachable.
4. Security group rule to allow port 4472 (Db2) and 5432 (PostgreSQL).
5. AWS Identity and Access Management (IAM) role for AWS DMS is configured.
6. Database credentials stored in AWS Secrets Manager or readily available for manual entry.

To create an AWS DMS replication instance, follow these steps:

1. On the AWS DMS console, choose Replication instances.
2. Choose Create replication instance.
3. Enter the following:
   1. Name: cbc-db2-postgresql
   2. Instance class: Choose based on your workload
   3. VPC: Select the same VPC where your EC2 gateway and Aurora PostgreSQL exist
   4. Multi-Availability Zone (AZ): Optional, recommended for production
   5. Storage: The minimum is 100 GB, but you can adjust based on your workload

Leave the defaults for maintenance and logs unless your use case requires something different. Wait for the instance to become available.

The source endpoint connects to your on-premises Db2 server. To create a source endpoint, follow these steps:

1. On the AWS DMS console, choose Endpoints.
2. Choose Create endpoint.
3. Enter the following:
   1. Endpoint type: Source
   2. Endpoint identifier: db2-test-src-ep
   3. Source engine: ibm-db2 on z/OS
   4. Server name: Private IP or hostname of Db2 server
   5. Port: 4472
   6. Database name: MDB2 (your source Db2 database name)
   7. Username and password: Choose from Secrets Manager or enter them manually
   8. SSL mode: This is required and recommended for production
   9. (Optional) Extra connection attributes:

      currentSchema=SCHEMA_NAME;retrieveEntireLob=true;

After the endpoint is created successfully, test the endpoint connection using the replication instance cbc-db2-postgresql.

The target endpoint connects to your Aurora PostgreSQL database. To create a target endpoint, follow these steps:

1. On the AWS DMS console, choose Endpoints.
2. Choose Create endpoint.
3. Enter the following:
   1. Endpoint type: Target
   2. Endpoint identifier: rates-target-ep
   3. Target engine: aurora-postgresql
   4. Server name: Aurora writer endpoint (for example, aurora-db.cluster-xxxxxxxx.us-east-2.rds.amazonaws.com)
   5. Port: 5432
   6. Database name: Target database name (for example, rates_db)
   7. Username and password: Use Secrets Manager or enter them manually
   8. SSL mode: This is required and recommended for production
   9. (Optional) Extra connection attributes:

      maxFileSize=512;

After the endpoint is created successfully, test the endpoint connection using the replication instance cbc-db2-postgresql.

Create an AWS DMS migration task

When migrating data from IBM Db2 to Aurora PostgreSQL, AWS DMS offers a scalable and secure approach. This guide details the step-by-step process to create a DMS migration task after setting up your replication instance and endpoints with appropriate large binary object (LOB) settings and optimized table mapping.

Keep in mind that Full LOB mode is slower than limited mode and strictly bounded by Db2 z/OS limits rather than supporting unlimited sizes. Make sure your environment is ready with these appropriate LOB settings and optimized table mapping (limitations when using Db2 on z/OS as a source for AWS DMS), then follow these steps:

1. On the AWS DMS console, choose Create task.
2. Define the task settings:
   1. Task identifier: cca-rates-db2-postgresql-poc
   2. Replication instance: Select cbc-db2-postgresql
   3. Source endpoint: db2-test-src-ep
   4. Target endpoint: rates-target-ep
   5. Migration type: Choose Migrate existing data
   6. Enable Start Task on Create
3. To configure task settings, under Task settings, enter the following:
   1. Target table preparation mode: Truncate
   2. Include LOB columns in replication: Full LOB mode
   3. Enable Logging
   4. Control table settings: Use the defaults or configure to meet your requirements
   5. Table mapping rules: Provide your table mapping from source to target.
   6. Task tags: Optional. Add tags as needed.

Choose Create task. After it is created successfully, start the task.

Validate migration

After you complete the preparations and the AWS DMS migration task has run successfully, you can validate the data migration.

1. Make sure you are connected to the right source server on-premises:

   SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1;

2. Record the total number of rows from the source tables:

   Run these SQL statements against your source database and expected row counts are shown next to each statement.

   SELECT COUNT(*) FROM CMDB2.FSRPRC_PRICE_STRAT; --1302
   SELECT COUNT(*) FROM CMDB2.MSMTRN_MSM_TRANCD; --247
   SELECT COUNT(*) FROM CMDB2.POSTRAN_POS_TRANCD; --2,392

The following four images confirm the source database name and row counts for the preceding SQL statements.

Figure 3: Source server confirmed as MDB2

Figure 4: Source row count for CMDB2.FSRPRC_PRICE_STRAT is 1,302 rows

Figure 5: Source row count for CMDB2.MSMTRN_MSM_TRANCD is 247 rows

Figure 6: Source row count for CMDB2.POSTRN_POS_TRANCD is 2,392 rows

3. Connect to your target Aurora PostgreSQL database from pgAdmin. Record the total number of rows from the matching target tables:

   Run these SQL statements against your target database before starting the DMS full-load task and expected row counts are shown next to each statement.

   SELECT COUNT(*) FROM REF.FSR_PRICING_STRATEGY; --0
   SELECT COUNT(*) FROM REF.MERCHANT_TRANSACTION; --0
   SELECT COUNT(*) FROM REF.POS_TRANSACTION; --0

The following image confirms that the target tables contain 0 rows before the full-load task runs.

Figure 7: Target Amazon Aurora PostgreSQL row counts of 0 before the full-load task runs

To invoke the AWS DMS database migration task, follow these steps:

1. On the AWS DMS console, choose Database migration tasks.
2. Choose cca-rates-db2-postgresql-poc, as shown in the following screenshot.

Figure 8: Database migration tasks in the AWS DMS console

3. Under Actions, choose Restart/Resume.

To monitor the task, follow these steps:

1. In the Database migration tasks section, select cca-rates-db2-postgresql-poc task.
2. Go to monitor task status, then table statistics tab.

In the Table statistics section under Load state, confirm that each table has a status of Table completed and check for warnings or errors, as shown in the following screenshot.

Figure 9: Table statistics showing all three tables completed with matching row counts

After the task is completed successfully, reconnect to your target Aurora PostgreSQL database from pgAdmin and enter the following SQL command:

SELECT COUNT(*) FROM REF.FSR_PRICING_STRATEGY; --1302
SELECT COUNT(*) FROM REF.MERCHANT_TRANSACTION; --247
SELECT COUNT(*) FROM REF.POS_TRANSACTION; --2,392

Figure 10: Target Amazon Aurora PostgreSQL row counts matching the source after the full-load task

Confirm that the target table row counts match those of the source database, AWS DMS migration task, and the target PostgreSQL database. This confirms the migration task is complete and all the associated rows migrated successfully.

Automate the AWS DMS task (optional)

Automating your AWS DMS migration task with AWS Lambda keeps your target Aurora PostgreSQL tables refreshed and in sync with the source system. Use that function to start this AWS DMS migration task at a scheduled time or repeat it according to your schedule. Follow these steps:

1. On the Lambda console, choose Create function.
2. Choose Author from scratch.
3. Enter the following:
   1. Function name: StartDMSTask
   2. Runtime: Python 3.13
   3. Architecture: x86_64
4. Under Permissions, change Execution Role to the role you created earlier and leave the other sections as the defaults.
5. Choose Create function.
6. After the function is created, select the name and on the Code tab, add the following Python code:

   import boto3
   import os
   import time
   
   dms = boto3.client('dms')
   sns = boto3.client('sns')
   
   def lambda_handler(event, context):
       task_arn = os.environ['DMS_TASK_ARN']
       sns_topic = os.environ['SNS_TOPIC_ARN']
       try:
           # Start task
           dms.start_replication_task(
               ReplicationTaskArn=task_arn,
               StartReplicationTaskType='reload-target'
           )
           start_msg = f"DMS task STARTED: {task_arn}"
           sns.publish(TopicArn=sns_topic, Message=start_msg, Subject="DMS Task Started")
           # Poll for completion
           while True:
               response = dms.describe_replication_tasks(Filters=[{
                   'Name': 'replication-task-arn',
                   'Values': [task_arn]
               }])
               status = response['ReplicationTasks'][0]['Status']
               if status in ['stopped', 'failed', 'error', 'ready']:
                   break
               time.sleep(30)
           final_msg = f"DMS task COMPLETED with status: {status.upper()} for {task_arn}"
           if status in ['failed', 'error']:
               final_msg = f"DMS task FAILED with status: {status.upper()} for {task_arn}"
           sns.publish(TopicArn=sns_topic, Message=final_msg, Subject="DMS Task Completion")
           return {'statusCode': 200, 'body': final_msg}
       except Exception as e:
           fail_msg = f"ERROR: {str(e)}"
           sns.publish(TopicArn=sns_topic, Message=fail_msg, Subject="DMS Task Failed to Start")
           return {'statusCode': 500, 'body': fail_msg}

7. On the Amazon EventBridge console, choose Rules.
8. Choose Create rule.
9. In the Rule detail section, enter the following:
   1. Rule-name: dms-start-schedule
   2. Description: Migration Test POC
   3. Event Bus Name: Default
   4. Type: Schedule standard
10. In the Specify schedule details section, enter the following:
    1. Schedule name: cca-rates-dms-db2-postgresql
    2. Schedule description: POC migration task automation test
    3. Schedule group: Default
11. In the Schedule pattern section, enter the following:
    1. Occurrence: Recurring schedule
    2. TimeZone: America/New York
    3. Schedule type: Cron based schedule
    4. Cron expression: 0 10 * * ? *
    5. Flexible time window: 5 minutes
12. In the Select target section, choose AWS Lambda.
13. On the list, select your StartDMSTask Lambda function that you created previously.
14. Choose Next.
15. (Optional) On the Setting page, enable Schedule enable then perform the following:

1. Action after completion: None
2. Retry policy: None
3. Permissions: Create a new role for this schedule

16. Review your settings and choose Create schedule.
17. (Optional) You can configure an Amazon Simple Notification Service (Amazon SNS) notification to receive email notifications when the AWS DMS task starts and upon completion.

Troubleshooting

Use the following guidance if you come across some common issues for this migration:

1. Data Connect Gateway and inability to connect to the source Db2 database (valid customer owned IBM Db2 ODBC connect license required)

   [IBM][CLI Driver] SQL1598N An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968

   Obtain your permanent activation license file using your valid license from the vendor

   db2start: error while loading shared libraries: libcrypto.so.1.1: cannot open shared object file: No such file or directory

   Make sure to install all pre-requisites including setting environment variables

2. DMS task is interrupted.

   DMS task in stopped state

   check DMS task log file and resolve the outstanding issue if any and restart the task

3. DMS endpoints issues.

   Unable to connect to the source or target endpoint

   check end point configuration and provide correct details for required fields

4. Check these pre-requisites and limitations when using IBM Db2 for z/OS as a source for AWS DMS.
5. For any issues on the EC2 Db2 Connect gateway instance, the customer owns issues related to the OS, driver, software configuration, and patching, which are outside of AWS DMS support. The EC2 gateway OS and Db2 Connect are customer owned, and you should confirm the supported OS versions with IBM.

Costs

There is a cost associated with using this solution because it uses various AWS services, for example, an Aurora PostgreSQL database, an S3 bucket, AWS DMS, and Lambda. Make sure to visit AWS Pricing for additional clarity before deploying this solution and make yourself familiar with AWS Pricing Calculator.

Clean up

If you decide that you no longer want to keep the solution and infrastructure components, follow these steps to remove the resources. On the AWS console, select and remove the resources in this order:

1. Amazon EventBridge rules.
2. Lambda function.
3. AWS DMS migration task, source endpoint, target endpoint, and replication instance.
4. EC2 instance.
5. Aurora PostgreSQL cluster.
6. All the associated IAM roles and policies.

Conclusion

In this post, we outlined a complete solution for migrating a Db2 database on z/OS to an Aurora PostgreSQL database using AWS DMS and an EC2 instance configured as a Db2 gateway server. The approach supports both initial full-load migrations and recurring refreshes of the target database using scheduled AWS DMS tasks triggered using Lambda.

To further enhance automation and repeatability, this solution can be extended using AWS CloudFormation or HashiCorp Terraform.

If you’re interested in a related mainframe modernization and migration approach that uses AWS DMS to migrate data from Db2 LUW to Aurora PostgreSQL-Compatible, refer to Using IBM Db2 for Linux, Unix, and Windows database (Db2 LUW) as a source for AWS DMS.

If you have questions or feedback, leave a comment in the comments section.

About the author