How DXC Migrated Db2 Database on AS400 to a Cloud-Native Architecture

By Girish H B, Digital Solution Architect, Hybrid Cloud – DXC Technology
By Farooq Babu, AWS Capability Lead – DXC Technology
By Dhiraj Thakur, Solutions Architect – AWS

DXC Technology

A large global oil and gas company ran its legacy AS400 application in an on-premises data center. Due to the legacy nature of the application and high maintenance cost, the customer decided to move to the cloud as part of its application modernization to reduce cost and improve productivity.

The customer sought increased scalability, security, accessibility, and cost savings with help from DXC Technology, an AWS Premier Tier Services Partner with several AWS Competencies including Mainframe Modernization Consulting and Migration Consulting.

DXC understands the complexities of migrating workloads to Amazon Web Services (AWS) in large-scale environments and helps bridge the gap in migrating applications across traditional, private cloud, and AWS environments.

In this post, explore why it was important for this customer to migrate legacy data to cloud-native services, including storing data for the required 10 years and making it accessible for reporting.

You’ll learn the steps involved in migrating an application from an AS400 system to cloud-native services, including Amazon Simple Storage Service (Amazon S3), AWS Glue, Amazon Athena, and Power BI.

Challenges of an On-Premises System

At the outset of the engagement, the customer was experiencing the following challenges with its AS400 systems:

High maintenance costs: AS400 systems are more expensive to maintain than modern systems due to their age and the specialized nature of the hardware and software.
High hosting costs: AS400 systems are physical servers and must be hosted in a data center or third-party facility. Since the customer was closing their data center facility, these servers needed to be moved to a new, expensive third-party facility.
Lack of skilled personnel: AS400 systems require specialized knowledge and skills. This can make it difficult to find qualified personnel to maintain and upgrade the system.
Limited reporting: The reporting capability of AS400 systems is limited, making it difficult to gain insights from the data.
Lack of modern features: AS400 systems run legacy software, so they lack many of the features and capabilities of modern systems. This can make it difficult to integrate the system with other systems or to use new technologies.

Planning the Migration Approach

The DXC team worked through the requirements, analyzed the challenges, and provided multiple solutions with indicative cost savings. It was decided the legacy application would be migrated in two phases:

First, the application would migrate to a software-as-a-service (SaaS) environment.
Next, the legacy data from the IBM Db2 database on AS400 system would be migrated separately and retained for 10 years as mandated by local law.

Multiple options were discussed for the second phase, including migration to Amazon Relational Database Service (Amazon RDS), cloud-native solutions, or on-premises. Considering the infrequent need to access data, the customer chose a cloud-native solution that’s less expensive and meets their cloud-native application modernization approach.

High-Level Architecture of Cloud-Native Solution

Migrating old application data from an AS400/Db2 system to a cloud-native environment brings many benefits, such as increased scalability, improved security, and lower costs.

Figure 1 – Architecture illustrating cloud-native architecture for the customer.

In Figure 1, you can see a visual illustration of the cloud-native architecture implemented for the customer. The solution consists of Amazon S3, AWS Glue, Amazon Athena, and Power BI.

Amazon S3 is a scalable, high-performance, and low-cost object storage service that allows data to be stored and retrieved. In this case, S3 stores the data extracted from the AS400/Db2 database.
AWS Glue is a fully managed extract, transform, load (ETL) service that enables customers to prepare and load data for analytics. Here, AWS Glue is used to create tables in the data catalog from CSV files (Db2 table extracts) stored in S3.
Amazon Athena is an interactive, serverless query service that allows users to develop and execute SQL-based queries on S3. Here, we used Amazon Athena to query S3 tables stored in CSV files.
Power BI is a SaaS component of Office 365 that allows users to create interactive dashboards, reports, and visualizations from various data sources. In this case, Power BI is being used to connect the Athena data source (Athena SQL queries) and create customer-specific reports.

Implementation Steps to Cloud-Native Services

Next, let’s look at the steps involved in migrating application data from an AS400 system to cloud-native services.

Step 1: Data Extraction

The first step is to extract data from the Db2 database on the AS400 system. This is accomplished using the IBM i-Access Utility, which provides the option of exporting data in several formats, including CSV and XML.

In this customer migration example, the DXC team used a CSV export. Upon exporting the data, it’s uploaded to an Amazon S3 bucket, as shown in Figure 2.

Figure 2 – Data is extracted from AS400 and copied to Amazon S3.

