Oracle SQL到DB2 SQL的移植(3)

  来源：

---

 

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 PRIMARY 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