DB2V9.7與ORACLE的兼容特性
在DB2V9.7版本後，DB2可以兼容ORACLE的一些特性，譬如PL/SQL、ROWNUM,CONNECT BY等特性。
通過設置注冊表環境變量DB2_COMPATIBILITY_VECTOR=ORA來開啟-www.2cto.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
(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 yansp/yansp@db2server:50000/oracle
數據庫連接信息
主機名 = 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>
作者 TOMSYAN
