Motivation

Last March at a Cebit presentation I held on DB2 9.7, I burned out a 110V computer loudspeaker that I had thoughtlessly hooked up to Germany's 230V power grid without using a transformer. On that same trip, I also destroyed the charger for my electric toothbrush and my beard clipper in similar incidents. Putting my apparent inability to learn from mistakes aside, it comes as no surprise that one of my favorite sayings (origin unknown) is: "The problem with standards is that there are so many of them."

In the world of relational database management systems (RDBMS), we are blessed with at least three major standards and countless variations on those:

• ANSI SQL and ANSI SQL/PSM
• Oracle SQL and PL/SQL
• Sybase and Microsoft TSQL

Figure 1 illustrates with a venn diagram how the SQL dialects overlap:

Whenever you write an application, you have to make the choice of which RDBMS vendor to utilize. Once you have made that choice, you are essentially committed. Any attempt to switch vendors to take advantage of lower prices, better technology, or a better partnership is thwarted by legacy code that requires extensive rewrite before it can be used with another RDBMS. In addition, your skill set cannot be transferred from one product to another as easily as you would expect.

IBM DB2 9.7 for Linux, UNIX, and Windows (DB2) seeks to dramatically lower the barriers for applications written for Oracle when enabling them to DB2. This provides customers and vendors the ability to choose a DBMS based on its merits rather than application history.

Back to top

DB2 9.7 adds Oracle compatibility features

To allow an application written for one RDBMS to run on another virtually unchanged, many pieces have to fall into place. Different locking mechanisms, data-types, SQL, procedural language residing on the server, and even the client interfaces used by the application itself need to be aligned not only in syntax, but also in semantics.

All these steps have been taken in DB2. Changes are the exception, not the rule (IBM can rapidly assess the application changes needed). Table 1 provides a quick overview of commonly used features:

With DB2 9.7, you do not need to port an application anymore. You merely enable the application. In the case of a packaged application, it is even possible to share one source for both DB2 and Oracle.

In other words, enabling an Oracle application to DB2 becomes no more complex than enabling a C-Program written for HP-UX to run on AIX.

Back to top

Concurrency control

In the past, one of the most prominent differences between Oracle and DB2 has been the approach to concurrency control. The catchy phrase is: "Readers don't block writers and writers don't block readers."

Without going into detail on isolation levels, suffice it to say that the vast majority of applications that use the Oracle default Statement Level Isolation will work just fine using the DB2 default of Cursor Stability (CS).

Traditionally, however, CS has been implemented so that writers block readers and, in some cases, readers can block writers. The reason for that is that, traditionally, a transaction under CS isolation will "wait for the outcome" of a pending concurrent transaction's changes.

It turns out that there is no semantic reason why a transaction running under CS isolation waits for outcome when encountering a changed row. An equally satisfactory behavior is to read the currently committed version of the changed row.

This behavior has been implemented in DB2 9.7. What happens is that DB2 simply retrieves the currently committed version of a locked row from the log. In most common cases, the row is still in the log buffer because the change has not been committed yet. But even if the row has been written out and has also been overwritten in the log buffer DB2 knows exactly where to find it, so that a single IO will bring the desired version into the bufferpool.

Imagine (see Figure 2) a user updating a name in an employee table. Before that user has committed the change, another user scans that table. Traditionally, the second user would have had to wait for the first user to commit or rollback. Thanks to read currently committed data, the scan for the second user will simply retrieve the version of the row from the log buffer that does not contain the first user's changes.

It is important to note that this behavior:

• Introduces no new objects such as a rollback segment
• Has no performance overhead for the writer since the log needs to be written anyway
• Cannot cause any situation such as a "snapshot too old" because in the extremely unlikely event that the log file needed has been archived (while a transaction was still open!), DB2 will simply fall back and wait for the lock to go away

In addition to these changes, additional lock avoidance techniques have been introduced into DB2 to eliminate a reader holding a lock under CS isolation.

As you can see, the concurrency behavior is now identical to that of Oracle. In fact, newly created DB2 databases exhibit this behavior by default.

Back to top

New data types

The heart of every database is its data. Mismatched types or mismatched semantics of these types can seriously impact the ability to enable an application to another RDBMS. So to allow Oracle applications to run on DB2, it is crucial to support its non-standard basic types, such as strings, dates, and numerics. Beyond aligning these basic types, there are other, more complex types that are commonly used in Oracle's PL/SQL that have been added in DB2 9.7.

Back to top

Implicit casting and type resolution

"If it walks like a duck, and it talks like a duck, then it must be a duck."

