![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
- Read
this article in our interactive digital edition format!
- Subscribe to
IBM Data Management magazine
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.
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 Table 1).(2)
Table 1. Configuring partitioning in DB2 may require code changes to accommodate differences in syntax
| Oracle partitioning | DB2 data organization | Oracle 10g syntax | DB2 9 syntax |
|---|---|---|---|
| No equivalent | Round robin | None | Default: occurs automatically on single partition database |
| Range partitioning | Table partitioning | PARTITION BY RANGE | PARTITION BY RANGE |
| Hash partitioning | Database partitioning | PARTITION BY HASH | DISTRIBUTE BY HASH |
| List partitioning | Table partitioning with generated column | PARTITION BY LIST | PARTITION BY RANGE |
| Composite partitioning: hash-rangehash-list | Combination of: database partitioning, table partitioning, multi-dimensional clustering | PARTITION BY RANGE, SUBPARTITION BY HASH, SUBPARTITION BY LIST | DISTRIBUTE BY HASH, PARTITION BY RANGE, ORGANIZE BY DIMENSIONS |
| No equivalent | Multidimensional clustering | None | ORGANIZE BY DIMENSIONS |
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.
Many commonly used concepts and much of the terminology varies between the Oracle and DB2 worlds. We asked Ted Alexander of The Praxium Group to share a few of his hot-button terms, as found in the IBM Redbook Oracle to DB2 Conversion Guide: Compatibility Made Easy.
Table 2. Oracle and DB2 Terminology
| Oracle term | DB2 term | Description |
|---|---|---|
| Oracle Parallel | DB2 Enterprise DPF | Support server partitioning |
| Oracle Gateway | DB2 Connect | DRDA access to hosts |
| Instance | Instance | Processes and shared memory; in DB2, it also includes a permanent directory structure. An instance is usually created at install time (or can be later) and must exist before a database can be created. A DB2 instance is also known as the database manager (DBM). A DB2 instance can have multiple databases, but an Oracle instance can have only one database. |
| Database | Database | Physical structure containing data: in Oracle, multiple instances can use the same database, and an instance can connect to one and only one database; in DB2, multiple databases can be created and used concurrently in the same instance. |
| Control files and .ora files | DBM and database configuration files | In Oracle, these are files that name the locations of files making up the database and provide configuration values. In DB2, each instance/DBM and each database has its own set of configuration parameters stored in a binary file. There are also other internal files and directories; none are manually edited. |
| Table spaces | Table spaces | Contains actual database data |
| Data files | Containers | Entities inside the table spaces |
| Segments | Objects | Entities inside the containers/data files |
| Extents | Extents | Entities inside the objects/segments |
| Data blocks | Pages | Smallest storage entity in the storage model |
| Database link | Federated system | In Oracle, this is an object that describes a path from one database to another. In DB2, a federated system is used. One database is chosen as the federated database and within it wrappers, servers, nicknames, and other optional objects are created to define how to access the other databases (including Oracle databases) and objects in them. Once an application is connected to the federated database, it can access all authorized objects in the federated system. |
| Clusters | N/A | A data structure that allows related data to be stored together on disk. This data can be table or hash clusters. The closest facility to this in DB2 is a clustering index, which causes rows inserted into a table to be placed physically close to the rows for which the key values of this index are in the same range. |
| Data dictionary | System catalog | Metadata of the database |
| N/A | SMS | System-managed table space |
Migrating from Sybase ASE 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.
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.
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. |
| Partner Resources | |
|---|---|
| IBM Information On Demand Virtual 2010 | IBM Server and Storage |
| Quest Software | Safari Books Online |
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
