DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows

DB2 10 gives applications developed on Oracle a bear hug with PL/SQL support, and more

IBM® DB2® 10 for Linux®, UNIX®, and Windows® has out-of-the-box support for Oracle's SQL and PL/SQL dialects. This allows many applications written against Oracle to execute against DB2 virtually unchanged. In this article, get a high-level overview of what Oracle compatibility means in DB2. Whether you want to switch your custom application to DB2 or extend your DBMS vendor support to DB2, now is your time. [2013 8 May: This article has been updated to include descriptions of the latest new capabilities in DB2 10.5]

Share:

Serge Rielau (srielau@ca.ibm.com), SQL Architect, STSM, IBM

Serge Rielau photoSerge Rielau has worked in DB2 for Linux, UNIX, and Window's SQL compiler for 15 years. For many years he has been the SQL architect for DB2 for Linux, UNIX, and Windows, having been the responsible architect for SQL compatibility on DB2 9.7 to DB2 10.5. Serge is now Chief Architect of IBM OpenPages where he is committed to eating his own cooking. Using the technology described in this article, OpenPages 6.2.1 shipped April 30, 2013 for the first time on DB2. Serge can be found at SQLTips4DB2.com.



08 May 2013 (First published 23 July 2009)

Also available in Chinese Russian Vietnamese Portuguese Spanish

Motivation

Develop skills on this topic

This content is part of a progressive knowledge path for advancing your skills. See DB2 fundamentals for Oracle professionals: Migrating from Oracle to DB2

In March 2009 at a Cebit presentation I held on DB2, 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. Given my inability to learn from mistakes, it comes as no surprise that one of my favorite sayings (origin unknown) is this: "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.

Figure 1. Babylonian confusion of SQL
Image shows circles of overlapping standards

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® 10 for Linux®, UNIX®, and Windows® (DB2) dramatically lowers 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.

DB2 10 includes 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 of these steps have been taken in DB2. Changes are the exception, not the rule (you can rapidly assess the application changes needed). Table 1 provides a quick overview of commonly used features.

Table 1. Commonly used features
Oracle-->DB2
Concurrency control-->Native support
SQL dialect-->Native support
PL/SQL-->Native support
PL/SQL packages-->Native support
Built-in packages-->Native support
JDBC client with extensions-->Native support
OCI client applications-->Native support
Pro*C applications-->Native support
SQL*Plus scripts-->Native support
Oracle Forms-->Automated conversion to Java™ technology
RAC-->pureScale®

With DB2, you do not need to port an application. You merely enable the application. In the case of a packaged application, it is even possible to share one source for 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.


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." Table 2 shows the concurrency behavior for Oracle.

Table 2. Oracle concurrency behavior
Pending transactionBehaviorNew transaction
ReaderDoes not blockReader
ReaderDoes not blockWriter
WriterDoes not blockReader
WriterBlocksWriter

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, 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. Table 3 shows the concurrency behavior with CS.

Table 3. Traditional DB2 concurrency behavior with CS
Pending transactionBehaviorNew transaction
ReaderDoes not blockReader
ReaderRarely blocksWriter
WriterBlocksReader
WriterBlocksWriter

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 since 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.

As shown in Figure 2, imagine 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 which that does not contain the first user's changes.

Figure 2. Writers don't block readers
Shows user 1 updating a value, while User 2 does a select of the table

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. Table 4 shows the new concurrency behavior DB2 has with CS.

Table 4. New DB2 concurrency behavior with CS
Pending transactionBehaviorNew transaction
ReaderDoes not blockReader
ReaderDoes not blockWriter
WriterDoes not blockReader
WriterBlocksWriter

As you can see, the concurrency behavior is now identical to that of Oracle. In fact, any DB2 database created since DB2 9.7 exhibits this behavior by default.


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 are available since DB2 9.7, as shown in Table 5.