This is the mantra of many of the new languages such as PHP and Ruby. Every literal is a string and then gets used as another type based on context. In adherence with the SQL Standard and following a philosophy that a type mismatch is likely an indication of a coding mistake, DB2 has traditionally followed strong typing rules, where strings and numerics cannot be compared unless one is explicitly cast to the other.

Unfortunately, when an Oracle application uses weak typing in its SQL, that application would have previously failed to compile against DB2. In DB2 9.7, implicit casting (or weak typing) has been added. That is, strings and numbers can be compared, assigned, and operated on in a very flexible fashion.

In addition, untyped NULLs can be used in many more places, while untyped parameter markers can be used nearly anywhere, thanks to deferred prepare. That is, DB2 will not resolve the type of a parameter marker until it has seen the first actual value.

To round out implicit casting, DB2 also supports defaulting of procedure parameters as well as the association of arguments to parameters by name.

Back to top

Extended built-in function library in DB2

All RDBMs provide libraries of functions to operate on the data. The problem is that no two use the same names for these functions, even if in the end the functionality is very similar.

In addition to its own traditional set of functions, DB2 now supports a library compatible with Oracle. The following list provides a quick overview, which is by no means complete:

• Conversion and cast functions
  • TO_DATE
  • TO_CHAR
  • TO_CLOB
  • TO_NUMBER
  • TO_TIMESTAMP
  Each of these functions supports a rich set of compatible formatting strings.
• Date arithmetic
  • EXTRACT
  • ADD_MONTHS
  • MONTHS_BETWEEN
  • NEXT_DAY
  • Plus (+) adding fractions of days
• String manipulation
  • LPAD
  • RPAD
  • INSTR
  • INITCAP
  • Extensions to SUBSTR
• Miscellaneous
  • NVL
  • DECODE
  • LEAST
  • GREATEST
  • BITAND

The greatly increased overlap in supported functions between the two products implies a greatly improved out-of-the-box success enabling an Oracle application to DB2.

Back to top

Oracle SQL dialect support

This article, so far, has covered concurrency, data types, typing, and functions. But the differences between Oracle and DB2 go deeper than this. The very fabric of the SQL dialects, their keywords and semantics differ in some areas. Also each product supports some features that the other simply does not. When these features are popular, they limit the ability to submit common SQL against both products. Among the many small and big language tweaks, Table 6 lists some highlights:

This concludes the overview of the changes made to DB2 so that Oracle applications that submit SQL against the database can run largely unchanged. There are, however, major sections of many applications that are executing at the server itself. The server-side language of choice for Oracle applications is PL/SQL. No claim of compatibility could be seriously made without support for PL/SQL.

Back to top

DB2 support for PL/SQL

Commonly, when an application is ported from one product to another, the SQL and procedural language is translated from one SQL dialect to the other. This poses several problems:

• The resulting translated code tends to be convoluted due to automation and impedance mismatch between the source and target dialect.
• The application developers are not familiar with the target SQL language dialect. That makes it hard to debug the ported code. Over time, further maintenance becomes a challenge due to the lack of skills.
• In the case of packaged applications, translation needs to be repeated for every new release of the application.
• In the end, the result is an emulation, which by definition runs slower than the original.

To avoid these issues, DB2 9.7 includes native PL/SQL support. What does this mean?

As you can see in Figure 3, the DB2 engine now includes a PL/SQL compiler side by side with the SQL PL compiler. Both compilers produce virtual machine code for DB2's SQL Unified Runtime Engine. It is important to note that monitoring and development tools such as Optim Development Studio are hooked into DB2 at the runtime engine level.

The integration of PL/SQL into DB2 as a first class procedural language has several implications:

• There is no translation. The source code remains as it is in the schema catalog.
• Developers can continue working in the language they are familiar with. There is no need to move logic to DB2's dialect even if new logic is written in SQL PL. Routines using different dialects can call each other.
• Packaged application vendors can use one source code against both Oracle and DB2.
• Both PL/SQL and SQL PL produce the same virtual machine code for DB2's SQL Unified Runtime Engine. Therefore, by design, both PL/SQL and SQL PL perform at the same speed.
• Since the debugger infrastructure hooks directly into the SQL Unified Runtime Engine, PL/SQL is naturally supported by Optim Development Studio.

Figure 4 illustrates a PL/SQL debugging session. The debugger supports standard features such as step into, step over and breakpoints. In addition, it allows the user to change local PL/SQL variables while the program is running.

PL/SQL syntax details

So what exactly does PL/SQL support imply? First, there is the core syntax support. DB2 supports all the common constructs of PL/SQL, such as:

