Best Practices for Migrating to SAP Sybase ASE –Implementing application migration

[former_member197516]

The Migration Roadmap so far ....

1. Planning the migration
2. Designing architecture migration
3. Implementing schema migration
4. Implementing data migration
-> Implementing application migration
6. Implementing operation migration
7. Validating the migration

This is the fifth installment for best practices for migrating to SAP SYBASE ASE server.  Regardless of which database vendor you are moving from, the steps outlined below will form the overall guide for our talk.  We are now onto the topic of implementing the data movement other to the target database server.    We will also target our talk mainly to the non-SAP Applications. 

The story so far: we have our logins created, the database (or databases) created and the tables and indexes created and data and all compiled code is on the target ASE Server.  The backend is done and we need to focus on the Client side of the house.

While the Migration Roadmap with the steps from 1 to 7 are roughly based on chronological order, implementing application migration, or we should say the "discovery and conversion" portion of implementing application migration should be started right after the "designing architecture migration" step.   Once we have the schema or database mappings complete, we have a starting point on how the SQL statements on the Client side need to be structured.

Client Application Types

Lets divide the Client Applications into two camps:  Third-party Applications (you are not the developer) and In-house (you are the developer) Applications.  Both Application Clients have their pros and cons when we are considering a database layer migration. 

Third-party Applications migrations

If you have bought an Application and wish to convert connectivity to the database layer, the best and only course of action is to consult with the Company that originally developed the Application and inquire if they offer support for linking a different set of APIs to connect to the SAP Sybase database.  That seems quite easy, at least on your end.  However,  we have a chicken and egg situation here:  API conversion is a small part of the conversion process for an application.   Due to different architectures, the structure of the database(s) found on the database server might be suitably different.   If the Application Vendor has done their job right, they will either have a conversion tool on the database layer that must be run to convert their database layer objects into the new format or they will enforce strict rules as to how the new structure should look.  Regardless of the assumptions and changes we have made previously in the migration process (refer to the previous talk: Designing architecture migration), for Third-party Application support, we are constrained in our design by the rules and processes outlined by  the Third-party Application Vendor.   Being proactive for a database migration is to recognize this situation early on in the process and explore the migration options required by the Application Vendor.

In-house Applications migrations

We will assume that you have full control of the source code.   When we look at what must change at the Application side we have two areas of study.  The first area is the code that communicates or makes direct SQL calls inside the Application (aka Embedded SQL)  and the other area of study is the "how" (Call-level interface) of the equation.   Typically we will see the majority being embedded SQL code and some call-level interfaces surrounding connectivity.

Info Sidebar: Embedded SQL

Embedded SQL programs are SQL statements written into an application program written in a conventional programming language.   Once written, a preprocessing routine  parses the  application program with its Embedded SQL code and the end result is a program that the host language compiler can read and compile. These types of programs are linked with SAP Sybase's Open Client Library.  All Embedded SQL statements must begin with the keywords "EXEC SQL" and end with a semicolon (;).

Info Sidebar: Call-level Interface
In this programming style one places Client-Library calls into an application program in-line code and then links with the SAP Sybase Client-Library.  For the purposes of this discussion we will assume ODBC/JDBC connectivity.

In undertaking efforts to understand the In-house Applications our basic needs in this area are: location of the Application source code and tools (manual and automated) that facilitate the searching of keywords that need to be changed from the source database SQL syntax to SAP Sybase ASE SQL syntax.  Searching for these keywords is the first step, the next step is to judge the amount of effort required (delivered as part of the migration planning stage done by SAP Global Database Services) followed by the physical changing of the code itself; whether that be by automated or manual means.   For example, embedded SQL is recognized by parsing the source files for the "EXEC SQL" keyword.  The same code changes we featured in earlier migration talks still apply to the Client code changes.

Conversion of In-house Application code can be further divided into three migration areas of study:

1.  SQL syntax  changed manually resulting in a "re-written" source file.

To act as an aid to manually convert Source code into SAP Sybase SQL syntax, we introduce the concept of Templates.  A Template is a design construct that  documents the code conversion process and delivers a “how” for the conversion of the SQL fragment being studied.  Templates are an ideal method to either hand off the effort to a design team for migration automation or to give direction to  a specific code conversion team.   Best practices for Template designs are delivered as part of the migration planning stage done by SAP Global Database Services.  The typical Code Migration Template (initially done as part of the project planning documentation) would have:

Typical Template fields	Field description
Source Vendor	Oracle/MSoft/Other….
Source Vendor Version	When this code fragment came into being.  If a syntax feature came about in later releases we do not want to search for it in earlier releases of the source vendor syntax.
Source Pattern	Keywords in the syntax that would allow embedded source code recognition.  This is one of the first steps to automating this template.  If we needed placeholders we use the "######" symbol to denote placeholders for varied syntax.  Only searchable and unique keywords are entered.
Target Pattern	The keywords re-written in SAP Sybase SQL syntax.  A Developer should be able to read the source and make syntax changes to the target SQL code.  This is the second step in the automation process.
Automated	Y/N answer.  This will indicate if the template is a still manual process or if the template has advanced enough to be automated.  Template advancement is part of the overall migration project plan.
Target Flag	Every code fragment either automated 100% or by a certain percentage of automation, needs to have a “Flag” associated with it and this is denoted as a “ –“ comment line  immediately before the automated change in the source file.  This Flag serves as a reminder to follow up with a manual check or the code migration effort needs further manual refinement.  Flags are unique to the template.

2.  SQL syntax changed automatically by using third-party conversion tools resulting in a "re-written" source file.

Some third-party conversion tools are WinSQL , SwissSQL, to name a few.   This code conversion at the Client or Application level should not be confused with the conversions done at the Database level; while they are similar in some of the code constructs the environments will be different.   Part of the migration planning process done by SAP Global Database Services is to recognize which tool is applicable to automating your environment.    While templates can be used to detail the planning of such automation in this area it should be pointed out that no tool will convert "code from one database vendor to another" 100%.

3.  SQL syntax not changed by using a wrapper API to convert logic in the code "on the fly" from a Source vendor to SAP Sybase.

As an example only for wrapper designs, SwissSQL features an API JDBC wrapper that sits between the Application and the source database Vendor JDBC driver.  This product will convert JDBC calls meant for the source database into SAP Sybase JDBC calls.  Obviously we are making the assumption that the source and target architectures somewhat mimic each other and the nature of the calls being made are approaching ANSI SQL standards.   Selecting the right wrapper and the appropriate use of any wrapper technology will be part of the migration planning process done by SAP Global Database Services.

Stay tuned for the next topic where I will focus on the next step in our migration high-level steps:  Implementing operations migration, basically taking what you have currently for database supporting operations and morphing these features  into the SAP Sybase world.