DB2 開發實戰技巧大全：從存儲過程到性能優化

前言

在長期的 DB2 開發與維護實踐中，積累了不少實用的編程技巧與管理經驗。本文將這些經驗整理為四大模塊——編程技巧、性能優化、對象管理、系統管理，供開發人員與 DBA 參考。

一、DB2 編程核心技巧

1.1 存儲過程創建避坑指南

創建存儲過程時，CREATE PROCEDURE 關鍵字後絕不可使用 Tab 鍵，只能用空格，否則編譯會失敗。這是一個極易被忽視的語法細節。

1.2 臨時表使用規範

• 臨時表只能建立在 USER TEMPORARY TABLESPACE 上；若數據庫僅有系統臨時表空間，則無法創建臨時表。
• DB2 臨時表的生命週期為會話級，多線程環境下難以控制，建議謹慎使用。
• 創建時加上 WITH REPLACE 選項，可避免同會話重複創建報錯，並省去顯式 DROP。

1.3 限制返回行數

使用 FETCH FIRST n ROWS ONLY 子句可快速取前 N 條記錄：

SELECT * FROM tb_market_code FETCH FIRST 1 ROWS ONLY;

但該寫法不支持直接配合 SELECT ... INTO 變量。正確做法是聲明游標並加 FOR UPDATE：

DECLARE v_market_code CHAR(1);
DECLARE cursor1 CURSOR FOR SELECT market_code FROM tb_market_code FETCH FIRST 1 ROWS ONLY FOR UPDATE;
OPEN cursor1;
FETCH cursor1 INTO v_market_code;
CLOSE cursor1;

1.4 游標進階用法

事務與游標存活

未加 WITH HOLD 的游標在 COMMIT/ROLLBACK 時會自動關閉。若循環中需提交事務又要保持游標，必須使用 DECLARE ... CURSOR WITH HOLD 配合 CONTINUE HANDLER FOR NOT FOUND 的傳統寫法。

FOR 迴圈游標

語法簡潔，無需 OPEN/FETCH/CLOSE，但不支持 WITH HOLD，循環體內不可有提交/回滾操作。

當前行更新

使用 WHERE CURRENT OF cursor_name 更新游標指向的行，前提是游標聲明時帶 FOR UPDATE，且查詢不能包含 GROUP BY、DISTINCT、ORDER BY 等子句（UNION ALL 除外）。

1.5 常用函數替代方案

1.6 關鍵 SQL 語法

• Identity 列：GENERATED ALWAYS AS IDENTITY (START WITH 500 INCREMENT BY 1)，每表僅允許一個。
• 獲取影響行數：GET DIAGNOSTICS v_cnt = ROW_COUNT; 僅對 INSERT/UPDATE/DELETE 生效。
• 存儲過程返回結果集：聲明 RESULT SETS n，游標用 WITH RETURN TO CALLER，調用端用 ASSOCIATE RESULT SET LOCATOR + ALLOCATE CURSOR 接收。
• VALUES 賦值：多個 SET 合併為一條 VALUES (...) INTO ... 提升效率，但不能賦 NULL。
• 指定隔離級別：SELECT ... WITH UR（Uncommitted Read）。
• ATOMIC / NOT ATOMIC：ATOMIC 將塊視為單一事務單元，任一語句失敗整塊回滾。

1.7 存儲過程互調與 C SP 注意點

• SQL SP 可相互調用，亦可調 C SP；C SP 調 SQL SP 在新版本已支持。
• C SP 參數風格建議用 PARAMETER STYLE GENERAL WITH NULLS，否則 SQL SP 調用會報保護錯誤。
• FENCED 獨立地址空間較安全；UNFENCED 共享調用者空間，可獲取調用者 PID 等上下文信息。

1.8 錯誤處理模式

調用返回結果集/輸出參數的 SP 會產生 SQLWARNING。用標志變量屏蔽預期內的警告：

DECLARE V_STATUS INTEGER DEFAULT 0;
CALL SPNAME;
SET V_STATUS = 1;
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
  IF V_STATUS <> 1 THEN
    -- 真實警告處理
    SET V_STATUS = 0;
  END IF;
END;

二、DB2 編程性能優化

• 大數據遷移：EXPORT + LOAD 優於 SELECT INTO，因 LOAD 不寫日誌。
• 大 SQL 策略：DB2 優化器擅長處理複雜 SQL，盡量合併多條簡單語句為一條大 SQL，減少語句解析開銷。（Oracle 則相反）
• SP 語言選擇：邏輯複雜、SQL 簡單 → C SP；SQL 複雜、邏輯簡單 → SQL SP。
• Hash Join 與 RR→RS：
  • db2set DB2_HASH_JOIN=Y 啟用哈希連接，大表無索引 Join 性能提升顯著。
  • db2set DB2_RR_TO_RS=Y 將 RR 降為 RS，減少 Next-Key 鎖管理開銷。
• 避免 COUNT(*)/EXISTS：
  • 直接 SELECT INTO 捕獲 SQLCODE=100（無數據）或異常（多行）最快。
  • 需判斷是否唯一時，用游標計數器掃描到預期值即停止，避免全表掃描。

三、表與存儲過程管理查詢

-- 查看 SP 文本
SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME='PROC1';

-- 查看表結構
DESCRIBE TABLE SYSCAT.PROCEDURES;

-- 表被哪些 SP 使用
SELECT PROCNAME FROM SYSCAT.PROCEDURES
WHERE SPECIFICNAME IN (SELECT DNAME FROM SYSIBM.SYSDEPENDENCIES
  WHERE BNAME IN (SELECT PKGNAME FROM SYSCAT.PACKAGEDEP WHERE BNAME='TB_BRANCH'));

-- SP 使用了哪些表
SELECT BNAME FROM SYSCAT.PACKAGEDEP WHERE BTYPE='T' AND PKGNAME IN
  (SELECT BNAME FROM SYSIBM.SYSDEPENDENCIES WHERE DNAME IN
    (SELECT SPECIFICNAME FROM SYSCAT.PROCEDURES WHERE PROCNAME='PR_CLEAR_MATCH_DIVIDE_SHA'));

-- 修改表結構（一次加多列）
ALTER TABLE tb_test ADD COLUMN t1 CHAR(1) ADD COLUMN t2 CHAR(2) ADD COLUMN t3 INT;

四、DB2 系統管理常用命令

4.1 實例與數據庫操作

db2start / db2stop force
db2 activate db HEAD / db2 deactivate db HEAD
db2 connect to HEAD user db2inst1 using password
db2 list applications show detail
db2 "force application (236)"  -- 236 為應用句柄
db2 list database directory

4.2 關鍵配置參數

4.3 環境與升級

• AIX 下 SP 編譯環境：db2set DB2_SQLROUTINE_COMPILE_COMMAND="xlc_r ..." 並 DB2_SQLROUTINE_KEEP_FILES=1 保留中間文件。
• FixPak 升級後必須執行 db2iupdt 實例名 升級實例，否則 db2start 會報符號缺失錯誤。
• 升級後重綁系統包：db2 bind @db2ubind.lst、db2 bind @db2cli.lst。

4.4 死鎖排查流程

1. db2 update dbm cfg using dft_mon_lock on 開啟鎖監控
2. db2 get snapshot for locks on DBNAME > snap.log 獲取快照
3. 分析 snap.log 中的 Application Handle 定位死鎖應用
4. 分析結束後 db2 update dbm cfg using dft_mon_lock off 關閉監控