• if then else
• while loops
• := assignments
• local variables and constants
• #PRAGMA EXCEPTION and exception handling
• Various forms of for loops (range, cursor, and query)
• %TYPE and %ROWTYPE anchoring of variables and parameters to other objects
• #PRAGMA AUTONOMOUS transactions,which allow procedures to execute in a private transaction.

PL/SQL object support

PL/SQL can be used in various different objects that allow procedural logic:

• Scalar functions
• Before each row triggers
• After each row triggers
• Procedures
• Anonymous blocks
• PL/SQL packages

PL/SQL package support

Most PL/SQL in Oracle applications is contained within so-called PACKAGEs. A PL/SQL package—not to be confused with a DB2 package—is a collection of individual objects with the ability to differentiate between externally accessible objects and those that are mere helpers for use within the package. The ANSI SQL equivalent of a package is a MODULE. DB2 now provides support for ANSI SQL modules as well as PL/SQL packages. In particular, the following capabilities are provided:

• CREATE [OR REPLACE] PACKAGE, which defines prototypes for externally visible routines. It also defines all externally visible, non-procedural objects such as variables and types.
• CREATE [OR REPLACE] PACKAGE BODY, which implements all private and public routines as well as all other private objects.
• Within a package or package body, the following objects can be defined:
  • Variables and constants
  • Data types
  • Exceptions
  • Scalar functions
  • Procedures
  • Cursors
• Package initialization
• Public synonyms on packages

Back to top

DB2 provides common built-in packages

Some Oracle applications utilize packages that are provided by the RDBMS. In particular, libraries that provide reporting, e-mail, or cross-connection communication can be popular. To facilitate enablement of these applications to DB2, the packages listed in Table 7 are provided by DB2:

Back to top

Oracle-specific JDBC extensions

JDBC is a standard Java client interface. There are, however, extensions that have been added to Oracle's JDBC driver in order to support specific non-standard data types.

To maximize the level of compatibility for Java technology-based applications, the DB2 9.7 JDBC driver provides, among other things, support for calling procedures with reference cursor and VARRAY parameters.

Back to top

SQL*Plus script support using CLPPlus

Often times DDL scripts and even reports are written using the SQL*Plus command line processor. To make it easier to transfer these scripts as well as the skills of developers writing them, DB2 provides an SQL*Plus-compatible command line processor, called CLPPlus. The tool provides the following functionality:

• SQL*Plus-compatible command options
• Variable substitution
• Column formatting
• Reporting functions
• Control variables

Back to top

Enabling to DB2 is as easy as drag and drop

Given the close alignment of DB2 with PL/SQL and Oracle SQL, there is now no need for a complex migration toolkit. Instead, you can use the IBM Data Movement Tool to simply drag and drop tables, packages, or entire schemas from Oracle onto DB2. Only minimal adjustments are needed on an exception basis to either move an application to DB2, or modify an application so the same source can operate against both DB2 and Oracle.

The steps can be as simple as:

1. Set the necessary registry variables:
   • db2set DB2_COMPATIBILITY_VECTOR=ORA
   • db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES
2. Restart the database manager:
   • db2stop
   • db2start
3. Create an Oracle-compatible database:
   • db2 create database mydb pagesize 32 K
   • db2 update db cfg for mydb using auto_reval deferred_force
4. Start up the IBM Data Movement Tool, and connect to the Oracle and DB2 databases (see Figure 6). Once you are connected, you can choose to extract the DDL only or both DDL and data. Finally, you have two choices: Either you deploy directly by executing the generated scripts, or you continue with the interactive deploy pane. (The latter is recommended for most, non-trivial applications.)
   
   Figure 6. Drag and drop Oracle schemata onto DB2 using IBM Data Movement Tool
   
   
   
5. Move desired schemas from Oracle to DB2 using interactive deploy (see Figure 7). In interactive deploy mode, you see a nav-tree that displays all objects extracted from the Oracle database. Select all objects and execute the deploy menu option. As a result, the tool will copy the objects over to DB2 and record its progress. Some objects may fail to deploy successfully, and the tool gives you the option to work with those. When selecting an object, you will see the DDL along with the error DB2 encountered. Now you can fix the definition as required and redeploy using the built-in editor. The goal is to interactively move all the objects to DB2 on an exception basis.
   
   Figure 7. Drag and drop Oracle schemata onto DB2 using IBM Data Movement Tool
   
   
   

Back to top

Sizing up the enablement to DB2

