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
来自: 新客网(www.xker.com) 详文参考：http://www.xker.com/page/e2008/0128/46641_3.html
版权声明：
原创作品，许愿转载，转载时请务必以超链接方式标明文章 原始情由 、作者信息和本声明。不然将穷究法律责任。