DB2V9.7与ORACLE的兼容特性

DB2V9.7与ORACLE的兼容特性

在DB2V9.7版本后，DB2可以兼容ORACLE的一些特性，譬如PL/SQL、ROWNUM,CONNECT BY等特性。

通过设置注册表环境变量DB2_COMPATIBILITY_VECTOR=ORA来开启与ORACLE的兼容特性。

上面的命令执行后，DB2 所有的Oracle 兼容特性都被激活了。但实际工作中，有时候开发者只想使用兼容特性的一种或者多种。为此，我们首先需要解释一下DB2_COMPATIBILITY_VECTOR 值的含义。环境变量DB2_COMPATIBILITY_VECTOR 的值是一个16 进制数字，其中每一位和Oracle 兼容特性中的一种相对应。开发者可以根据实际项目的需要选择一种或多种兼容特性。

16 进制值  兼容特性    描 述
1 (0x01)   ROWNUM 支持    ROWNUM 出现在WHERE 字句中
2 (0x02)   DUAL 支持     DUAL 表
3 (0x04)   Outer join operator    支持外连接操作符(+)
4 (0x08)   Hierarchical queries    支持使用CONNECT BY 的嵌套查询
5 (0x10)   NUMBER data type    支持 NUMBER 数据类型
6 (0x20)   VARCHAR2 data type    支持 VARCHAR2 数据类型
7 (0x40)   DATE data type 支持    DATE 和TIMESTAMP 组合使用
8 (0x80)   TRUNCATE TABLE 支持    TURNCATE TABLE 语句
9 (0x100)   Character literals    支持 CHAR 和GRAPHIC 数据类型的赋值操作
10 (0x200)   Collection methods    支持集合方法，例如对ARRAY 的first、last、next 和previous 方法
11 (0x400)   Data dictionary-compatible views  支持创建数据字典兼容特性视图
12 (0x800)   PL/SQL      compilation 支持 PL/SQL 语言

以下是一个简单的开启步骤：

 

[yansp@db2server ~]$ db2set DB2_COMPATIBILITY_VECTOR=ORA

[yansp@db2server ~]$ db2set -all

[i] DB2_COMPATIBILITY_VECTOR=ORA
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=db2server
[g] DB2INSTDEF=yansp

[yansp@db2server ~]$ db2 force applications all
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

[yansp@db2server ~]$ db2stop
2012-03-26 10:59:34     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

[yansp@db2server ~]$ db2start
03/26/2012 10:59:40     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

[yansp@db2server ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.0

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => get instance

 The current database manager instance is:  yansp

db2 => create database oracle automatic storage yes
DB20000I  The CREATE DATABASE command completed successfully.
db2 => list active databases
SQL1611W  No data was returned by Database System Monitor.
db2 => connect to oracle

   Database Connection Information

 Database server        = DB2/LINUX 9.7.0
 SQL authorization ID   = YANSP
 Local database alias   = ORACLE

db2 => select * from dual

DUMMY
-----
X   

  1 record(s) selected.
 

[yansp@db2server ~]$ clpplus
数据库连接信息

主机名 = db2server
数据库服务器 = DB2/LINUX  SQL09070
SQL 授权标识 = yansp
本地数据库别名 = ORACLE
端口 = 50000

CLPPlus: 版本 1.0
Copyright ? 2009, IBM CORPORATION.  All rights reserved.

SQL> values current schema
  2  ;

1                                                
--------------------------------------------------
YANSP                                            

DB250000I: 成功地完成该命令。

SQL> create table huateng(x int);

DB250000I: 成功地完成该命令。

SQL> begin
  2    for i in 1..100
  3    loop
  4      insert into huateng values(i);
  5    end loop;
  6    commit;
  7  end ;
  8  /

DB250000I: 成功地完成该命令。

SQL> select * from huateng where rownum<=5;

          X
-----------
          1
          2
          3
          4
          5
SQL> begin
  2    dbms_output.put_line(10);
  3  end;
  4  /

DB250000I: 成功地完成该命令。

SQL> set serverout on
SQL> run
  1  begin
  2    dbms_output.put_line(10);
  3* end
10

DB250000I: 成功地完成该命令。

SQL>