IBM Data Management magazine Issue 2, 2009

Compatibility in

From triggers to object enhancements:
A demonstration of the new Oracle
compatibility features

P S h

Calling all developers! do you have Oracle database skills? Do you wish you could reuse your PL/SQL knowledge and code on another database platform? IBM DB2 for Linux, UNIX, and Windows (LUW) has ad a makeover. Driven by present and future customer requirements for cost-saving enhancements, the latest release, DB2 9. 7, provides a host of new technologies in familiar areas like autonomics (self-management) and Deep Compression (see “DB2 9.7: It All Adds Up” in this issue). But the icing on the cake is a collection of capabilities focusing on SQL compatibility. These features make DB2 easier to adopt as your database software so you can take advantage of its cost-saving and high-performance characteristics, even if you are currently most familiar with competing databases.

In this article I will show examples of several of the new QL compatibility features in DB2 9. 7. We’ll start with triggers and new data types, move into support for Oracle SQL and PL/SQL, and wrap up with a discussion of object and concurrency enhancements. There is something for everyone as DB2 “gets compatible.”

To get started, connect to the Command Line Processor lus (CLP Plus) window (see Figure 1), replacing “db2admin”

with your chosen login credentials:

clpplus db2admin@localhost:50000/sample

SQL compatibility

The first step in using the SQL compatibility features in DB2 9. 7 is to enable the DB2_COMPATIBILIT Y_VECTOR parameter. Setting db2set DB2_COMPATIBILITY_VECTOR=ORA enables the SQL compatibility features related to Oracle for all subsequent database creation. For an explanation of some of the most important parameters used by DB2_COMPATIBILITY_ VEC TOR, see Table 1.

To enable this feature, you must have administrative rivileges. Enter the following commands at the DB2 command window:

db2set DB2_COMPATIBILIT Y_VECTOR=ORA db2stop db2start

To create a simple database, enter: db2 create database [dbname].

You can then connect to the database by entering:

db2 connect to [dbname];

The next step is to create tables using Oracle data types. Enter the following CREATE TABLE statement using either the CLP Plus window or IBM Data Studio: