In 2021, our client requested Autoverse to modernize their IBM DB2 Database to AWS Redshift. This database system provided critical marketing research which captured customer data experience by recording all customer views and actions. The solution provided valuable customer behavior experience, exposed unknown issues in digital experience and lead their customers to take data driven actions. The current database solution was an IBM DB2 OLTP database that held over 10 TB of data and continually ingested data coming from an on-premise Hadoop MapReduce process which reduced and summarized initial generated data. The current database was unable to scale and could not keep up with data ingestion requirements that occurred on Black Friday and analytic processing requested by their customers. These performance issues caused digital experience problems for their own customers and limited them from acquiring new customers due to each new account consuming database resources. Autoverse was tasked with three goals for this project: Refactor their IBM DB2 OLTP database to AWS Redshift OLAP, tune and optimize AWS Redshift for application access patterns and develop a near-real time re-entrant data ingestion process that could keep up with the volume of data produced on Black Friday.

It was very clear at the onset of this project that the current database system and architecture mismatched the processing requirements of the application. They were using a transactional processing system to run analytical queries and needed to move to data warehouse OLAP architecture. AWS Redshift provides a managed data warehouse in the cloud and offers scaling, cluster management and deep integration with other AWS services and was an ideal service to refactor their current database system to. This was a double heterogeneous refactor, the first refactor being from IBM DB2 to AWS Redshift and the second from OLTP to OLAP processing.

Our next challenge was to optimize and tune AWS Redshift for its specific use by the application. Multiple levels of optimization were addressed, from how data is laid on disk to re-writing queries.

Our final challenge was to develop a near real-time data ingestion process that had minimal impact on production tables, is re-entrant and could handle data ingestion requirements experienced on Black Friday.

Our initial step was to assess all the objects in the database to determine the difficulty migrating to AWS Redshift. Objects such as tables, indexes, triggers and sequences are generally easily converted to the new platform. Other objects such as packages, procedures, functions will require manual conversion and testing. DB2 database features need to be checked for AWS Redshift equivalent, and we needed to determine which components of the application need to be modified in order to support the new database platform.

Once the assessment is complete, issues and delays identified, database objects are converted and created in the target system. We create a baseline for objects using a number of tools that save time and cost. Difficult features such as packages, procedures and functions are not included in this step. A load of data is then completed to determine any errors or issues in our object conversion.

When all baseline objects are created, manual conversion of procedures, packages, and functions begins. Full testing of these functions are completed with test data and run on both on-premise and AWS systems. Outputs from both systems are compared to validate the conversion.

Our next step is to convert the database architecture from OLTP to OLAP. Understanding table access patterns, determination of fact and dimension tables and table sizes are used to create an initial partitioning scheme. Generally, the largest dimension table associated with the fact table is used to determine partitioning keys and tables with small record counts are copied to each partition. The partitioning scheme will create greater co-location of data when accessed by queries. Co-location of data decreases internodal traffic and pushes each partition to process more data. Co-location significantly improves performance by:

Decreases internodal traffic.
Pushes each partition to process more data, distributing resource consumption.
Increases load scalability.

Datasets are then loaded into Redshift using the table and partition scheme that was created and partitions are checked for an even distribution of data. Application queries are collected and tested on both on-premise and AWS Redshift systems. Query response times are compared and slow queries/high cost queries are explained to determine root cause of delay. If the explained query output shows large table queues being passed amongst the nodes, the partitioning scheme is adjusted, data is reloaded and queries retested. When the partitioning scheme was hardened the application team was requested to start their load testing. The requirements of this project was to have faster query response times and be able to handle the database load experienced on Black Friday with minimal impact. Test results produced by the application team were successful and provided evidence that performance SLA requirements were met.

Once partitioning scheme and query response time met SLAs, the near real-time data ingestion process was developed. This process ingested summarized/reduced data produced from AWS EMR, performed aggregation and merged aggregated results into AWS Redshift historical tables. The process started with AWS EMR creating new files which landed in AWS S3. AWS CloudWatch events would trigger an AWS Step Function that moved newly created AWS EMR files to an AWS S3 bucket utilized by AWS Redshift Spectrum. AWS Redshift Spectrum tables were then used to load data into temp tables created in AWS Redshift. Once data was loaded to AWS Redshift, ETL processing began to aggregate and merge data into historical tables. This ingestion process had to run under 2 minutes in order to provide their customers with recent analytical data. With some ingenuity, processing changes and SQL query tuning we were able to meet these requirements. Our process was batch orientated which allowed for a greater volume of data to be inserted, aggregated and merged within a single transaction.

Our final task was to assist the application team to convert application DB2 SQL queries to AWS Redshift SQL. At this point, many of the queries were already converted for testing; however, there are always queries not in our tests that need to be converted.

After the application team modified all SQL queries to AWS Redshift SQL and completed full testing of their application, cut-over and rollback procedures were hardened and scheduled. Cut-over plans are followed for the development environment and improved on with each execution, allowing for a seamless production cut-over.

This double heterogenous refactor from IBM DB2 OLTP to AWS Redshift OLAP transformed the way our customer did business. All requirements and SLAs for this project were met and exceeded our customer’s expectations. This refactor fundamentally changed all methods of database operational activities to better suit the needs of the application. Huge performance gains were achieved by moving to multi-parallel processing architecture and distributing data based on application access patterns. With AWS Redshift, load on the database could increase significantly with no adverse impact on performance, offering a consistent digital experience for our client’s customers and allowing our client to freely grow their customer base without worrying about a performance impact. Application performance on Black Friday was consistent with any other day in the year. Best of all, our client was now on an AWS managed service and was able to reduce their overall administrative burden and staff headcount.