Oracle 遷移 DB2 完整解決方案：語法對照、函數對應、遞迴查詢、外部連接與分頁查詢

1. DECODE → CASE 表達式

Oracle DECODE 在 DB2 使用標準 SQL CASE 實現，支援兩種語法：

-- 搜尋型 CASE
CASE
  WHEN 條件 THEN 結果1
  ELSE 結果2
END

-- 簡單型 CASE
CASE 運算式1
  WHEN 運算式2 THEN 結果1
  ELSE 結果2
END

範例：月份代碼轉名稱

SELECT ORDNO, CUSNO,
  CASE MONTH(SHIPDATE)
    WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
    WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun'
    WHEN 7 THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
    WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec'
  END
FROM FILE;

實務對照

2. START WITH ... CONNECT BY → WITH RECURSIVE CTE

Oracle 階層查詢在 DB2 使用 WITH RECURSIVE (或 WITH) 遞迴共用資料表運算式 (CTE)：

Oracle 原始

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

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. DUAL 表 → SYSIBM.SYSDUMMY1

-- Oracle
SELECT 15 AS ttt FROM DUAL;

-- DB2
SELECT 15 AS ttt FROM SYSIBM.SYSDUMMY1;

4. 日期轉換函數對照

範例：日期區間查詢

-- Oracle
WHERE m.blqsrq >= TO_DATE('2006-10-27','YYYY-MM-DD')
  AND m.blqsrq < TO_DATE('2006-10-27','YYYY-MM-DD') + 1

-- DB2
WHERE DATE(m.blqsrq) >= DATE('2006-10-27')
  AND DATE(m.blqsrq) < DATE('2006-10-27') + 1 DAY

5. NVL → COALESCE / VALUE

• COALESCE(expr1, expr2, ...)：標準 SQL，回傳第一個非 NULL 引數，型別可異。
• VALUE(expr1, expr2)：DB2 專用，要求兩引數型別相同。

注意：Oracle NVL(num, '') 允許字串預設值，DB2 VALUE(num, '') 會報型別不符，需用 COALESCE(num, 0) 或 VALUE(num, 0)。

6. 外部連接語法標準化

DB2 採 ANSI SQL-92 標準，無 Oracle (+) 符號：

7. LIKE 謂詞限制

DB2 LIKE 後僅接受：常數、特殊暫存器、主機變數、純量函數、上述運算式串接，**不支援直接欄位參考**。

8. 鎖定行為與隔離級別深度解析

實測結論摘要 (RR/RS 隔離級別)

^※ 普通索引在 RR 下不鎖列不鎖表，原因涉及索引掃描範圍與幻讀防護機制，需進一步研究。

四大隔離級別特性

• RR (Repeatable Read)： 掃描到的所有記錄加鎖，鎖數量最大，索引可大幅減少掃描行數。
• RS (Read Stability)： 僅鎖定符合條件記錄；無 WHERE 則鎖全表。
• CS (Cursor Stability - 預設)： 僅鎖定目前游標指向記錄，並行度最高。
• UR (Uncommitted Read)： 唯讀不加行鎖；非唯讀同 CS。

9. DB2 分頁查詢

SELECT * FROM (
  SELECT col1, col2, col3,
         ROW_NUMBER() OVER(ORDER BY sort_col ASC) AS rn
  FROM table_name
) AS t
WHERE t.rn BETWEEN 10 AND 20;

遷移檢查清單

• 所有 DECODE → CASE
• 階層查詢 → WITH RECURSIVE
• DUAL → SYSIBM.SYSDUMMY1
• 日期函數對照表逐一替換
• NVL → COALESCE (注意型別)
• 外部連接改寫為 ANSI JOIN
• 檢查 LIKE 後是否為欄位參考
• 評估隔離級別對並行效能影響
• 分頁查詢改用 ROW_NUMBER() OVER()