With the release of v6.3 version of Summit, Finastra has stopped supporting Sybase as a core database. Clients who are currently using Sybase should plan their transition to other databases such as Oracle if they intend to upgrade their Summit versions in the near future.
As a result of this, one of our clients decided to migrate its Summit database from Sybase to Oracle. GreenPoint undertook the conversion of their current Sybase stored procedure to Oracle.
In this article, we describe the challenges that the client faced and how GreenPoint migrated their stored procedures from Sybase to Oracle.
Please note that in the following description we do not delve into comparing the nuances of Oracle and Sybase as both the databases have their unique characteristics.
Migration to Oracle from Sybase can be Conceptualized as follows:
- The existing Sybase database schema will be replicated on Oracle.
- All attendant Sybase software artifacts will be converted to Oracle, including Stored Procedures, Triggers, and Functions.
- All existing data should be seamlessly and accurately migrated from Sybase to Oracle.
The following diagram shows how the migration will occur:
Summit Supported Achieving this Migration with Minimal Disruption
Summit provides an off-the-shelf application programming interface (API) called “sSybResultList” that executes queries, stored procedures, and other SQL statements, and produces tabular results. Clients use this API to create periodic reports.
However, some features of this API are limited across Sybase and Oracle:
Table 1
As a result, after a database has been migrated from Sybase to Oracle, using this API in the code base leads to errors in the program causing the execution to fail. This has a significant impact on the
transfer of the entire application.
In general, clients do not use complex stored procedures with unions and multiple selects. However, the client executable using the API sSybResultList along with multiple select unions failed to contact an Oracle stored procedure. If the institution is unable to find a solution, the migration project will be interrupted, putting its operations at risk.
GreenPoint Resolved this Complexity
We created and implemented a custom API with the capability to execute stored procedures in the
Oracle database, with the following built-in features:
- Drop-in replacement for standard API.
- Generate execution results in tabular form.
- Support for large data volumes in minimal execution time.
Drop-in Replacement for Standard API
- Simple initialization Interface
Custom API uses database connectivity details from the environment and establishes a database connection without any additional parameters.
- Like-to-like parameters
Input parameters for Custom API are kept as ‘standard ’, which means minimal changes in the code base to replace the core API.
Generate Execution Results in Tabular Form
The execution result in tabular form is one of the most important features of standard API and has been implemented to provide a seamless transition.
Support for Large Data Volumes in Minimal Execution Time
This is the most critical feature required for this Custom API wherein:
- Custom API provides an option for a user to customize the number of records to be fetched during execution.
- Custom API has a default number set, based on the volumes, and the user can change this parameter to improve system performance.
Fixing Performance Issues
During implementation there was a performance issue while retrieving the data. GreenPoint’s team addressed the problem and resolved the underlying hurdles. The initial performance metrics of this API in a client environment without any inbuilt optimization are represented in Table 2.
Table 2
This performance bottleneck was resolved by using the PREFETCH option in Oracle OCI and introducing a configurable parameter to change the value of the fetch size.
This improved the system performance multifold as described below as compared to the processing
time needed by the old API for fetching the data.
Table 3
GreenPoint’s team addressed this challenge by creating a bespoke API that performed the same
functions as the core API. This enabled us to support the clients, for executing their Oracle migration
effectively.