Table 5. New DB2 data types
Data typeDescription
NUMBERSupport for NUMBER and NUMBER(p [, s]) has been added based on DECFLOAT (with Power hardware acceleration) and DECIMAL.
VARCHAR2The VARCHAR2-type behavior includes empty strings being interpreted as NULL and trailing blank sensitive collation.
NCHAR, NVARCHAR2, and NCLOBThe National Character types provide double-byte string support for Unicode databases.
Oracle DATEA database in Oracle DATE mode includes the TIME component in addition to the calendar date.
TIMESTAMP(n)The scale of fractions of seconds can be chosen between 0 (date + time) up to 12 (pico seconds).
BOOLEANThis type can be used in procedural logic, variables, and parameters to routines.
VARRAYThe ARRAY support in procedures has been extended to support VARRAY style methods and syntax.
INDEX BYIn addition to regular arrays, DB2 also supports associative arrays.
ROW TYPEThis composite type can be used in variables and parameters, and as an element to arrays and associative arrays.
Ref Cursor typeCursors can be assigned to variables or passed around using parameters.

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. Since DB2 9.7, implicit casting (or weak typing) has been the default. 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 routine parameters as well as the association of arguments to parameters by name.

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, but it is by no means an exhaustive list:

  • Conversion and cast functions
    • TO_DATE
    • TO_CHAR
    • TO_CLOB
    • TO_NUMBER
    • TO_SINGLE_BYTE
    • 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
    • INITCAP
    • INSTR
    • INSTRB
    • INSTR2
    • INSTR4
    • LENGTHB
    • LENGTH2
    • LENGTH4
    • LISTAGG
    • LPAD
    • LTRIM
    • RPAD
    • RTRIM
    • SUBSTRB
    • SUBSTR2
    • SUBSTR4
    • Extensions to SUBSTR
  • Miscellaneous
    • NVL
    • NVL2
    • HEXTORAW
    • DECODE
    • LEAST
    • GREATEST
    • BITAND
    • RATIO_TO_REPORT
    • Native NUMBER support for MOD

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.


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, which can prompt many small and big language tweaks. Table 6 lists some highlights.

Table 6. New SQL support
FeatureDescription
CONNECT BY recursionDB2, so far, has supported the ANSI SQL recursion. Now, Oracle-style CONNECT BY has been added, including the various functions and pseudo columns such as LEVEL and CONNECT_BY_PATH.
(+) join syntaxThis syntax is actually discouraged even by Oracle, but there are numerous applications and developers who still use this form of OUTER JOIN syntax.
DUAL tableA single-row, single-column table used pervasively in Oracle applications as a dummy.
ROWNUM pseudo columnThis pseudo column is typically used to limit the number of rows returned and to enumerate rows in a result set.
ROWID pseudo columnRowids are used to quickly retrieve a row that was previously fetched based on its physical address.
MINUS SQL operatorIn Oracle, MINUS is used instead of EXCEPT to subtract one result set from another.
SELECT INTO FOR UPDATEThe FOR UPDATE construct on SELECT INTO allows extraction of a row from DB2 with the intent to later modify it without using a cursor.
PUBLIC SYNONYMA public synonym is an alias with out a schema name. DB2 supports public synonyms for table objects, sequences, and PL/SQL packages.
@DBLINK syntaxUsing a table reference of the form schema.table@dblink allows reference of tables in other databases.
CREATE TEMPORARY TABLEDB2 supports created global temporary tables in addition to declared global temporary tables.
TRUNCATE table statementThis statement quickly deletes the contents of an entire table without firing triggers.
CREATE OR REPLACE object statementsThis notation allows you to replace objects if they already exist with new versions.
UNIQUE excluding NULL keysIf you create an index using the EXCLUDE NULL KEYS option rows where all key parts are NULL are not indexed. If the index is UNIQUE multiple all-NULL keys are allowed.
Function based indexThis feature allows you to index for example the upper-case of a string.
Large row sizesYou can define tables with many large strings, such as VARCHAR2(4000), which, in aggregate exceed 32KB.
Named parameters (=>)You can invoke procedures and functions by associating arguments to parameters by name using a => notation, rather than relying on positional association.
Relaxed name resolutionDB2 does not require inline views to be named anymore. Also, column names can be inherited more easily from set operators such as UNION.

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.


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, including:

  • 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 includes native PL/SQL support. What does this mean?

As you can see in Figure 3, the DB2 engine 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.

Figure 3. PL/SQL compiler support
PL/SQL Compiler support in Optim Development Studio

The integration into DB2 as a first-class procedural language has several implications, including the following.

  • 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 shows 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.

Figure 4. PL/SQL debugger support in DB2
Shows screen with debug pane, variables pane, and pane with query

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 the following.

  • 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
  • Local types can be declared within any PL/SQL block to be consumed within that block.
  • Local procedures can be delared within PL/SQL blcoks and be called from within that same block.
  • SUBTYPE declarations are used to restrict variables and parameters to domains within built-in types.
  • #PRAGMA AUTONOMOUS transactions, which allow procedures to execute in a private transaction.
  • CCFLAGS conditional compilation allows different sections of PL/SQL to be compiled based on context. This feature is particularly useful to minimize any DB2 specific code within a shared PL/SQL code base.
  • Vendors can obfuscate precious intellectual property in PL/SQL by wrapping PL/SQL objects such as package bodies using the DBMS_DDL.WRAP and DBMS_DDL.CREATE_WRAPPED functions.

