Performance of operations and benefits of DB2 9.7 SQL compatibility

This article mainly describes the actual operation steps of DB2 9.7 SQL compatibility and the benefits of DB2 9.7 SQL compatibility. At the same time, this article also describes the SQL features, the following is a detailed description of the main content of the article. I hope you will have a better understanding of it after browsing.

Developers! Do you have Oracle database skills? Do you want to reuse PL/SQL knowledge and Code on another database platform?

IBM DB2 for Linux, UNIX, and Windows (LUW) have completed a major transformation. As customers have been demanding cost-saving improvements, the latest version of DB2 9.7 provides many new technologies in areas that everyone is familiar with, such as autonomous self-management.) and deep compression can be found in "DB2 9.7: overall improvement ").

However, developers are most interested in functions related to SQL compatibility. These features make it easier for customers to use DB2 as database software to take advantage of its cost-saving and high-performance features. Even if the customer is most familiar with other database systems, it is easy to master DB2.

In this article, I will use an example to demonstrate the new SQL compatibility feature in DB2 9.7. First, we will discuss the triggers and new data types, then talk about the support for Oracle SQL and PL/SQL, and finally discuss the object and concurrency improvement. In this way, all developers should believe that DB2 is "compatible ".

First, connect to the Command Line Processor Plus (CLP Plus) window, as shown in Figure 1), replace "db2admin" with your selected logon ID:

 
 

  
  
clpplus db2admin@localhost:50000/sample 
 
 

SQL compatibility

The first step to use the new SQL compatibility feature in DB2 9.7 is to enable the DB2_COMPATIBILITY_VECTOR parameter. Set db2set DB2_COMPATIBILITY_VECTOR = ORA to enable Oracle-related SQL compatibility for subsequent database creation operations. The most important parameters used by DB2_COMPATIBILITY_VECTOR are described in table 1.

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

 
 

  
  
db2set DB2_COMPATIBILITY_VECTOR=ORA 
  
  
db2stop  
  
  
db2start 
 
 

To create a simple database, enter db2 create database [dbname]. Then, run the following command to connect to the database:

 
 

  
  
db2 connect to [dbname]; 
 
 

The next step is to create a table that uses the Oracle data type. In the CLP Plus window or IBM Data Studio, enter the following create table statement:

 
 

  
  
Create table employee (EMPNO NUMBER(5), ENAME VARCHAR2(50), DEPTNO NUMBER(5))  
 
 

In DB2 9.7, you can experience SQL compatibility through the CLP Plus Interface

PL/SQL features

Now we will discuss the features that support creating PL/SQL processes. DB2 9.7 support:

User-Defined package

PL/SQL process/function/anonymous Block

Built-in packages such as DBMS_OUT.PUT_LINE, DBMS_PIPE, and DBMS_UTL)

INDEX BY ARRAY

Varrays

Trigger

 
 

  
  
%ROWTYPE   
  
  
%TYPE   
  
  
EXCEPTIONS   
  
  
Ref-Cursors  
 
 

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

For details, visit:

Http://www.ibm.com/developerworks/cn/data/dmmag/dbt14n2/InTrans/index.html