教你快速掌握Oracle SQL到DB2 SQL的移植
Oracle SQL到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： 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 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 |
按照以上字段 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 我认为应该是锁住这个表而不是不锁。这点一直没想明白。留待以后再加强理解。
对RS隔离级别是“锁定检索到的数据行”，是通过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 |
