Oracle到DB2的SQL移植解决方案

核心提示： 将Oracle(大型网站数据库平台)迁移到DB2上，SQL会有些变化，Oracle到DB2的SQL移植解决方案，下面让我们来具体看一下：1、Oracel中的decodeDB2解决方案：用case条件表达式完成， case两种语法模式： (1)CASE WHEN 条件 THEN 结果1 ELSE 结果2 END (2

　  将Oracle(大型网站数据库平台)迁移到DB2上，SQL会有些变化，下面让我们来具体看一下：

1、Oracel中的decode
DB2解决方案：用case条件表达式完成。

case两种语法模式：

(1)CASE

WHEN 条件 THEN 结果1

ELSE 结果2

END

(2)CASE 表达式1

WHEN 表达式2 THEN 结果1

ELSE 结果2

END

上面的WHEN可以重复多次，就像C中的SWITCH ..CASE的表达.

例如：

SELECT ORDNO,CUSNO,

CASE MONTH(SHIPDATE)

WHEN ''01'' THEN ''Jan''

WHEN ''02'' THEN ''Feb''

WHEN ''03'' THEN ''Mar''

WHEN ''04'' THEN ''Apr''

WHEN ''05'' THEN ''May''

WHEN ''06'' THEN ''Jun''

WHEN ''07'' THEN ''Jul''

WHEN ''08'' THEN ''Aug''

WHEN ''09'' THEN ''Sep''

WHEN ''10'' THEN ''Oct''

WHEN ''11'' THEN ''Nov''

WHEN ''12'' THEN ''Dec''

END

FROM FILE

应用实例：

Oracle(大型网站数据库平台) SQL：

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

select decode(t.organtypecode, ''D'', t.parent, ''S'', t.parent, t.id)

from A_ORGAN t

where t.parent = 35

DB2 SQL：

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

select case x.organtypecode

when ''D'' then

x.parent

when ''S'' then

x.parent

else

x.id

end

from a_Organ x

where x.parent = 35;

2、Oracle(大型网站数据库平台)中的Start with...Connect By递归查询

DB2解决方案：用with公共递归表达式来解决。

DB2解决方案：用case条件表达式完成。

Oracle(大型网站数据库平台) SQL：

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

select t.id

from a_organ t

start with t.id in (select decode(t.organtypecode,

''D'',

t.parent,

''S'',

t.parent,

t.id)

from A_ORGAN

where t.id = 35)

connect by t.parent = prior t.id

DB2 SQL：

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

WITH FKK(id) as

(select o.id from a_organ o

where o.id=35

UNION ALL

select case x.organtypecode

when ''D'' then x.parent

when ''S'' then x.parent

else x.id

end

from FKK fk, a_organ x

where fk.id=x.parent)

select distinct id from FKK;

3、Oracle(大型网站数据库平台)中的dual表对应DB2中的SYSIBM.SYSDUMMY1表

DB2解决方案：对应于DB2中的 SYSIBM.SYSDUMMY1表

Oracle(大型网站数据库平台) SQL：

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

select 15 as ttt from dual

结果：

ttt

-------

15

DB2 SQL：

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

select 15 as ttt from SYSIBM.SYSDUMMY1

结果：

ttt

-------

15

4、日期转换问题

DB2解决方案：有相应的函数

Oracle(大型网站数据库平台) SQL：

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

select m.*

from dj_mcdj m

where m.mcqc || '' '' like ''%$P%''

and m.xzqhdm || '' '' like ''%$P%''

and m.hylbdm || '' '' like ''%$P%''

and m.blqsrq >= to_date(''$P'', ''yyyy-mm-dd'')

and m.blqsrq < to_date(''$P'', ''yyyy-mm-dd'')+1

DB2 SQL：

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

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

--名称：名称库查询

--作者：雷智民

--日期：2006-10-27

--FOR ：DB2

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

select m.*

from dj_mcdj m

where m.mcqc || '' '' like ''%%''

and m.xzqhdm || '' '' like ''%%%''

and m.hylbdm || '' '' like ''%%%''

and date(m.blqsrq) >= date(''1900-01-01'')

and date(m.blqsrq) < date(''2050-01-01'')+1 day

5、nvl 问题
DB2解决方案：利用coalesce(,) 或 value(,)方法。
Oracle(大型网站数据库平台)中的nvl对应db2中的value ，只是Oracle(大型网站数据库平台)中的语法更有宽松一些，在db2中，value要求两个参数必须是同一种类型的，nvl要求则不是很严格，nvl(A,‘’)，如果A是数字类型或者日期类型的这个表达式也没有 问题，但是在db2中，若是也这么写的话， value(A,''),那肯定就有问题了，总的来说，基本上是一致的。

6、左右外连接问题
db2的左右外连接的语法和标准sql语法一样，只是没有Oracle(大型网站数据库平台)中的(+)这个简单符号来标记左右外连接，left (right) outer join on
1）.内连接INNER JOIN的Oracle(大型网站数据库平台)和DB2的写法

　　Oracle(大型网站数据库平台)可以这样实现? Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no; DB2 可以这样实现? Select * from db2admin.bsempms inner join db2admin.bsdptms on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

2）.外连接的Oracle(大型网站数据库平台)和DB2的写法(右外连接,左外连接,完全外连接,组合外连接)

　　Oracle(大型网站数据库平台)可以这样实现?
　　Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no(+);
　　Select a.* from bsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no;
　　DB2 可以这样实现?
　　Select * from db2admin.bsempms right outer join db2admin.bsdptms
　　on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

　　Select * from db2admin.bsempms left outer join db2admin.bsdptms
　　on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

　　Select * from db2admin.bsempms full outer join db2admin.bsdptms
　　on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

7、LIKE问题

db2中谓词LIKE后边的表达式不支持字段。只支持一下类型：
A constant
A special register
A host variable
A scalar function whose operands are any of the above
An expression concatenating any of the above

（附DB2文档：
使用格式： match-expression LIKE pattern-expression
match-expression
An expression that specifies the string that is to be examined to see if it conforms to a certain pattern of characters.
The expression can be specified by:

A constant
A special register
A host variable (including a locator variable or a file reference variable)
A scalar function
A large object locator
A column name
An expression concatenating any of the above

pattern-expression
An expression that specifies the string that is to be matched.
The expression can be specified by:

A constant
A special register
A host variable
A scalar function whose operands are any of the above
An expression concatenating any of the above
with the following restrictions:

No element in the expression can be of type LONG VARCHAR, CLOB, LONG VARGRAPHIC, or DBCLOB. In addition it cannot be a BLOB file reference variable.
The actual length of pattern-expression cannot be more than 32 672 bytes.
）

DB2中几个隔离级别select..for update with ** 的行锁

看了很多介绍DB2中隔离级别和锁的各种用法和机制，动手做了一个比较详尽的试验，
对于有些结果我还真没想明白。。

在　db2 9 中我做了以下的试验，
Create table RRTest (pkID VARCHAR(20) NOT NULL ,unID1 varchar(20) Not NULL,UnID2 varchar(20) ,"CUSTOMER_ID" VARCHAR(6) ,
"ORDER_TYPE" DECIMAL(2,0) ,
"EXECUTION_TYPE" DECIMAL(2,0) ,
"ORDER_DATE" VARCHAR(8) ,
"ORDER_TIME" VARCHAR(6) ,
"ORDER_DATETIME" TIMESTAMP ,
"SIDE" DECIMAL(1,0) ,
"TRADE_TYPE" DECIMAL(1,0) ,
"ORDER_AMOUNT" DECIMAL(15,2) ,
"ORDER_PRICE" DECIMAL(8,4),
TSID varchar(20) )

insert into RRTest
SELECT Order_ID, Order_ID, Order_ID, CUSTOMER_ID, ORDER_TYPE, EXECUTION_TYPE, ORDER_DATE, ORDER_TIME, ORDER_DATETIME, SIDE, TRADE_TYPE, ORDER_AMOUNT, ORDER_PRICE ,ORDER_ID
FROM DB2INST1.Fx_Order where ORDER_DATE >'20070401'
GO
select count(*) From RRTEST
72239

ALTER TABLE "DB2INST1".RRTest
ADD Prima(最完善的虚拟主机管理系统)RY KEY
(pkID);

CREATE UNIQUE INDEX UNIQINDX ON RRTest(unID1)
CREATE INDEX INDX002 ON RRTest(unID2)
db2 "RUNSTATS ON TABLE DB2INST1.RRTest ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS"

db2 connect to db2TT
db2 +c

select * From RRTEST where TSID='20070223ORD01267732' for update with RR
select * From RRTEST where TSID='20070222ORD01266302' for update with RR

select * From RRTEST where TSID='20070223ORD01267732' for update with RS
select * From RRTEST where TSID='20070222ORD01266302' for update with RS

select * From RRTEST where unID1='20070223ORD01267732' for update with RR
select * From RRTEST where unID1='20070222ORD01266302' for update with RR
select * From RRTEST where unID1='20070223ORD01267732' for update with RS
select * From RRTEST where unID1='20070222ORD01266302' for update with RS

select * From RRTEST where unID2='20070223ORD01267732' for update with RR
select * From RRTEST where unID2='20070222ORD01266302' for update with RR
select * From RRTEST where unID2='20070223ORD01267732' for update with RS
select * From RRTEST where unID2='20070222ORD01266302' for update with RS

select * From RRTEST where pkID='20070223ORD01267732' for update with RR
select * From RRTEST where pkID='20070222ORD01266302' for update with RR
select * From RRTEST where pkID='20070223ORD01267732' for update with RS
select * From RRTEST where pkID='20070222ORD01266302' for update with RS

按照以上字段　pkID 是主键，unID1 是唯一健索引，unID2 是普通健索引，TSID 是普通字段，没有在上建立索引。

试验结论：

PK_INDEX UNIQ_INDEX NormalINDEX NO_INDEX
WITH RR 锁行,不锁表 锁行,不锁表 不锁行,不锁表(1) 锁行,锁表
WITH RS 锁行,不锁表 锁行,不锁表 锁行,不锁表 锁行,锁表(2)

锁行是指在一个事务中用某种方式读取并更改了改行数据并显示得指明要修改后，这个事务将锁住改行，直到它提交或者回滚了事务后，才释放该锁。

锁表是指在用以上各种SQL在读取并更改一行的同时锁住了整个表。

对以上红字部分(1)可能有不能理解的是:为什么对普通索引和主键或者唯一健索引的不同结论?　

　对　PK和UNIQ的解释是因为RR 是可重复的读的级别，对这次检索扫描到的有可能成为自己的潜在检索对象的内容都会锁住，而因为是主键或者唯一健，别的行不可能成为这次这个检索的潜在读的范围，就是对别的数据此事务根本就没有必要锁，任何情况的更改都不可能出现幻读的情况(此表上的约束限制），所以只锁这一行。这么理解对PK,UNIQ没有问题。

但是NormalINDEX　我认为应该是锁住这个表而不是不锁。这点一直没想明白。留待以后再加强理解。

对　ＲＳ隔离级别是“锁定检索到的数据行”，是通过SQL检索到的结果进行锁定， PK,UNIQ,INDEX的结论完全都可以理解。　对 tableScan的检索而出现的锁表有些象RR隔离级别的所为。

嗯，想了一圈没想明白，故把详细过程贴出来给自己留个纪念，以供以后遇到此类并发控制程序中注意一下，select * From TTT where ****= ? for update with RR(RS)，这里的　*** 可不是随便定义的。

隔离级别分为RR/RS/CS/UR这四个级别。 下面让我们来逐一论述：

1． RR隔离级别： 在此隔离级别下， DB2会锁住所有相关的纪录。 在一个SQL语句执行期间， 所有执行此语句扫描过的纪录都会被加上相应的锁。 具体的锁的类型还是由操作的类型来决定， 如果是读取，则加共享锁； 如果是更新， 则加独占锁。 由于会锁定所有为获得SQL语句的结果而扫描的纪录， 所以锁的数量可能会很庞大， 这个时候， 索引的增加可能会对SQL语句的执行有很大的影响，因为索引会影响SQL语句扫描的纪录数量。

2． RS隔离级别： 此隔离级别的要求比RR隔离级别稍弱，此隔离级别下会锁定所有符合条件的纪录。 不论是读取， 还是更新， 如果SQL语句中包含查询条件， 则会对所有符合条件的纪录加相应的锁。 如果没有条件语句， 也就是对表中的所有记录进行处理，则会对所有的纪录加锁。

3． CS隔离级别： 此隔离级别仅锁住当前处理的纪录。

4． UR隔离级别：此隔离级别下，如果是读取操作，不会出现任何的行级锁。对于非只读的操作，它的锁处理和CS相同。

DB2默认的隔离级别是　CS。即　游标稳定性。

DB2分页查询
SELECT * FROM (Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20
SELECT * FROM (Select T.*,rownumber() over(ORDER BY T.ID ASC) AS rn from ADMINISTRATOR.TEST_BY_HUFENG T) AS a1 WHERE a1.rn BETWEEN 1 AND 3

技术交流　永无止境