mysql至DB2迁移应用小结，有点杂

第一节

1.1
函数及操作符，DB2比其他数据库怪点，不按规矩走，列举下面几个操作，左边为常见操作，右边为DB2对应内容：
%               =>  MOD
MINUS           =>  EXCEPT
IFNULL          =>  ISNULL,NVL
IF              =>  CASE
CHARACTER_LENGTH(COL_NAME) => CHARACTER_LENGTH(COL_NAME, CODEUNITS32) http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0008470.htm
INSTR           =>  LOCATE
LAST_INSERT_ID  =>  IDENTITY_VAL_LOCAL
&               =>  BITAND
|               =>  BITOR
LIMIT a, b      =>  需要 ROW_NUMBER() ORDER BY(column ...) 与 FETCH FIRST n ROWS ONLY 配合使用
REPLACE         =>  MERGE (这个语法很好，非常有用，Oracle、DB2和SQL Server都支持，字面意思就是说，有相同的数据就合并更新没有就插入，不局限于主键)
TO_CHAR         =>  VARCHAR_FORMAT，TO_CHAR，DB2两种语法都支持，前者为正统身份，后者为前者的别名
TO_DATE         =>  TIMESTAMP_FORMAT，TO_DATE，DB2两种语法都支持，前者为正统身份，后者为前者的别名
TO_NUMBER       =>  DECFLOAT_FORMAT，TO_NUMBER，DB2两种语法都支持，前者为正统身份，后者为前者的别名
DECODE          =>  DECODE，木有变化
NVL             =>  NVL，正常情况下NVL只支持两个参数，但DB2的NVL牛X点，支持不少于两个的参数，功能与标准函数COALESCE相同（貌似强，实则比较懒）
EXTRACT         =>  虽然DB2也支持EXTRACT，但是与mysql中的效果并不一样，所以TO_CHAR(date, 'YYYYMM')效果好些


第二节

2.1
mysql里的CONCAT函数形式为CONCAT(param1, param2, [,param3 ...])，而DB2只支持两个参数，所以如果想拼接两个以上的参数，只能用操作符“||”，例如('a' || 'b' || 'c' ...)。

2.2
SELECT DISTINCT a, b, c FROM test 
SELECT a, b FROM test order by c
-- 当被检索的表中含有CLOB字段 c 时，则不可以使用DISTINCT，或是利用CLOB字段进行排序

2.3
mysql支持布尔型，其实就是整形，0代表false非0代表true，因此表达式1或0可以在mysql的WHERE语句中使用，如：
mysql> select max(id) from role where 1;
+---------+
| max(id) |
+---------+
|      62 |
+---------+
1 row in set (0.25 sec)

mysql> select max(id) from role where 0;
+---------+
| max(id) |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)
但DB2仅仅把这样的表达式当字面值处理，所以处理时会出错（终于正常了一把：）

2.4
DB2使用有自己的伪表SYSIBM.DUMMY1，另外还有一个与Oracle呼应的SYSIBM.DUAL，从http://www.ibm.com/developerworks/data/library/techarticle/dm-0707rielau/index.html可以找到一些关于Oracle和DB2的资料。
下面是DUMMY和DUAL的解释：
Sometimes all that is required of the data server is the computation of a simple scalar expression, or the retrieval of a built-in variable, such as the current time. However, since SQL is table centric such a task is not nearly as trivial and consistently solved as one might expect. One possible solution is to provide a dummy table that has one row and one column, and can serve up the environment to perform the computation. In DB2, this dummy table is called SYSIBM.SYSDUMMY1. In Oracle, it is called DUAL. So in DB2 Viper 2, you can also use DUAL. Note that DUAL does not require a schema. If the functionality is enabled, any unqualified table reference named "DUAL" is presumed to be this one. So it is best not to use DUAL in your regular schema. 


第三节

3.1
关于ROW_NUMBER和FETCH FIRST n ROWS ONLY的解释：
The ROW_NUMBER (or ROWNUMBER) function computes the sequential row number of the row within the window defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in arbitrary order, as returned by the subselect (not according to any ORDER BY clause in the select-statement).
If the FETCH FIRST n ROWS ONLY clause is used along with the ROW_NUMBER function, the row numbers might not be displayed in order. The FETCH FIRST clause is applied after the result set (including any ROW_NUMBER assignments) is generated; therefore, if the row number order is not the same as the order of the result set, some assigned numbers might be missing from the sequence.