PL/SQL object support

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

  • Scalar functions including support for the following.
    • INOUT and OUT function parameters
    • Invocation using named parameter association
    • Parameter defaulting
    • Runtime "purity" enforcement
  • Pipelined table functions including support for the following.
    • Invocation using named parameter association
    • Parameter defaulting
  • Before each row or statement multi-action triggers.
  • After each row or statement multi-action triggers.
  • Procedures including support for the following.
    • INOUT and OUT procedure parameters.
    • Invocation using named parameter association.
    • Parameter defaulting.
  • 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.

DB2 provides common built-in packages

Some Oracle applications utilize packages provided by the RDBMS. In particular, libraries that provide reporting, email, 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.

Table 7. Built-in packages provided by DB2
PackageDescription
DBMS_OUTPUTProvides basic reporting capabilities that can be switched on or off from the command line.
UTL_FILEA module that allows working with files on the DB2 server.
DBMS_SQLA package that provides an SQL API to perform dynamic SQL in addition to the existing EXECUTE and EXECUTE IMMEDIATE statements.
UTL_MAILA module that allows sending email notifications from SQL.
UTL_SMTPA lower-level API similar to UTL_MAIL that provides SMTP integration.
DBMS_ALERTA package that, when used, allows different sessions to semaphore one another.
DBMS_PIPEA module that allows sessions to send each other data.
DBMS_JOBProvides a compatible API that integrates with DB2's task scheduler.
DBMS_LOBAn Oracle API for LOB processing that echoes DB2's built-in LOB functions.
DBMS_UTILITYA collection of various procedures used in applications.
DBMS_DDLFunctions to obfuscate PL/SQL objects in order to protect vendor IP

More packages are available as-is at the Oracle Application Enablement to DB2 for LUW wiki. At this location, you can also find various other hints and tips as well as background information.


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 JDBC driver provides, among other things, support for calling procedures with reference cursor, VARRAY, and ROW parameters.


Support for OCI applications

Many older Oracle C applications use the Oracle Client Interface (OCI) to communicate with the DBMS. Since DB2 9.7 provides an OCI-compatible client called DB2CI, which supports in excess of 150 OCI-compatible functions from OCIAttrGet to OCITransRollback. With Fix Pack 2, even OCI7 APIs are available. In many cases, it is possible to simply relink an OCI application to the DB2CI library, allowing the OCI application to work against DB2 virtually unchanged.

  1. Simply create the following include file named oci.h:
    #include <db2ci.h>.
  2. Ensure that the new oci.h is before Oracle's oci.h in the PATH.
  3. Then replace the -loci.dll link option with -ldb2ci.dll in your makefile and recompile your application.

SQL*Plus script support using CLPPlus

Oftentimes, 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, as shown in Figure 5.

Figure 5. SQL*Plus-compatible CLPPlus tool
Shows entering commands from a command line in the CLPPlus tool

The tool provides the following functionality.

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

Oracle Forms enablement

Oracle Forms is a legacy software product used to create data entry systems for the database. Some customers and ISVs have hundreds of Oracle Forms screens that constitute or are part of an application. IBM has partnered with realease LLC and WonderWorks Group to provide seamless Oracle Forms to Java conversion capability.

Both partners provide tooling that translates Oracle forms to the Java language in a highly automated, efficient, and maintainable way preserving the look and feel of the original Forms GUI without creating any new dependencies on a third-party product.

WonderWorks FusionPaaS can convert a classic Oracle Fusion Forms application, such as the one shown in Figure 6, into a J2EE application, as shown in Figure 7, that is 100-percent web-enabled, running on an iPad for example or other smart device, and be integrated into Web 2.0 applications, such as Google Maps.

Figure 6. Original Oracle Fusion Forms
Shows an Oracle Fusion Forms Application
Figure 7. Converted form using FusionPaaS
Shows an Oracle Fusion Forms Application after conversion by FusionPaaS

For more information about WonderWorks FusionPaaS, see the white paper on the Oracle Application Enablement to DB2 for LUW wiki.


Continuous refinement

As more and more customers and partners enable to DB2, feature requests and refinements are being added into the DB2 Fix Pack streams to continually improve the level of compatibility.

