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