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:

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

Back to top

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
• %ROWTYPE
• %TYPE
• EXCEPTIONS
• 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.

Back to top

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)
IS
BEGIN
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:

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

This loop will produce the following output:

Counter 10
Counter 20

Back to top

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:

CREATE TABLE CLIENTS
(CLIENTID NUMBER(5) PRIMARY KEY NOT NULL,CLIENTNAME
VARCHAR2(50));

Insert some rows:

INSERT INTO CLIENTS (CLIENTID, CLIENTNAME) VALUES (1,'IBM');
INSERT INTO CLIENTS VALUES (2,'MSFT');
INSERT INTO CLIENTS VALUES (3,'EDB');

Let's look at the rows:

SELECT * FROM CLIENTS;

Now issue the following command to truncate these rows:

TRUNCATE TABLE CLIENTS;

Let's prove that they're gone:

SELECT * FROM CLIENTS;

You will get zero rows displayed.

Back to top

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!

Back to top

Resources

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• SQL compatibility
• PL/SQL features
• PL/SQL procedures in DB2
• The truncate table command
• Object and concurrency enhancements
• 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