Migrating from Sybase to DB2, Part 1: Project description

This article describes the processes and techniques used to migrate trigger code from Transact SQL (Sybase) into SQL PL (DB2®). Part 1 describes the intended goal and scope of the project. Part 2 talks about the considerations and challenges we had to overcome to make the database vendor transparent to the application.

Luc Vandaele (luc.vandaele@suadasoft.com), DB2 Specialist, SuadaSoft

Luc VandaeleLuc Vandaele is an IBM Gold Consultant and IBM Information Champion working with DB2 since 1990. He currently contributes to the DB2 community in his role as vice president for IDUG. In his day-to-day job, he enjoys being architect for several implementations, and one of his favorite hobbies is migrating databases to DB2.



16 January 2014

Also available in Russian

Introduction

In early 2013, I had the opportunity to attend the zIM Tech Sales and Sales Bootcamp 2013, hosted in the IBM Silicon Valley Laboratory (SVL). During the opening speech, it was a pleasure to hear that DB2 has been doing well over the past year and that there are great plans for future developments still ahead of us.

Industry-leading performance, scale, and reliability on your choice of platform from Linux®, UNIX®, and Windows® to z/OS® is a slogan that probably sounds familiar to each of us, knowing IBM turns this into reality release after release. Did you know that upwards of 95 percent of the worldwide Fortune 500 companies are running DB2?

If you open an IT magazine these days, you will be exposed to topics ranging from transactional to deep analytics, scoring, big data, social media, and a lot of hardware systems that support and accelerate these techniques. I experienced the same when attending the sessions during the bootcamp. Tremendous efforts are being made to enable DB2 to profit from all this new technology and allow us to interact with our information assets swiftly so we can continuously improve our increasingly complex and dynamic business services and processes.

Every year, Gartner Inc. performs a global analysis of IT spending trends. Key findings from this year's Gartner IT Key Metrics report include:

  • 55 percent of global IT budgets are spent on infrastructure and operations
  • 33 percent of global IT budgets are spent on applications
  • 12 percent of global IT budgets are spent on IT overhead

According to another study, technology is top of mind for CEOs, but 68 percent of IT operation costs will be for management and administration. The last figure is not sustainable toward the future, and that is one of the arguments for this article.

The cost of database administration can greatly exceed the cost of the database software and hardware, so it's critical that IT staff time be used effectively and efficiently. DB2 includes numerous autonomic features that free IT staff from many administration tasks. The other reasons I have heard for migrating away from the current database server are performance, scalability and reliability, license cost, declining technical support from the database vendor, and the customer's or application vendor's conflicting strategic directions.

Today, applications are more consciously written to be agnostic to the database technology so that a switch from one database vendor to another should not be as labor-intensive. These applications should also only require a testing cycle to be confident that both functional and non-functional performances have not been adversely affected. However, there are still many applications that require more effort.