DB2 9.7 Fix Pack 1

Fix Pack 1 introduces the following capabilities.

  • CCFLAGS support to maximize the amount of shared code between Oracle and DB2 for vendor applications.
  • FORALL and BULK COLLECT support.
  • The SUBSTRB function and refinements to SUBSTR.
  • Improvements to the handling of Boolean expressions.
  • The OCI application-compatible DB2CI client.
  • CREATE OR REPLACE for types.
  • Extended support for PL/SQL functions, such as INOUT parameters, the ability to write to tables from a function, and more.
  • The partnership with realease to enable Oracle Forms applications.

DB2 9.7 Fix Pack 2

Fix Pack 2 introduces the following capabilities.

  • NCHAR, NVARCHAR2, and NCLOB support, along with various NCHAR functions, such as TO_NCHAR and TO_NCLOB.
  • PL/SQL code obfuscation using the DBMS_DDL.WRAP and DBMS_DDL.CREATE_WRAPPED functions.
  • Named parameter invocation and DEFAULTs in functions.
  • DEFAULTs in procedures that are not at the end.
  • BULK COLLECT performance improvements.

DB2 9.7 Fix Pack 3

Fix Pack 3 introduces the following capabilities.

  • Ability to compare small LOBs to overcome page size limitations.
  • NUMBER performance enhancements.
  • RATIO_TO_REPORT function.
  • Improvements to RAISE_APPLICATION_ERROR.
  • Runtime "purity level" enforcement.

DB2 9.7 Fix Pack 4

Fix Pack 4 introduces the following capabilities.

  • JDBC support for ROW and ARRAY OF ROW.
  • Multi-action trigger support.
  • Support for BEFORE triggers that update the database.
  • Support for LIKE with non-constant patterns.
  • LISTAGG string aggregation support.
  • Improvements to autonomous transaction support.

DB2 9.7 Fix Pack 5

Fix Pack 5 introduces the following capabilities.

  • JDBC support for nested ROW and ARRAY.
  • Nested ROW and ARRAY support for PL/SQL.
  • New NVL2, SUBSTR2, and HEXTORAW functions.
  • Support for BOOLEAN in ROW and ARRAY types.
  • Improvements to anonymous blocks support.
  • Pro*C support.
  • Performance improvements for SQL comparing CHAR columns with VARCHAR2 values.

DB2 10.1

DB2 10.1 introduces the following capabilities.

  • Up to a magnitude better performance for logic heavy SQL PL and PL/SQL.
  • Support for type declarations local to a PL/SQL block.
  • Support for procedure declarations local to a PL/SQL block.
  • Statement level PL/SQL triggers
  • Row and Column level Access Control (RCAC) which makes it easy to map FGAC.

DB2 10.1 Fix Pack 2

Fix Pack 2 introduces the following capabilities.

  • Better performance for NOTFOUND exception handling in PL/SQL.
  • Support for SUBTYPE in PL/SQL.
  • Support for PL/SQL pipelined table functions.
  • Allow specification of the trim character for LTRIM and RTRIM functions.
  • Native NUMBER support for the MOD function.
  • Greatly improved performance for the Data Studio PL/SQL and SQL PL debugger.

DB2 10.5

DB2 10.5 introduces the following new capabilities.

  • Support for indexing of expressions.
  • Support for Oracle compatible unique index semantics.
  • Allow table definitions with rows exceeding 32KB.
  • Support for the INSTRB, INSTR2, INSTR4, LENGTHB, LENGTH2, LENGTH4, and SUBSTR4 functions.
  • Support for @dblink syntax when referencing remote tables.

Subsequent Fix Packs and upcoming major releases of DB2 are expected to provide an even higher level of compatibility.


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 used 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 follows.

  1. Setting 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 Data Extract Tool and connect to the Oracle and DB2 databases as shown in Figure 8.
    Figure 8. Drag and drop Oracle schemata onto DB2 using IBM Data Extract Tool
    Screen capture shows connecting to Oracle, connecting to DB2, extractin DDL/data and generating migration scripts, and then deploying the DDL/data when you execute the DB2 script
    Once you are connected, you can choose to extract the DDL only or both DDL and data. Finally, you have two choices: deploy directly by executing the generated scripts, or continue with the interactive deploy pane. The later is recommended for most non-trivial applications.
  5. Move desired schemas from Oracle to DB2 using interactive deploy as shown in Figure 9.
    Figure 9. Drag and drop Oracle schemata onto DB2 using IBM Data Extract Tool
    Screen cap shows panes where you drag and drop Oracle schemata
    In interactive deploy mode, you see a navigation 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. The goal is to interactively move all the objects to DB2 on an exception basis.

