DB2V9.7与ORACLE的兼容特性

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

通过设置注册表环境变量DB2_COMPATIBILITY_VECTOR=ORA来开启-www.zzzyk.com-与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

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

-----

1 record(s) selected.

[yansp@db2server ~]$ clpplus yansp/yansp@db2server:50000/oracle

数据库连接信息

主机名 = db2server

数据库服务器 = DB2/LINUX SQL09070

SQL 授权标识 = yansp

本地数据库别名 = ORACLE

端口 = 50000

CLPPlus: 版本 1.0

SQL> values current schema

2 ;

--------------------------------------------------

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;

-----------

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

DB250000I: 成功地完成该命令。

SQL>

作者 TOMSYAN