Performance of operations and benefits of DB2 9.7 SQL compatibility

Source: Internet
Author: User
Tags db2 express ibm db2

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:

  1. 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:

  2. db2stop  
  3. db2start 

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

  1. 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:

  1. 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)




  1. %ROWTYPE   
  2. %TYPE   
  4. 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:


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.