Database Migration - From Sybase to Oracle - A Case Study

A Case Study in Database Migration
 
Introduction
 
We present one of our modules as case study for database migration. The customer had multiple and heterogeneous data sources on various platforms ranging from Sybase, Oracle to IDMS and DB2 on mainframes. The customer wanted to remove Sybase from their system and move on to Oracle. The case study leads to testing and validation of the concepts, which have been mentioned in our white paper.
 
Scope
 
The customer had a running application with Sybase as the database, they wanted to move all their active data of a particular application to Oracle from Sybase and in the process modify the applications and backend programs associated with the database.
 
Motivation
 
The motivation of the customer to move on to the single database system were as follows,
• Have a single licensing agreement instead of multiple agreements in case of multiple database platforms thereby leading to savings in terms of costs.
• Have single service agreement for support rather than have multiple supports for the various databases.
• Have single structure and architecture of the applications working in conjunction with the database. In case of multiple database the structure had to be such as to support the different databases.
• Easier maintainability of the database and applications as compared to maintaining multiple databases and their related applications.
• Minimize requirement of trained technical personnel to handle multiple database.
 
Existing System Components
 
1. Mainframe Interactions
2. Unix Boxes
3. Customized Generated Reports
4. Sybase Database
5. IIS/MTS 
6. Web Applications
 
 
Technologies/Languages 
 
The following are the various technologies and languages on which various components of the application was running.
• Sybase: Adaptive Sybase Server 11.9 (earlier database)
• Oracle 8.1.6 (target database)
• MVS(Mainframe)
• Sun 5.0
• Microsoft Internet Information Server (IIS)
• Microsoft Transaction Server (MTS) 
• VB 6.0
• Asp
• Bourne Shell scripts
• SQL and PL/SQL
• C programs
 
Earlier System
 
The earlier system was of the following structure:
• Front End – Consisting of ASP deployed on IIS, it exchanged data with the middle tier
• Middle Tier – Developed in VB 6.0 and deployed on MTS, it interacted with the database on one end to retrieve and store data, and also with the front end to display and accept data.
• Backend System – This constituted the major part of the system. It was an amalgamation of COBOL and JCLs running on the mainframe, C program/executables running on the Unix Box, shell scripts on the Unix box serving the automation purpose, Perl scripts running on the Unix box to provide reports and database programming in terms of Stored Procedures and Triggers. 
 
Existing System Specifications
 
During the analysis phase the following information and specifications about the existing Sybase system were gathered.
• Which source Operating system and Hardware platform are used.
• Table structure, constraints, table size etc 
• Indexes and Index size 
• Sybase stored Procedures, Packages, function and triggers with their complexity
• Sybase data types used in the columns
• Database maintenance schedules and associated scripts 
• Associated applications and their processing profile (online, batch etc)
• Any ERP/CRM applications involved (SAP, Siebel etc)
• Any custom code and code profile (language, development and testing) 
• Primary development languages and tools (C, Java, VB, Powerbuilder, etc.) for the developed applications.
• Any Middleware used (like tuxedo or any application server) 
 
Analysis and Design
 
The following points were kept in consideration when the analysis was made.
• Oracle database structure identification vis-à-vis the existing Sybase database.
• Mapping of the Sybase data types to their Oracle Equivalence.
• Mapping of the fields and database objects of the database.
• Database sizing for the target Oracle database. This would help in allocating enough space and setting the proper parameters in the Oracle database.
• Analysis of the data to see if there was any discrepancy, constraint violation etc when moved to the target database.
• Analysis and design of the database objects (Stored procedures and triggers) that had to be converted to Oracle equivalence.
• Design of automation tools (in awk or shell scripts) to aid the data loading and migration.
• Plan to load data and integrate the database into the production environment.
• Test Plan with respect to before image and after image of the system.
 
Migration Strategy
 
The objective was to essentially move/migrate all data and programs associated with the Sybase database to Oracle. The activities that were initially going on in the Sybase system were as follows:
1. Data Loading
2. Modifying the associated backend programs appropriately
3. Modifying the SQL and Stored Procedures associated with the Middle Tier.
4. Migrating the database objects
 
Data Loading
 
In the data loading part, data from the mainframe system was loaded into Sybase database. The initial system was follows.
1. Data in form of flat files were moved from mainframe to Unix Box
2. This data was then appropriately loaded into the required Sybase table structure through C programs/executables.
3. Shell scripts running on the Unix box automating the above mentioned data loading process.
The strategy to move over this process to suit the Oracle system was as follows:
1. Data was loaded into Oracle temporary tables from mainframe through SQL Loader.
2. This data from the Oracle temporary tables was then appropriately loaded into the required Oracle table structure through Oracle Stored Procedures.
3. Shell scripts running on the Unix box automating the above mentioned data loading process.
 
Backend Processing
 
The backend processing, which was going on earlier was as follows:
1. Mainframe JCLs triggering Unix shell scripts through a process called NDM. These shell scripts used to generate flat files with data with a Sybase utility called BCP. These data files were sent back to the mainframe again.
2. Mainframe was sending data files to the Unix box. Mainframe JCLs were triggering Unix shell scripts through a process called NDM. These shell scripts then load the data from the data file to the appropriate Sybase tables.
3. Stored Procedures in Sybase implementing business processes.
4. Perl Programs run from Unix box to generate reports.
Our objective was to move the existing system and program to work with Oracle. So our strategy was to change the system as follows.
1. Mainframe JCLs triggering Unix shell scripts through a process called NDM. These shell scripts generate flat files with data by calling Oracle Stored procedures. These data files are sent back to the mainframe again.
2. Mainframe was sending data files to the Unix box. Mainframe JCLs triggering Unix shell scripts through a process called NDM. These shell scripts call Oracle Stored procedures to load the data from the data file to the appropriate Oracle tables.
3. Stored Procedures in Sybase implementing business processes were changed over to their equivalent Oracle Stored Procedure.
4. Perl Programs run from Unix box to generate reports were discarded, as those reports were no longer required.
 
Middle Tier Processing
 
In the Middle Tier the database activities that were carried out were:
1. SQL queries doing querying or DML operations
2. Stored procedures implementing business rules
Now the conversion was made as follows.
1. The Sybase and Oracle SQL differ in some areas as functions used, joins etc. So essentially those were kept in mind when changing the SQL statement
2. The Sybase Stored Procedures were converted to the corresponding Oracle counterparts.
 
Database Objects
 
The database objects as Sybase Stored procedures and Triggers had to be converted to their Oracle counterparts. The programming language for Sybase and Oracle are different. So the existing Stored Procedures and Triggers had to be implemented in PL/SQL.
 
Challenges and Hurdles
 
While migrating the database and application we faced the following challenges:
• Sybase is lenient about date formats while Oracle is not.
• Date coming from Mainframe to Oracle has to be shaped according to acceptable Oracle Date formats.
• While the process is being automated proper care has to be taken for exception and error handling so that the entire system does not fail.
• Doing proper sizing for the target database (Oracle) was a challenge.
• Difference in the SQLs for Sybase and Oracle.

   Comment    13-06-2017 3 : 50 PM