在 DB2 中實現 Oracle 風格 ALERT 機制：會話間異步消息通信完整實現

目標

移植 Oracle DBMS_ALERT 功能至 DB2，實現多會話間異步消息傳遞：一會話發送（Signal），另一會話等待接收（WaitOne/WaitAny），支持超時、註冊/刪除警告。

架構設計

• 核心表：ALERTS.DBMS_ALERT_INFO 存儲警告名、會話 ID、消息、狀態標誌
• 存儲過程：REGISTER、SIGNAL、WAITONE、WAITANY、REMOVE、DELETE
• UDF：DELAY(秒) 實現輪詢間隔（Java 實現 Thread.sleep）、SESSION_ID() 獲取會話標識（C 實現）
• 權限：所有對象授予 PUBLIC EXECUTE，由 DBADM/SYSADM 創建

核心對象 DDL 與邏輯

1. 警告信息表

CREATE TABLE ALERTS.DBMS_ALERT_INFO (
  NAME VARCHAR(60) NOT NULL,
  SID VARCHAR(50) NOT NULL,
  MESSAGE VARCHAR(1800),
  CHANGED CHAR(1) NOT NULL DEFAULT 'N',
  CONSTRAINT PK_DBMS_ALERT_INFO PRIMARY KEY (NAME),
  CONSTRAINT CC_DBMS_ALERT_INFO CHECK (CHANGED IN ('Y','N','y','n'))
);
GRANT SELECT,INSERT,UPDATE,DELETE ON ALERTS.DBMS_ALERT_INFO TO PUBLIC;

2. 關鍵存儲過程邏輯摘要

3. UDF 實現要點

• DELAY：Java FENCED UDF，調用 Thread.sleep，比 SQL 循環高效
• SESSION_ID：C UDF，讀取 SQLUDF_DBINFO 結構獲取會話標識

安裝與測試步驟

1. 解壓 alert.zip，編輯 alertsetup.bat 設置 DB2HOMEPATH 與 JAR 路徑
2. DB2 命令窗口運行 alertsetup.bat SAMPLE user pwd
3. 打開兩個 CLP 窗口（會話 A/B），連接 SAMPLE
4. 測試流程：

   -- 會話 A
   CALL ALERTS.REGISTER('abc');
   CALL ALERTS.SIGNAL('abc', 'Hello from A');
   
   -- 會話 B
   CALL ALERTS.WAITONE('abc', ?, ?, 30);  -- 接收消息
   
   -- 會話 A 註冊新警告
   CALL ALERTS.REGISTER('xyz');
   CALL ALERTS.SIGNAL('xyz', 'Msg via xyz');
   
   -- 會話 B 等待任意
   CALL ALERTS.WAITANY(?, ?, ?, 30);
   
   -- 清理
   CALL ALERTS.REMOVE('abc');  -- 僅 A 可成功
   CALL ALERTS.DELETE('xyz');  -- B 可成功

關鍵設計決策

• 使用表而非隊列：簡單、事務性強、易查詢審計
• CHANGED 標誌實現「單次投遞」語義：讀取後自動重置
• WAITONE 固定間隔、WAITANY 指數退避：平衡響應與資源消耗
• UDF 實現延遲與會話 ID：繞過 SQL PL 無睡眠函數、無會話上下文變量的限制