DB2 目錄視圖在專案全生命周期中的最佳實踐指南

前言

DB2 目錄視圖 相當於資料字典，記錄所有資料庫物件屬性與狀態。熟練運用目錄視圖能在設計、開發、部署、維運各階段大幅提升效率、避免錯誤。本文結合實戰案例，示範四大階段的典型應用場景，並附贈自研 DDL 擷取工具。

設計階段：避免結構冗余

實踐一：新建表前檢查重複欄位

擬建 customer_info 表，查詢 SYSCAT.COLUMNS 找出與既有表 customer 重複的欄位定義：

SELECT c.colname, c.tabschema, c.tabname
FROM (
  SELECT colname, COUNT(*) FROM syscat.columns
  WHERE colname IN (SELECT colname FROM syscat.columns WHERE tabname='CUSTOMER_INFO' AND tabschema='TEST')
  GROUP BY colname HAVING COUNT(*) > 1
) AS temp
JOIN syscat.columns c ON temp.colname = c.colname;

結果顯示 CUST_ID、CUST_NAME 於兩表重複，可據此調整設計符合第三正規化。

開發階段：快速診斷 DDL 錯誤

實踐二：解決 ALTER TABLE DROP COLUMN 失敗

嘗試刪除 customer_info.cust_name 報錯 SQL0270N Reason code=100。查詢 SYSCAT.TABLES.DATACAPTURE 發現該表開啟 DATA CAPTURE CHANGES，此屬性禁止刪除欄位。關閉後即可成功：

SELECT datacapture FROM syscat.tables WHERE tabname='CUSTOMER_INFO' AND tabschema='TEST';  -- 回傳 Y
ALTER TABLE test.customer_info DATA CAPTURE NONE;
ALTER TABLE test.customer_info DROP cust_name;  -- 成功

部署階段：依賴關係分析與影響評估

實踐三：部署前掃描物件依賴鏈

使用遞歸 CTE 整合 SYSCAT.VIEWDEP、FUNCDEP、ROUTINEDEP、PACKAGEDEP，一次查出所有直接/間接依賴某表的視圖、函數、預存程序：

WITH OBJ AS (...), PROC AS (...), RPL AS (...)  -- 完整 SQL 見原文
SELECT DISTINCT ... FROM RPL, OBJ WHERE ROOTSCHEMA=OBJ.SCHEMA AND ROOTNAME=OBJ.NAME ORDER BY ...;

結果範例：

部署時：視圖需重建，函數/預存程序需 REBIND 套件。

維運階段：權限稽核與自動化監控

實踐四：清查異常執行權限

規定僅 PROGRAM 帳號可執行程序。查詢 SYSCAT.ROUTINES + ROUTINEAUTH 發現 DEVELOPER、TESTER 擁有執行權，立即撤銷：

SELECT rout.routinetype, rout.routineschema, rout.routinename, auth.grantee, auth.grant_time, auth.executeauth
FROM syscat.routines rout, syscat.routineauth auth
WHERE rout.routineschema=auth.schema AND rout.specificname=auth.specificname
AND rout.routineschema='TEST' AND auth.grantee<>'PROGRAM';

REVOKE EXECUTE ON FUNCTION TEST.FUNCTION1 FROM USER DEVELOPER RESTRICT;
REVOKE EXECUTE ON PROCEDURE TEST.PROCEDURE1 FROM USER TESTER RESTRICT;

建議配合排程腳本定期掃描權限、監控資料庫容量與物件狀態。

工具推薦：DB2ExtractDDL — 目錄視圖驅動的 DDL 擷取器

作者早期以 Java 開發，支援表/視圖/函數/預存程序/觸發器 DDL 擷取，含欄位細節、索引、權限等。範例輸出：

DROP TABLE TEST.TEST_TABLE @
CREATE TABLE TEST.TEST_TABLE ( PK_COL CHAR(10) NOT NULL, COL1 CHAR(1) NOT NULL, COL2 DATE, COL3 CHAR(35) DEFAULT NULL, COL4 INTEGER )
IN TEST_DATA_01 INDEX IN TEST_INDX_01 @
ALTER TABLE TEST.TEST_TABLE DATA CAPTURE CHANGES @
... (索引、PK、GRANT 等) @
Complete!

工具可批量化、整合至 CI/CD，原始碼見文末下載，適合初學者快速掌握數十個目錄視圖用法。

結語

目錄視圖是 DB2 核心設施，貫穿專案全生命周期。建議開發與 DBA 人員將其納入日常工具箱，善用 SQL 與目錄視圖結合解決複雜問題。