So how easy will it really be to enable your application to DB2 9.7? The answer is, of course, it depends. IBM has an internal tool named MEET DB2, that can analyze all the objects in your Oracle database and score it. It produces a report of what will work out of the box and where adjustments need to be made. Your IBM account representative or sales contact can run this utility to help quickly provide a compatibility assessment of your current Oracle database with DB2.

During the one year beta phase of DB2 9.7, many applications totaling over 750.000 lines of PL/SQL code have been analyzed in detail with an average out-of-the-box transfer rate of 90%-99%.

Back to top

Restrictions

PL/SQL, the built-in package library, and CLPPlus are presently not available for the DB2 Express, DB2 Express-C, and DB2 Personal Editions.

Back to top

Summary

Thanks to its native multi-dialect SQL support, DB2 9.7 allows for easy enablement of Oracle applications to DB2. Vendors of packaged applications can offer their applications on both Oracle and DB2 at minimal additional cost. Customers can freely choose the vendor that offers the technology they need without being limited by past choices.

To test these features, you can download a trial version of DB2 9.7 (see Resources).

Back to top

Frequently asked questions

Which version of Oracle does DB2 9.7 support?

The coverage provided for the SQL and PL/SQL dialects is strictly based on what is being used by applications. There are features that have been introduced in releases as recent as Oracle 11g that are supported, while some constructs available in Oracle 8i are not supported. In one study of 18 applications totaling over 750,000 lines, 90%-99% of the code moved to DB2 without change. Many of the remaining adjustments can be automated or are otherwise repetitive.

How fast will my Oracle application run on DB2?

That is the million dollar question! Unfortunately, Oracle licensing terms prohibit anyone from publishing benchmarking results without prior written consent. Needless to say, we did not bother to ask. However, by its very design and confirmed by quality assurance benchmarking, an application written against PL/SQL on DB2 is as fast as one written against SQL PL on DB2. Vendors who have gone through the enablement process already are generally pleasantly surprised.

How much work was it to provide these features?

Not nearly as much as one might think. Some initial work, such as CONNECT BY and NUMBER, was done for DB2 9.5 in a tactical fashion. In earnest, the effort was completed in less than 18 months.

What are common complications in enabling from Oracle to DB2?

The compatibility of DB2 is obviously not 100%. So there will likely be some hiccups when you first enable to DB2. Many of these complications, however, are trivial and easy to fix. For example, DB2 supports PL/SQL triggers, but does not allow trigger actions to be combined. That is, one trigger is shared for UPDATE, DELETE, and INSERT actions. Now, given a PL/SQL multi-action trigger, it is pretty easy to copy it into three DB2 PL/SQL triggers using a boolean variable for the INSERTED, UPDATED, and DELETED predicates.

Resources

Learn

• DB2 9.7 launchpad: Get an overview on the values of DB2.
  
  
• Chat With the Lab: Listen to and watch videos on topics such as a DB2 9.7 technical deep dive and a webcast version of this article.
  
  
• "Moving to DB2 is Easy" (youTube): Follow a video demonstration giving an overview of application enablement to DB2.
  
  
• "DB2 9.7 CLPPlus" (youTube): What a video that demonstrates the usage of the SQL*PLus-compatible CLPPlus shell.
  
  
• "DB2 9.7: Native PL/SQL Support" (youTube): Watch a short video that showcases the many PL/SQL features supported by DB2 9.7.
  
  
• "Break Free with IBM DB2 9.7" (youTube): Watch a video of numerous customer and partner testimonials.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• DB2 9.7 for Linux, Unix, and Windows: Download a free trial version of DB2 9.7 for Linux, UNIX, and Windows.
  
  
• DB2 Express-C 9.7: Download DB2 Express-C 9.7, a no-charge version of DB2 Express database server for the community.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the My developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.
  
  

About the author

Serge Rielau has worked in DB2 for Linux, UNIX, and Window's SQL compiler for 12 years. Presently, he is the SQL architect for DB2 for Linux, UNIX, and Windows. He is the responsible architect for SQL compatibility on DB2 9.7. Serge can be found at comp.databases.ibm-db2.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Motivation
• DB2 9.7 adds Oracle compatibility features
• Concurrency control
• New data types
• Implicit casting and type resolution
• Extended built-in function library in DB2
• Oracle SQL dialect support
• DB2 support for PL/SQL
• DB2 provides common built-in packages
• Oracle-specific JDBC extensions
• SQL*Plus script support using CLPPlus
• Enabling to DB2 is as easy as drag and drop
• Sizing up the enablement to DB2
• Restrictions
• Summary
• Frequently asked questions
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers