DB2 10: Making the move from Oracle even easier

The DB2 for LUW team has been busily driving down the cost of enabling Oracle applications since that effort started with DB2 9.7.

Every DB2 9.7 fixpack up to DB2 9.7.5 has contained features to incrementally increase the level of compatibility allowing users to break free of Oracle.

Now, finally it's time to unleash DB2 10 with yet another set of goodies.

Here are some:

Local Types

This feature allows PL/SQL and SQL PL blocks to locally declare types for consumption in variables within the BEGIN .. END block.
Previously when such type declarations were encountered you needed to move them to become either global or PL/SQL package types.
Now, the following PL/SQL works natively in DB2.

DECLARE
TYPE point IS RECORD(x NUMBER, y NUMBER);
here point;
BEGIN
here := (5, 3);
END;
/

Similar to local types this feature allows you to declare procedures for consumption within the BEGIN .. END block.
Procedures can declare local procedures in turn and reference variables and local types within the block in which they have been declared.

Similar to local types in DB2 9.7 you had to move such locally declared procedures into a package or make them global when an application used this feature.
Now, the following PL/SQL works natively in DB2.

DECLARE
TYPE point IS RECORD(x NUMBER, y NUMBER);
PROCEDURE makepoint(x IN NUMBER, y IN NUMBER, xy OUT point)
IS
BEGIN
xy := (x, y);
END;
here point;
BEGIN
makepoint(5, 3, here);
END;
/

PL/SQL Expression Inlining

DB2 9.7 is fast. DB2 10 is even faster! 
Customers and business partners frequently report that Oracle Applications enabled to DB2 meet or exceed performance of the source system.
But there have been cases where logic heavy procedures or functions with little or no SQL have not met this goal.

The reason was that DB2 9.7 performs most mathematical or string expressions as simple SQL statements.
DB2 10 collapses most arithmetic, boolean logic and string operations into a single light-weight processing unit.

This greatly improves performance.

One business partner has reported that switching from DB2 9.7 to DB2 10 provided a multiple of performance improvement for a specific PL/SQL routine.

This in turn speed up a critical process by over 20% exceeding the goal of equal performance compared to the source system. 

But wait there is more!

Many other features, while not primarily aimed at improving application compatibility further help make DB2 a good alternative.

• Support for expressions in hash join conditions
• Zig zag join
  
• Row and Column Access Control (RCAC)
• The incorporation of pureScale DB2 10

By the numbers

Since DB2 9.7 compatibility of DB2 with real Oracle Applications has been steadily increasing.
With the data available so far it seems DB2 is breaching the 98% mark for Lines Of Code of PL/SQL code that remain unchanged on average when moving an application from Oracle to DB2 10. 

When you add it all up DB2 10 is a safe, scalable and easily reachable harbor from high database licensing cost.

Over the next little while I will discuss here many of the DB2 10 features in detail.