Blogs

Migrate from Oracle or Sybase (insert vendor name here) to DB2 in Weeks

Rewriting code for a new platform? That’s old news.

Ask most DBAs how they feel about migrating applications, and you’ll probably learn some new and colorful adjectives. After all, what could be more fun than opening up a business-critical app and rewriting legacy code so that it works with a new platform? You could follow up with a few joy-filled months of reworking complex, long-standing schemas so that they make sense to a new RDBMS. And then, how about a nice, relaxing trip to the dentist for a root canal?

But wait just a second. If you’re talking about bringing applications from Oracle or Sybase environments to IBM DB2 for Linux, UNIX, and Windows (LUW), it’s time to revise your expectations. DB2 9.7 is compatible with applications and databases developed for Oracle and Sybase at a level that you might not have thought possible.

The release of DB2 9.7 and the introduction of a new DB2 SQL Skin (available in DB2 9.7 Fix Pack 2) for applications compatible with Sybase Adaptive Server Enterprise (ASE) have greatly simplified migration to DB2 from both Oracle and Sybase environments. Migrations can now be accomplished faster, more cost-effectively, and often with little or no application rewriting. It wouldn’t be fair to say that migrating to DB2 from these two platforms is effortless—but it is fair to say that most Oracle and Sybase applications can now be moved to DB2 9.7 with only minor modifications.

Skeptical? Sure you are. So let’s take a closer look at the tools, techniques, and timing of migrating from Oracle and Sybase environments to DB2 9.7. We’ll discuss the key steps in planning and conducting a migration, and then we’ll examine potential hurdles and ways to eliminate them.

Migrating from Oracle to DB2

Previously, moving from Oracle to DB2 meant that the application needed to be ported, or rewritten to account for proprietary SQL, different locking mechanisms, and client interfaces that differed in both semantics and syntax. In DB2 9.7, IBM and EnterpriseDB (an IBM Business Partner) combined their expertise and developed a different approach, offering native support for many commonly used Oracle features, including:

  • Expanded SQL support that incorporates popular keywords and semantics from the Oracle SQL dialect
  • Extended data-type support, including support for the most common nonstandard data types used by Oracle Database
  • Added PL/SQL support to help avoid the translation of procedural language (PL) code
  • Support for many built-in Oracle Database packages to help simplify application migration; DB2 provides many of these packages, including DBMS_OUTPUT, UTL_FILE, DBMS_ALERT, DBMS_PIPE, DBMS_JOB, DBMS_LOB, DBMS_SQL, DBMS_UTILITY, UTL_MAIL, and UTL_SMTP
  • Scripting support offers a SQL*Plus-compatible command-line processor called CLP Plus to help connect databases as well as to define, edit, and run statements, scripts, and commands
  • Enhanced concurrency model enables administrators to adopt the same concurrency behavior as Oracle Database

These capabilities can dramatically reduce the need for manual modifications of application code. To date, IBM has worked with dozens of organizations to migrate millions of lines of code, and IBM DB2 for LUW SQL Architect Serge Rielau is one of the folks who spends much of his time on the migration front lines. “As we work with customers, we are finding that usually between 90 and 99 percent of SQL and PL/SQL statements need no changes,” says Rielau. “On average, the typical organization needs to modify only 2 percent of its code.”

Oracle migration step 1: Planning and assessment

An Oracle migration usually follows a five-step process: migration assessment, database object migration, application migration, solution deployment, and skills transfer and DB2 ramp-up. For now, let’s focus on the first three steps.

The first step is to assess your environment and applications to identify potential roadblocks. If you’re looking for hard data on how well your Oracle databases and applications will play with DB2, you’ll want to download the free IBM Migration Enablement Evaluation Tool (MEET). The tool analyzes Oracle database objects and procedures, and quickly identifies those that use features not supported in DB2 9.7. The tool delivers an HTML report that identifies unsupported code, lists details and source code line numbers, and provides summary statistics (see Figure 1).

Figure 1: IBM MEET scans and identifies Oracle database objects and statements that will run on DB2 without modification, noting any possible incompatibilities.

Evaluating and assessing your infrastructure and database requirements will help you understand the scope of your migration project. In step 3, we’ll look at some of the most common challenges reported by organizations that have already completed migrations.

Oracle migration step 2: Database object migration

The next step is to migrate Oracle database objects to DB2. The native support for Oracle PL/SQL and Oracle SQL provided by DB2 9.7 greatly simplifies this process, as does another resource now freely available: the IBM Data Movement Tool. This tool automatically copies Oracle database objects—including tables, packages, or entire schemas—to DB2.

To use the IBM Data Movement Tool, start it up and connect it to both your Oracle and DB2 databases. Once you are fully connected, you can extract the Data Definition Language (DDL) only, or both the DDL and the data. You’ll likely want to use the tool’s interactive deploy mode, which displays a navigation tree with all the objects extracted from the Oracle database. As the tool copies objects to DB2, it records its progress. If an object does not deploy correctly, the tool highlights the object in the navigation tree; click on the object, and the tool reveals the DDL and the error DB2 encountered, so that you can fix the definition and redeploy. With the tool, data migration is a relatively straightforward and automatic process that should be nearly problem-free.

Oracle migration step 3: Application migration