3.2
关于注册变量，下面四个表达式是DB2中特有的注册变量的概念，非函数
CURRENT TIMESTAMP
CURRENT TIMEZONE
CURRENT DATE
CURRENT SQLID
以下是对注册变量的解释
A special register is a storage area that is defined for an application process by the database manager. It is used to store information that can be referenced in SQL statements. A reference to a special register is a reference to a value provided by the current server. If the value is a string, its CCSID is a default CCSID of the current server. - SQL Reference, Volume 1 Updated November, 2009. More examples: CURRENT USER, CURRENT SCHEMA, CURRENT SERVER and so on.

3.3
别名可以在ORDER子句中使用（有点奇怪），GROUP中不可以只用（还算正常）

3.4
在编写存储过程时可能会出现与关键字"deterministic"有关的错误，这时可以通过添加行为特性（behavioral characteristic）DETERMINISTIC来解决
相关解释：
DETERMINISTIC or NOT DETERMINISTIC
The DETERMINISTIC or NOT DETERMINISTIC clause specifies whether the function is deterministic.
NOT DETERMINISTIC: Specifies that the function will not always return the same result from successive function invocations with identical input arguments. NOT DETERMINISTIC should be specified if the function contains a reference to a special register or a non-deterministic function.
DETERMINISTIC: Specifies that the function will always return the same result from successive invocations with identical input arguments.
A UDF that returns the temperature in Celsius provided that a Farenhait temperature is deterministic, because no matter under which circumstances it is called, it always will return the same result when parameter values are equal.
A UDF that accesses a thermometer and returns the temperature is non-deterministic because it may provide different results even if the received parameters are equal.

3.5
在使用JOIN和MERGE语句时，UDF（用户定义函数）是不可以使用的，不过系统内置的函数倒是可以，至少在我用过的9.7版本可以。

3.6
使用CASE表达式时，特别要注意返回结果的类型，尽可能保证返回结果类型唯一或是可以通过类型提升来保持一致性，如果碰到CASE语句执行失败，可以尝试通过TO_CHAR或TO_NUMBER或TO_DATE来进行类型转换。
如下例，如果不是用TO_CHAR的话，语句就会执行失败，因为部分数据的返回值是字符“-”
select ... ..., case when sum(coalesce(id, 0)) > 0 then to_char(round(sum(coalesce(length, 0)), 2)) else '-' end test_expr
  from (... ...) x
 group by ... ...
再来一个例子
/*
SELECT 2009 || (CASE WHEN 1 < 10 THEN 'a' || 1 ELSE 1 END) FROM SYSIBM.DUAL
UNION
*//* MIXED TYPE */
SELECT 2009 || (CASE WHEN 1 < 10 THEN '0' || 1 ELSE 1 END) FROM SYSIBM.DUAL /* INTEGER TYPE */
UNION
SELECT 2009 || (CASE WHEN 3 < 10 THEN '0' || 3 ELSE TO_CHAR(3) END) FROM SYSIBM.DUAL /* CHAR TYPE */;


第四节

4.1
DB2参考文档，中英文都有
ftp://ftp.software.ibm.com/ps/products/db2/info/vr97/pdf/en_US/
ftp://ftp.software.ibm.com/ps/products/db2/info/vr97/pdf/zh_CN/

4.2
共享一个初级入门的好文章：）
http://www.michael-thomas.com/tech/db2/db2_survival_guide.htm 

4.3
备份还原或拷贝数据库
Backup and Restore (also Copy a DB).
Create a directory to backup to:
Ex: C:/db2/mybackup
Backup your DB:
Ex: db2 => backup database SAMPLE TO c:/db2/mybackup
Restore the DB:
db2 => restore database SAMPLE from C:/db2/mybackup
Restore the DB to another name (ie: Copy DB):
db2 => restore database SAMPLE from C:/db2/mybackupintoMYSAMPLE