減少 DB2 for z/OS 交互：第三部分——重新思考常規事務的 SQL 模式

前言：消除不必要的 DB2 對話

前兩期專欄探討了不必要的 SQL 及執行過多、應以新特性替代的案例。本期深入剖析更多 SQL 應被推遲、合併或徹底避免的情境，最終目標一致：減少與 DB2 的交互次數。

三步驟移除螢幕應用的冗餘重讀

螢幕顯示應用因大量不必要的重讀聞名：讀取一行並顯示；用戶修改值並按功能鍵更新；程式取得控制權並重讀該行（常用 FOR UPDATE OF 游標）；FETCH 後進行「前後」檢查，查看螢幕顯示期間是否有其他用戶修改。若行未變，完成 UPDATE；若變，返回錯誤。

樂觀鎖定：假設多數情況行不會變

解決之道在於「樂觀鎖定」技術。我們確信多數情況行不會在顯示期間變更，UPDATE 的杯子是半滿而非半空。因此無需重讀即可直接 UPDATE，但需用經驗證技術確保針對未變更行的 UPDATE 已完成：在 WHERE 子句增加一或多個謂詞測試變更。選項包括：

• 表設計包含 DB2 維護的 ROW UPDATE TIMESTAMP（DB2 9 新特性），WHERE 子句檢查該列是否與初始值相同。
• 比對用戶維護的最後更新時間戳與初始值（並在 SET 子句重置該時間戳）。
• 在行的每個列中包含 WHERE 謂詞，確認每列值與初始值相同。
• 僅在關鍵相關列中包含 WHERE 謂詞，確保行仍符合資格並驗證我們關注的未變列。

為效能而生的重大變革：從 528 次對話降至 3 次

場景：擁有 TRANDATE、PROCESS-DATE、CUSTNO 索引，需 FETCH 行並提交報告，刪除條件：超過 3 月、狀態無效、CUSTNO 在程式工作儲存清單中。

原始模式：極度低效的往返

DECLARE CURSOR ... FOR SELECT ... FROM bigtable WHERE trandate < CURRENT DATE - 3 MONTHS AND status = :hv-inactive
OPEN -> FETCH 循環 -> 寫報表 -> DELETE WHERE CURRENT OF CURSOR

若 3,000 索引行指向 TRANDATE 超 3 月的表行，但只有 500 行 STATUS 為 inactive，最壞情況：

• OPEN 1 次 CONNECT
• 3,000 次 FETCH CONNECT：每次多層索引 GETPAGE、讀 6 索引項、對 6 RID 發表 GETPAGE 並等待 I/O、套用 STATUS 謂詞拒 5 納 1
• 500 合格行中僅 25 CUSTNO 在工作清單（程式端拒絕屬 Stage 3 謂詞，比 Stage 2 更糟）
• 25 次 DELETE CONNECT
• 重複 24 次，共 528 CONNECTS，海量 GETPAGE

四大改進：對話從 528 降至 3，表 GETPAGE 從 3,000 降至 ≤25

1. 索引加入 STATUS，使有效性僅靠索引判斷
2. 游標改用 ROWSET POSITIONING，每次讀多行
3. 利用 SELECT FROM DELETE 直接查看被刪行
4. 將數字 INSERT 進 CREATED GLOBAL TEMPORARY TABLE (CTT_CUST)，SQL 中用臨時表消除 STAGE 3 取消資格

優化後代碼

DECLARE CURSOR csr-select-and-delete-old-rows WITH ROWSET POSITIONING FOR
SELECT ponbr, custno, trandate, process-date
FROM FINAL TABLE (
  DELETE FROM bigtable
  WHERE trandate < CURRENT DATE - 3 MONTHS
    AND status = :hv-inactive
    AND custno IN (SELECT custno FROM ctt_cust)
)
OPEN -> FETCH ROWSET FOR 100 ROWS INTO 陣列 -> CLOSE

新最佳情況：OPEN 1 CONNECT，FETCH 1 CONNECT（讀 25 索引項、25 表 GETPAGE、每行發現即 DELETE、選取列插入 FINAL TABLE、回傳 25 行），CLOSE 1 CONNECT。總計 3 CONNECTS，表 GETPAGE ≤25。

消除 Stage 3 謂詞：將過濾下推至 DB2

顧問經驗顯示，主要共同編碼失誤之一是在程式而非 DB2 中驗證拒絕行。Stage 3 謂詞特別是拒絕大部分行者，通常源於未用 WHERE 子句的舊 VSAM 邏輯：鍵控讀取後套用 IF/THEN/ELSE。這恰恰是構建 WHERE 謂詞的良好基礎，能讓行在 DB2 位址空間快速被拒絕。

結語：讓「必要性」成為編碼習慣

本三部曲強調完全消除或延遲至最後一刻與 DB2 對話的重要性。願未來每編一條 SQL，腦海都有個小聲音問：「這有必要嗎？能否一次讀/維護多行？能否推遲直到確認需要？」