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
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
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.
Database Object Conversion
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 of Packages, Procedures and Functions
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.
Partitioning and Denormalization of tables
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.
Load data using partitioning scheme and test
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.
Develop Near Real-Time processing of Data
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.
Application SQL Conversion
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.
Cut-Over, Rollback Plans and Hypercare
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.
Results and Benefits
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.