SQL Compatibility in DB2 9.7

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

From triggers to object enhancements, this article provides a demonstration of the new Oracle compatibility features in DB2 9.7.


Anil Mahadev, Contributing writer, IT Market Strategy

Anil Mahadev is an IBM Data Champion and a member of the Board of Directors for the IDUG India Forum. He is currently working as an independent consultant in Bengaluru, India, and exploring database implementation, Windows clustering, high availability, and strategies for multi-database platforms and applications.

30 June 2009

Also available in Chinese Vietnamese Portuguese Spanish

Develop skills on this topic

This content is part of progressive knowledge paths for advancing your skills. See:

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 had 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 SQL 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 Plus (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_COMPATIBILITY_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_ VECTOR, see Table 1.

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


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:
Create table employee (EMPNO NUMBER(5), ENAME VARCHAR2(50), DEPTNO NUMBER(5))

Figure 1: In DB2 9.7, the CLP Plus interface allows you to try out the SQL compatibility features.
Figure 1: In DB2 9.7, the CLP Plus interface allows you to try out the SQL compatibility features.

PL/SQL features

We will now explore the features that allow you to create PL/SQL procedures. DB2 9.7 supports the following:

  • User-defined packages
  • PL/SQL procedures/functions/anonymous blocks
  • Built-in packages (DBMS_OUT.PUT_LINE, DBMS_PIPE, DBMS_UTL, and so on)
  • Associative (INDEX BY) arrays
  • Varrays
  • Triggers
  • %TYPE
  • Ref-Cursors

Note that PL/SQL support is available only in the Enterprise Server Edition and Workgroup Edition of DB2 9.7 for LUW. PL/SQL is not supported in DB2 Express-C, DB2 Express, or DB2 Personal Edition.

PL/SQL procedures in DB2

DB2 supports much of the PL/SQL language. Let's see how this works with some sample code. Once it is compiled and executed, this PL/SQL procedure displays a welcome message to the user. To see the output on the console, first issue the command SET SERVEROUTPUT ON.

CREATE OR REPLACE PROCEDURE message_proc (myname varchar2)
DBMS_OUTPUT.PUT_LINE('Welcome to DB2 9.7 ( )! ' || myname
|| '. Now supports for ORACLE SQL and PL /SQL as well :-)!');
END message_proc;

To call this PL/SQL procedure in DB2, issue the following commands:

EXEC proc_name
EXEC message_proc('Anil');

Here is an example that uses a FOR loop:

FOR a IN 10 .. 20 LOOP
DBMS_OUTPUT.PUT_LINE('Counter ' || a);

This loop will produce the following output:

Counter 10
Counter 20

The truncate table command

Those of you with an Oracle database background have probably used the command TRUNCATE TABLE at some point in your careers. DB2 now supports this command as well. The following code creates a simple table, adds rows to it, and then uses the TRUNCATE command to remove all the rows from the table:


Insert some rows:



Let's look at the rows:


Now issue the following command to truncate these rows:


Let's prove that they're gone:


You will get zero rows displayed.

Object and concurrency enhancements

I'd like to wrap up by mentioning two important improvements to DB2 9.7 that can save you time and increase performance: first-use validation, and a new default locking state.

First-use validation
Whenever underlying database objects (tables, views, procedures, functions, and so on) are altered, the next time an object is retrieved with its dependent objects, it will be automatically revalidated. This is definitely a time-saver for application developers. The automatic dependency check is done whenever an object is altered.

Read currently committed (default in DB2)
In previous releases of DB2, it was not possible to have one user trying to read and one trying to write to the same row at the same time. This is based on the principle that readers wait for the outcome of pending writes. DB2 9.7 now uses the default-locking state so that readers will not wait for the outcome of a pending write. Instead they will read the currently committed version of the row. Hence, readers don't block writers and writers don't block readers.

I am very impressed by these new SQL compatibility features. I have only been able to scratch the surface in this article, So I encourage you to take DB2 9.7 for a spin. You won't be disappointed!


General information on IBM DB2Upcoming DB2 technologies


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

Zone=Information Management
ArticleTitle=SQL Compatibility in DB2 9.7