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:
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.
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
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 :-)!');
To call this PL/SQL procedure in DB2, issue the following commands:
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:
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
Insert some rows:
INSERT INTO CLIENTS (CLIENTID, CLIENTNAME) VALUES (1,'IBM');
INSERT INTO CLIENTS VALUES (2,'MSFT');
INSERT INTO CLIENTS VALUES (3,'EDB');
Table 1: DB2_COMPATIBILITY_VECTOR values
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.
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.
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!