Tags: AWS Schema Conversion Tool, ETL, Informatica, Java application, SQL Server Integration Services
One of the largest life insurance companies in the United States supported a reporting application. Their system included Java based web app and Informatica data management solution connected to DB2 database. To provide a web interface for reporting needs they employed a WebSphere server for hosting Java application. With this interface, a small circle of users could create reports using data imported from Informatica application.
Our customer wanted to reduce the maintenance costs and increase the application functionality. We came up with a solution to migrate the customer database to SQL Server and reconnect the application to the new database. As a result, the customer has been able to improve the application performance by taking advantage of modern SQL Server capabilities.
Check how we have migrated the application to the SQL Server platform in the following video.
Schema conversion
At first, we had to migrate the customer database from DB2 to the SQL Server platform. We converted the source database schemas using SQL Server Migration Assistant for DB2 (SSMA tool). The major part of the DB2 code was in ANSI SQL standard, so almost all schemas were converted automatically. However, we discovered some code incompatible with direct conversion. To resolve this conversion errors, we had to manually rewrite the code in accordance with SQL Server standard.
Finally, we converted multiple DB2 databases to separate schemas within a single database on SQL Server.
Data migration
Then we migrated data using SQL Server Integration Services. We created SSIS package with imported data from DB2 database flat files. SQL Server Integration Services support two models of deploying packages to the production server: the project deployment model and the legacy package deployment model. On our customer’s request, we used the package deployment model.
Due to inability to use Visual Studio on the customer machine, we deployed the SSIS package using Execute Package Utility (DtExecUI). Once we had completed the database migration, we could upgrade the applications and connect them to the new SQL Server database.
Application remediation
We used AWS Schema Conversion Tool to find the database queries in the Java application code. We modified the code to replace DB2 specific queries with their T-SQL equivalents in order to make the application compatible with SQL Server. After that, we reconnected the WebSphere server to the newly created SQL Server database.
Moreover, we updated the Informatica workflow and provided the customer with converted ETL scripts.
Thus, we have successfully completed this project with validating the migration and performing the functional testing.
The benefits for our customer included:
- Reduced application maintenance costs with the consolidated SQL Server database;
- Obtaining up-to-date, stable, and safe database platform with SQL Server;
- Increasing the application performance by taking advantage of SQL Server performance features and the new hardware.
Feel free to contact us to complete your migration project.