Step 2: Data Processing

To organize extracted Db2 database tables in S3, the DXC team created a main folder containing the name of the Db2 database.

Separate folders for each table were created, maintaining the same naming convention. Next, the CSV file was copied into each folder. Using Amazon Athena, data will be accessed and queried.

After organizing the Db2-exported tables into S3, AWS Glue crawlers are used to automatically discover and categorize the data, creating definitions for each table in the AWS Glue Data Catalog. This allows the data to be easily searched and queried using Athena.

Figure 3 – Data processing and migration from AS400 to Amazon S3.

Step 3: Data Analysis

Amazon Athena allows you to run SQL queries on data stored in S3 using standard SQL, without the need for setup or management of a database.

To write SQL queries in Amazon Athena, you first need to create a table in the Athena Data Catalog that maps to the data stored in S3. This can be done using AWS Glue crawlers, which can automatically discover and classify the data, creating table definitions in the Athena Data Catalog.

Once the table is created, you can run SQL queries on the data using the Athena Query Editor. Queries in Athena are executed against the data stored in S3, so it’s important to optimize the data and partitioning to improve performance.

AWS Glue ETL jobs can be used to clean and prepare the data, as well as to partition and classify it for optimal performance in Athena.

As part of this customer’s migration project, the DXC team rewrote 70 standard SQL queries in Amazon Athena that are used in the AS400 Db2 database. Additionally, views were created for standard reporting using Power BI.

Figure 4 – Data stored in Amazon S3 is accessed by Amazon Athena.

Step 4: Data Reporting

The results of these Amazon Athena queries are then accessed by Power BI for the development of user-friendly reports.

Power BI allows you to create a wide range of visualizations and combine data from multiple tables. It also has a feature called Direct Query, which enables you to create a live connection to Athena. This means dashboards are always up to date with the data stored in S3.

This customer’s migration involved a customer network, so Athena and Power BI SaaS were connected via a Power BI gateway.

As part of this project, DXC developed both standard queries as well as views. The standard queries are used directly in Athena, whereas the views are used in Power BI reports.

By using the Power BI App feature, reports are grouped according to their purpose. With Power BI’s integration with Office 365, business users can access user-friendly reports via their standard single sign-on (SSO) authentication mechanism, which links to application data stored on S3.

Figure 5 – Standard reporting using Microsoft Power BI.

Step 5: Data Testing

Testing is an important step in the process of migrating an old application to cloud-native services. It’s necessary to perform both functional and non-functional testing to verify the application behaves as expected.

In this customer example, DXC compared the data records and queries between the source and the target. In non-functional testing, DXC tested the performance of complex queries containing millions of records.

Customer Results from Cloud-Native Approach

DXC proposed the solution and implemented it by following AWS best practices as well as DXC’s own Migration and Transformation framework.

As a result of this migration from legacy technology to cloud-native services, the customer experienced a number of benefits including:

Cost savings: The overall cost of the customer was reduced from thousands of dollars per month to less than 10 dollars per month.
Improved accessibility: Data can be accessed remotely by end users using standard web interfaces.
Improved reporting: The Power BI environment allows end users to access data through dashboards and a modern interface.
Improved security: In the past, data was accessed using the local server authentication mechanism, but now it has been moved to the organization’s standard SSO process. As a result, access can be managed, monitored, and controlled centrally.
Improved supporting: Getting AS400 skills is difficult today. Moving the application to the cloud allows customers to quickly manage and obtain resources as needed.

Conclusion

Transferring legacy workloads to cloud-native services provides many benefits, such as improved security, scalability, and cost savings. Services like Amazon S3, AWS Glue, and Amazon Athena make it quicker to extract, prepare, analyze, and move data.

In spite of this, migrating a legacy workload is not a simple task and requires careful planning, testing, and execution. During the migration process, it’s important to address the challenges associated with legacy AS400 application and Db2 complex and nested queries with millions of records and endian issues.

The customer use case presented in this post shows how DXC Technology can provide an innovative solution that is simple, intuitive, and cost-effective. Reach out to DXC Migration & Transformation services to modernize your legacy applications to cloud-native solutions.

.

.

DXC Technology – AWS Partner Spotlight

DXC Technology is an AWS Premier Tier Services Partner that understands the complexities of migrating workloads to AWS in large-scale environments, and the skills needed for success.

Contact DXC Technology | Partner Overview | AWS Marketplace | Case Studies