Sizing up the enablement to DB2

So how easy will it really be to enable your application to DB2 10? The answer is, of course, that it depends. IBM has a tool for download called MEET DB2. This tool can analyze all the objects in your Oracle database and score them. It produces a report of what will work out of the box and where adjustments need to be made. To get a quick idea, you can download MEET DB2 and try it for yourself. If you prefer, your IBM team, reachable at askdata@ca.ibm.com, can help to quickly provide a compatibility assessment of your current Oracle database.

Figure 10 shows an example of output from the MEET DB2 tool.

Figure 10. MEET DB2 report tool for assessment
Shows 96.8% of statements immediately transferable to DB2

In the example report, the tool indicates that 98% of the PL/SQL statements are immediately transferable to DB2. It also breaks down the statements by object type and shows how many of each object type would require attention during a migration.

Since DB2 9.7, hundreds of applications totaling millions of lines of PL/SQL code have been analyzed in detail with an average out-of-the-box transfer rate of 90-100 percent.

Figure 11 shows statistics that were run against DB2 9.7 Fix Pack 5.

Figure 11. Median of over 98 percent of statements supported
Graph shows average rate of supported statements

Th2 biggest 74 applications of 171 yielded over 2.5 million lines of code. Between 90.1 percent and 99.9 percent of the lines of code were immediately transferable to DB2.


Architecture

DB2's approach to application conversion is fundamentally different from that of classic conversion. Most vendors provide tooling that translates from the source language to the target language offline and with manual assistance. The outcome is very convoluted code that is very hard to maintain. Few vendors add intercept layers that emulate the source language in the target language dynamically. Of course, emulations are slow by design, especially when any attempt is made to achieve correct semantics.

For DB2, compatibility is built deep into all layers of its engine, which deals with semantic and syntax. Instead of working around limitations, DB2 has been extended natively to support necessary features. The architecture diagram in Figure 12 highlights the areas of DB2 that underwent significant change to support the Oracle SQL and PL/SQL dialect.

Figure 12. Architecture of DB2 Compiler and Runtime engine
Diagram showing DB2 Compiler and Runtime engine

The sections for SQL parser and rewrite in the SQL compiler, PL/SQL parser in the PL compiler, the PL runtime, and the SQL runtime were significantly extended or added to accommodate the Oracle dialects.


Frequently asked questions

Is this technology ready for critical applications?

One of the biggest US banks has entrusted its online banking system to this technology. If you checked your US bank account today you have likely driven execution of PL/SQL Packages on DB2.

Which version of Oracle does DB2 10 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 71 applications totaling over 2.5 million lines, between 90.1 percent and 99.9 percent of the code moved to DB2 9.7.5 without change with a median of over 98%. Many of the remaining adjustments can be automated, have since been addressed in DB2 10, 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. 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 in DB2 9.7 was completed in less than 18 months. Since then, we continue to incrementally improve the level of compatibility as customers require.

What are common complications in enabling from Oracle to DB2?

The compatibility of DB2 is obviously not 100 percent, so there will likely be some hiccups when you first convert to DB2. Many of these challenges are trivial and easy to fix. For example, DB2 does not support locally declared functions. So you would need to pull declared functions out into the package or replace them with locally declared procedures.

Which editions of DB2 support Oracle SQL compatibility features?

All features described above are supported across all platforms supported by DB2 for Linux, UNIX, and Windows. This includes Windows, Linux (on Intel, Power, and the mainframe), HP, and Sun Solaris. All editions of DB2 are supported. There are some technical restrictions when using PL/SQL functions and triggers in DPF.

Does DB2 Express-C support PL/SQL?

Yes! You can break out of the database size restrictions of Oracle XE by moving to DB2 10 Express C. And best of all: you can try it out on the cloud (see the Resources section).

What was the hardest feature to implement?

By far, VARCHAR2 semantics are the hardest to implement. The "trailing blanks matter" semantics and "NULL is empty string" continue to challenge.


Conclusion

Thanks to its native multi-dialect SQL support, the new DB2 allows for easy enablement of Oracle applications to DB2. Vendors of packaged applications can offer their applications on 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, download a trial version of DB2 10 (see the Resources section).

Resources

Learn

Get products and technologies

Discuss

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=416330
ArticleTitle=DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows
publish-date=05082013