(6) db2 9.7兼容oracle的特性

　　这是为了熟悉Oracle的用户向db2迁移而设计的功能，号称可以做到百分之几十的代码不需要修改就能在db2中运行原来的Oracle应用，但实际效果还是要亲自使用才知道。

　　设置步骤

　　首先要用db2set命令在注册变量中设定兼容模式，然后重新启动实例，再发出create database命令，如果在db2set设定前创建的数据库，则不能完全用到db2兼容Oracle的功能，因为兼容不但需要提供模拟Oracle的命令，还需要创建一系列系统表，比如类似Oracle的dict等数据字典表。兼容oracle的数据库可以用到oracle的一些特有的命令，比如connect by语法。

-sh-3.2$ db2set db2_compatibility_vector=ora
-sh-3.2$ db2stop force
2011-05-06 16:23:51     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
-sh-3.2$ db2start
05/06/2011 16:24:13     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
-sh-3.2$ db2 "create database oracle pagesize 32 K"
SQL1047N  The application is already connected to another database.
-sh-3.2$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
-sh-3.2$ db2 "create database oracle pagesize 32 K"
SQL0204N  "SYSTEM_1392_CN" is an undefined name.  SQLSTATE=42704

--支持oracle的connect by 语法
-sh-3.2$ db2 "with t as (select 1 a from dual union all select 0 from dual)select cast(sys_connect_by_path(a,'/') as varchar(30))from t connect by prior a=a-1"

1                            
------------------------------
/0                            
/0/1                          
/1                            

  3 record(s) selected.
--不支持connect by rownum和level语法
-sh-3.2$ db2 "select level l  from dual connect by level<=3"

L          
-----------
SQL20451N  Cycle detected in a hierarchical query.  SQLSTATE=560CO
-sh-3.2$ db2 "select rownum l  from dual connect by rownum<=3"
SQL0120N  Invalid use of an aggregate function or OLAP function.  
SQLSTATE=42903

--创建数据库后设置兼容模式
[db2inst1@aix:/home/db2inst1]#>db2set db2_compatibility_vector=ora
[db2inst1@aix:/home/db2inst1]#>db2set
DB2_COMPATIBILITY_VECTOR=ORA
DB2COMM=TCPIP
SQL1025N  The database manager was not stopped because databases are still active.
[db2inst1@aix:/home/db2inst1]#>db2stop force
05/10/2011 16:00:49     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@aix:/home/db2inst1]#>db2start
05/10/2011 16:00:58     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@aix:/home/db2inst1]#>clpplus -nw db2inst1/db2@localhost:60000/tcph
CLPPlus: Version 1.4
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.

SQL> set timi on
--sysdate函数可以使用
SQL> select sysdate from dual;

1                    
---------------------
2011-05-10 16:01:27  
Elapsed time: 59 millisecond(s)
--dict表不存在
SQL> desc dict
DB250211E: The database object named 'DICT' was not found in the database.
SQL> exit
--删除数据库并重建
[db2inst1@aix:/home/db2inst1]#>db2 drop database tcph
DB20000I  The DROP DATABASE command completed successfully.
[db2inst1@aix:/home/db2inst1]#>db2 terminate
DB20000I  The TERMINATE command completed successfully.
[db2inst1@aix:/home/db2inst1]#>db2 "create database tpch using CODESET UTF-8 TERRiTORY CN pagesize 32 K"
DB20000I  The CREATE DATABASE command completed successfully.
[db2inst1@aix:/home/db2inst1]#>clpplus -nw db2inst1/db2@localhost:60000/tpch
CLPPlus: Version 1.4
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.
--dict表存在了
SQL> desc dict

ALIAS - DICT
********************************************************************************

OBJECT NAME : DICTIONARY
OBJECT SCHEMA : SYSIBMADM
OBJECT TYPE : VIEW

Name                Data Type     Type schema   Length   Scale   Nulls Hidden
------------------- ------------- ------------- -------- ------- ----- --------
TABLE_SCHEMA        VARCHAR       SYSIBM             128       0 N     Not    
TABLE_NAME          VARCHAR       SYSIBM             128       0 N     Not    
COMMENTS            VARCHAR       SYSIBM             254       0 Y     Not    

********************************************************************************
--查看执行计划
SQL> set lines 132            
SQL> set pages 50000
--创建索引前，执行计划是表扫描
SQL> explain plan for select count(*) cnt from tpch.part where p_size=30;

    ID TYPE            OBJECT_SCHEMA        OBJECT_NAME          PREDICATE_TEXT    
------ --------------- -------------------- -------------------- ------------------
     1 RETURN                                                                      
     2 GRPBY                                                                      
     3 TBSCAN          TPCH                 PART                 (Q1.P_SIZE = 30)  
--创建索引后，执行计划变成了索引扫描
SQL> create index tpch.part_size_idx on tpch.part(p_size);

DB250000I: The command completed successfully.

SQL> explain plan for select count(*) cnt from tpch.part where p_size=30;

    ID TYPE            OBJECT_SCHEMA        OBJECT_NAME          PREDICATE_TEXT    
------ --------------- -------------------- -------------------- ------------------
     1 RETURN                                                                      
     2 GRPBY                                                                      
     3 IXSCAN          TPCH                 PART_SIZE_IDX        (Q1.P_SIZE = 30)  
     3 IXSCAN          TPCH                 PART_SIZE_IDX        (Q1.P_SIZE = 30)

 　　这里执行计划的输出速度较慢，好像和SQL查询的时间相当，要快速输出，还是用前面的db2expln工具。

　　(7)clpplus工具

　　前面我们已经多次用到了这个工具，从命令名的字面看它就是瞄准Oracle的sqlplus开发的，也是为了方便Oracle的开发人员尽快掌握db2的开发技术，它支持在命令行编辑PL/SQL存储过程或函数，而前面的db2工具是不支持的，还有一些oracle的专用命令，比如set timing on计时，也得到了实现，这样就方便了对SQL的运行计时，但db2的实现离真正的sqlplus还是有区别的，比如对输出SQL语句的执行计划就不能计时，另外，要注意clpplus中只能输入SQL语句，PL/SQL语句和clpplus命令，不能输入db2的命令，比如runstats。工具本身也存在一些问题，比如将代码粘贴到clpplus命令行时，有时候会丢失字符。Db2的兼容oracle的特性也在不断发展中，某些功能在9.72版本中还不具备，到9.74版本就有了，比如数据字典，查看执行计划和set autotrace功能。下载的pdf文档的信息不是最新的，最新的信息还是参考在线文档。