教你快速掌握Oracle SQL到DB2 SQL的移植

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;

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;

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