Several IBM Redbooks® publications are available (http://www.redbooks.ibm.com with conversion as the search keyword) and can be helpful in migrating from Sybase, SQL Server, or MySQL to DB2. The latest version of DB2 Linux, UNIX, and Windows includes extensive native support for the PL/SQL procedural language, new data types, scalar functions, improved concurrency, built-in packages, OCI, SQL*Plus, and more. These features will ease developing applications that run on DB2 and Oracle and can help simplify the process of moving from Oracle to DB2 LUW.

In this article, I will discuss a Sybase to DB2 migration project. I will limit the focus to a small but important piece of the project — namely, the translation of the triggers.

To outline the scope of the project, I will show a compatibility report produced using the Sybase DDL of that migration project.


Compatibility report

If you are curious about how compatible your Oracle applications are with DB2, I can refer you to the IBM Database Conversion Workbench (DCW). DCW provides an integrated workbench of tools to help facilitate your conversion project to DB2. Whether converting to DB2 from another RDBMS, or migrating from one version of DB2 to another, DCW provides a simple framework to take you through the conversion process.

DCW provides the following benefits:

  • Common graphical interface with uniform look and feel for all phases of the conversion
  • A consolidated process with well-defined steps based on best practices
  • Faster enablement for client and partner teams in conversion methodology
  • Wizards that guide you through all steps of the conversion

The DCW Evaluate Compatibility wizard provides a report that rates the compatibility of the source SQL code with a target DB2 database. The Technical Summary section of the Compatibility Evaluation Report provides statistics on the number of statements detected and identifies the number of statements that require attention. All incompatibilities found are identified in the report that provides summary information and detailed technical information about each incompatible feature, as well as known workarounds to fix incompatible code.

Note: DCW generates an encrypted report that must be emailed to askDCW@ca.ibm.com, which responds with the decrypted HTML report. No source code is included in the encrypted report. For users with IBM intranet IDs, DCW can authenticate your credentials and automatically generate the decrypted HTML report. Instructions on how to authenticate can be found in the user guides.

The DCW has been available as a plug-in of DataStudio since July 2013. Its predecessor was the MEET DB2 tool, which previously also evaluated the compatibility of your Sybase ASE applications that you possibly would migrate to DB2 and a separately priced feature named SQL Skin. SQL Skin is a layer between the Sybase application and the DB2 engine, in which the Sybase syntax is transformed in DB2 syntax and where the representation of output data can be transformed on the way back to the application. The report below states how compatible the application is for a migration from Sybase to DB2 with SQL Skin.

Executive summary

92.2 percent of statements immediately transferable to IBM DB2 — MEET DB2 has estimated that 92.2 percent of statements and 71.8 percent of objects are immediately transferable to IBM DB2. The technical report below is provided to detail exact instances and locations of potential issues to simplify the migration process.

Figure 1. MEET DB2 technical summary report
Image shows MEET DB2 technical summary report

By clicking detailed technical report, the following list is generated.

Figure 2. MEET DB2 statistics report
Image shows MEET DB2 statistics report

Next in the report is a list of all statements listed as to verify and the number of occurrences.

Figure 3. Statements to verify
Image shows statements to verify

You can click on one of the features flagged, and this is really the added value of the tool. You get the type of error, and the exact occurrence and line number in the DDL were the incompatibility can be cured.

Figure 4. Statement to cure
Image shows statement to cure

After modifying all of the flagged statements, you should be able to use the DDL and create the objects in the new DB2 database.

The estimates from the MEET DB2 tool were especially reliable for Oracle migrations. What I have witnessed myself is that using the SQL Skin layer can be successful in case you want to retire a Sybase application, but implementations in an agile environment are less successful due to the fact that there is not much harmony between developing in Sybase and seizing the benefits from the latest native DB2 features. It really becomes a nightmare if you want to mix procedures and triggers written in T-SQL with those natively written for DB2 in PL/SQL and have them interact. The SQL Skin layer is out of marketing and is deprecated in DataStudio 3.2.

In November 2012, there was a series of developerWorks articles illustrating the process and methodology for migrating from Sybase ASE 12.x/15.x to DB2 9.7.x/DB2 10.1.x. I recommend reading the series if you're considering migrating from Sybase to DB2 or if you want to migrate from any other database to DB2.


Sybase migration project description

The project in which our SuadaSoft team is currently involved has some prerequisites that are easy to define but not as easy to live up to. The look and feel of the application should be as close to Sybase as possible, meaning an extra blank in the result set of a call or statement is a difference that needs to be reported and managed. Another prerequisite is that the time necessary for the code freeze in Sybase has to be as minimal as possible, which limits the number of manual interventions, especially if you take into account the number of objects to translate.

In general, the migration of triggers is less straightforward than the migration of stored procedures due to the difference in the behavior of triggers in Sybase and DB2. You will see in the next paragraph that the trigger code is first translated the same way as we translate the stored procedures and then post-processed.

Project scope: The goal is to migrate more than 40 Sybase databases into one DB2 database as the different applications use multiple database schemas and we want to avoid the extra complexity of setting up homogeneous federated access. There are about 16.000 stored procedures, 3.500 triggers, 4.000 tables, more than 5.000 views, and 7.000 indices representing about 2 million lines of code in the triggers and stored procedures, making a pure manual migration not feasible. We use following translation process to get all the objects translated and created in DB2.

Figure 5. Translation process
Image shows the translation process

We start with a DDL file that contains all table, view, defaults, trigger, and stored procedure definitions, then we split that file into many smaller files that each contain one object per type of objects:

  1. The ITK Translate ANTLR3 (Another Tool for Language Recognition) engine is something we have developed ourselves based on the Migration Toolkit for Sybase from IBM. I'll explain more about the ITK in the next section. All of the objects are translated by the ITK, but for the triggers, we need extra steps.
  2. Then we take the translated objects and process them to make them more readable, and SQL statements are restructured so the different clauses are aligned and spread over several lines. This is important in order to allow developers to easily read the generated code, which is important for maintenance once everything is migrated and in production. Here, we also add extra logic to compensate for functionalities that don't have a direct equivalent in DB2.
  3. I'll cover more about the why we use trigger classifications later. In this step, the already-translated and beautified trigger code is processed by the Trigger Convert Engine. The Trigger Convert Engine splits the triggers in several DB2 before-and-after triggers where necessary and modifies some of the clauses depending on the type of trigger.
  4. Security is processed in a fourth step. An important issue the team encountered is that in Sybase, user names can have a length of 30, whereas in DB2, a standard length of only eight is supported. We solved this by using a security exit in DB2 and a login proc.

At the end of step one, we get a report that identifies the number of successfully translated objects, and at the end of step three, we get the number of objects successfully created in DB2. DBAs already suspect that we have to pause between step one and step three to create the tables, indices, and views before we can actually create the triggers and stored procedures. In the weeks following the first run, we don't have to recreate the tables as the DDL for tables doesn't change that often. The reporting we do allows us to calculate our compatibility level just like the MEET DB2 tool does. Many Friday evenings, I have waited to get the new figures, and it was awesome to see the translation figures going to 100 percent.

ITK translation process

Figure 6. ITK translation process
Image shows ITK translation Process

The above figure depicts the ITK translation process. The Parser (2) reads the DDL split by the Splitter (1) process. It builds an Abstract Syntax Tree (3) (AST). This tree represents the language structure of the Sybase DDL. The Walker (4) "walks" this tree and translates the elements into DB2 DDL matching the AST to the string templates. When errors occur in the translation, additional files are generated to provide more detail on the failures (5). Successfully translated DDL is listed in an output CSV file (6).


Conclusion

In this article have found information about the general project scope and overall translation process for all DDL and SQL routines. It sets the stage for Part 2 of this series, in which we will describe considerations and challenges of the trigger translation process.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=959900
ArticleTitle=Migrating from Sybase to DB2, Part 1: Project description
publish-date=01162014