Generally speaking, DB2 9.7 significantly reduces the need for manual modifications of application code. However, you can’t quite kick back with a cup of coffee and let DB2 do all the work. Here are a few of the places where you’ll probably need to intervene, starting with the most simple and working up to the more complex.

PHP/Perl

Migrating PHP or Perl applications from Oracle to DB2 involves just one alteration: changing the library call from an Oracle library to a DB2 library. Modifying that call should require only a global replacement of the call names in the code using a text editor. The SQL inside the PHP remains unchanged.

Java

Converting Java code is similarly straightforward. The application programming interface (API) itself is well defined and database independent—the database connection logic is encapsulated in standard J2EE DataSource objects. The Oracle- or DB2-specific terms, such as user name or database name, are then configured declaratively within the application.

Converting code requires changing only the Java source code to the appropriate API driver (JDBC or SQLJ), the database connect string, and any incompatible SQL statement. DB2 9.7 also enables you to use Hibernate (an open-source persistence and query service for Java), which is now as easy to use with DB2 as it is with Oracle.1

Oracle Calling Interface

Oracle Calling Interface (OCI) is one of the many programming interfaces used by C/C++ developers to interact with an Oracle database. DB2 9.7 Fix Pack 1 introduced the DB2 Call Interface (DB2CI), which provides compatibility for the OCI—developers will have a familiar interface for both environments.

Oracle Forms

Oracle Forms is a legacy software product used to create data-entry systems for the database. Some organizations have hundreds of Oracle Forms screens, which constitute all or part of an application.

IBM has partnered with Realease to offer Oracle Forms–to–Java conversion capabilities. Realease provides tooling that translates Oracle Forms to Java, preserving the look and feel of the original GUI. In many cases, the translation work can be accomplished in one week rather than months.

Triggers

DB2 does not (yet) allow you to perform updates to tables from within a BEFORE trigger. In most cases, you can use AFTER triggers to perform these actions. Also, DB2 does not yet allow trigger actions to be combined. So if you have a PL/SQL multi-action trigger, you’ll need to copy it into separate DB2 SQL PL triggers, using a Boolean variable for the predicates.

Partition handling

DB2 can be configured to organize data in several ways, including table partitioning, database partitioning, multidimensional clustering, or a combination of these organization schemes. If you used a form of partitioning with Oracle, you may need to update your code to accommodate the differences in syntax (see Figure 2).2

Figure 2: Configuring partitioning in DB2 may require code changes to accommodate differences in syntax.
Third-party dependencies

Some applications have third-party software dependencies written into their code. Those dependencies could be difficult to identify if existing systems have been functioning successfully for years.

The migration assessment process can help you uncover these third-party dependencies and enable you to determine which dependencies need to be altered or eliminated. For some organizations, recoding might be required if the third-party software is no longer available.

Migrating from Sybase to DB2

Spurred by frustration with increasing maintenance and administration costs and a lack of a solid road map from Sybase, many Sybase ASE users are looking for an alternative.

Until recently, proprietary syntax and functionality in Sybase ASE made migrations difficult; you had to invest significant time and money to rewrite application code and then run test cases for the application. With the introduction of DB2 SQL Skin for applications compatible with Sybase ASE, DBAs now can accomplish this migration while minimizing—and in some cases completely avoiding—application coding changes as well as subsequent test-case changes.

Developed jointly by IBM and IBM Business Partner ANTs Software, DB2 SQL Skin allows Transact SQL (T-SQL) code—including queries, functions, triggers, and stored procedures—from Sybase ASE to interact transparently with DB2 with little or no rewriting, recompiling, or re-linking. DB2 SQL Skin provides the features, functions, and data formats required for code from Sybase ASE to run natively on DB2. The only necessary change is resetting connection parameters so the application connects to the DB2 server instead of the Sybase server.

Planning

As with an Oracle migration project, you should begin the migration process by assessing your environment. The assessments will help you understand the work involved and help you decide whether assistance from IBM or an IBM Business Partner would enable you to complete the migration easier, faster, and with better efficiency. Assistance will be particularly useful for organizations that have limited IT resources or numerous applications whose interactions must be coordinated.

During the assessment process, you may opt to modify applications so they run natively on DB2 or decide to use DB2 SQL Skin to run Sybase applications as-is. Because DB2 SQL Skin can simplify the migration process, you could use it during the initial migration and then rewrite some applications later, when your developers become more comfortable with the DB2 SQL dialect.

Execution

Sybase applications can use the same APIs on DB2. With DB2 SQL Skin, many of the behaviors that Sybase applications expect are now provided by DB2. As a result, the application can still run and return the data in the existing format, but it will work with DB2 even though the format is different within the database. All of this data format handling and T-SQL function handling is transparent to the application, which still thinks it is talking to Sybase ASE.

1 For more information on using Hibernate with DB2, read Using Hibernate to Persist Your Java Objects to IBM DB2 Universal Database.

2 For specific syntax changes, see Oracle to DB2 Conversion Guide: Compatibility Made Easy.

Resources

Maybe you can’t drop off your fleet of Oracle and Sybase applications on the DB2 server and have them magically get themselves up and running while you make coffee (well, not yet, anyway). But the native compatibility features built into DB2 9.7 really do handle most of the heavy lifting, and lots of tools and resources are available to smooth out any remaining rough patches.