您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 教學

DB2 基礎：深入掌握 DB2 UDB 系統編目查詢技巧

編輯：DB2 教學

前言

資料庫的所有核心資訊幾乎都儲存在系統編目這個元資料倉庫中。元資料描述的是資料庫中資料的邏輯與物理結構，它獨立於實際資料而存在。DB2 UDB 系統編目由一組表與視圖組成，隨資料庫建立自動創建，並由資料庫管理器持續維護。當資料庫物件（如表、索引、視圖）被創建、修改或刪除時，編目表會同步更新，記錄物件定義、權限關係等關鍵資訊。

雖然無法直接操作編目基表，但我們可以透過查詢其上的只讀視圖（SYSCAT 模式）獲取豐富的運維洞察。本文將透過實戰範例，示範如何從系統編目中高效檢索授權、資料類型、約束、相依性、儲存管理及分區等關鍵資訊。

系統編目架構解析

基表層

編目基表位於 SYSIBM 模式，儲存於 SYSCATSPACE 表空間。在分區資料庫環境中，編目僅存在於發出 CREATE DATABASE 命令的協調分區。基表之間存在父子關係，例如 SYSIBM.SYSCOLAUTH（欄位權限）是 SYSIBM.SYSTABAUTH（表權限）的子表。

基表依功能分類包括：授權類（SYSDBAUTH、SYSTABAUTH 等）、資料類型與程序類（SYSDATATYPES、SYSROUTINES）、約束類（SYSCHECKS、SYSRELS）、相依性類（SYSDEPENDENCIES、SYSVIEWDEP）、儲存管理類（SYSTABLESPACES、SYSTABLES）、分區管理類（SYSNODEGROUPS、SYSPARTITIONMAPS）等。

視圖層：面向使用者的友善介面

資料庫管理器在基表之上建立兩組視圖：SYSCAT 模式提供唯讀視圖，SYSSTAT 模式提供可更新視圖。所有 SYSCAT 視圖的 SELECT 權限預設授予 PUBLIC，是日常查詢的主要入口。視圖可基於單一或多個基表，欄位名稱常為了易讀性而重新命名。

範例：單表視圖 `SYSCAT.KEYCOLUSE`

CREATE VIEW syscat.keycoluse (constname, tabschema, tabname, colname, colseq)
AS SELECT constname, tbcreator, tbname, colname, colseq
FROM sysibm.syskeycoluse;

此視圖僅重新命名欄位（如 tbcreator→tabschema），提升一致性。

範例：多表連接視圖 `SYSCAT.STATEMENTS`

CREATE VIEW syscat.statements (pkgschema, pkgname, unique_id, version, stmtno, sectno, seqno, text)
AS SELECT s.plcreator, s.plname, s.unique_id,
  (SELECT p.pkgversion FROM sysibm.sysplan p
   WHERE s.plcreator = p.creator AND s.plname = p.name AND s.unique_id = p.unique_id),
  s.stmtno, s.sectno, 1, s.text
FROM sysibm.sysstmt s;

此視圖連接 SYSPLAN 與 SYSSTMT，並透過子查詢補充套件版本資訊。

實用查詢場景全景示範

1. 授權資訊統一檢索

授權資料分散於多個視圖，可用 UNION 整合並以 SUBSTR 格式化輸出：

SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, 'Database' FROM syscat.dbauth
UNION
SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, '表空間' FROM syscat.tbspaceauth
UNION
SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, 'Schema' FROM syscat.schemaauth
UNION
SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, 'Table' FROM syscat.tabauth
UNION
SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, 'Index' FROM syscat.indexauth
UNION
SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, 'Column' FROM syscat.colauth
UNION
SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, 'Package' FROM syscat.packageauth
UNION
SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, 'Routine' FROM syscat.routineauth
UNION
SELECT DISTINCT SUBSTR(grantee,1,16) AS grantee, granteetype, 'Server' FROM syscat.passthruauth
ORDER BY grantee, granteetype;

進階範例：查詢擁有 DBADM 權限的用戶、或由當前用戶授予的表權限。

2. 使用者定義型別與程序查詢

-- 查詢非系統模式的使用者定義型別
SELECT typeschema, typename, sourcename, metatype
FROM syscat.datatypes WHERE typeschema != 'SYSIBM';

-- 查詢非系統模式的使用者定義程序
SELECT routineschema, routinename, routinetype, origin, language, text
FROM syscat.routines
WHERE SUBSTR(routineschema,1,3) NOT IN ('SQL','SYS');

3. 約束資訊深度挖掘

-- 找出 MELNYK 結構下不可為空的欄位
SELECT SUBSTR(tabname,1,16) AS tabname, SUBSTR(colname,1,16) AS colname, nulls
FROM syscat.columns WHERE tabschema = 'MELNYK' AND nulls = 'N';

-- 列出含唯一約束（非主鍵）的表
SELECT SUBSTR(tabschema,1,16) AS tabschema, SUBSTR(tabname,1,16) AS tabname, keyunique
FROM syscat.tables WHERE keyunique > 0;

-- 列出所有主鍵欄位及其在鍵中的順序
SELECT SUBSTR(tabschema,1,16) AS tabschema, SUBSTR(tabname,1,16) AS tabname,
       SUBSTR(colname,1,16) AS colname, keyseq
FROM syscat.columns WHERE keyseq IS NOT NULL;

-- 綜合查詢：唯一、主鍵、外鍵所涉欄位及約束類型
SELECT SUBSTR(k.constname,1,20) AS constname, t.type,
       SUBSTR(k.tabname,1,20) AS tabname, SUBSTR(k.colname,1,16) AS colname, k.colseq
FROM syscat.keycoluse k, syscat.tabconst t
WHERE k.constname = t.constname;

-- 列出所有檢查約束定義
SELECT SUBSTR(constname,1,20) AS constname, SUBSTR(tabname,1,20) AS tabname,
       SUBSTR(text,1,32) AS text FROM syscat.checks;

4. 物件相依關係分析

透過 SYSCAT.VIEWDEP 可追蹤視圖依賴的基表：

SELECT DISTINCT SUBSTR(a.tabschema,1,16) AS tabschema,
       SUBSTR(a.tabname,1,16) AS tabname, a.type, SUBSTR(a.tbspace,1,16) AS tbspace
FROM syscat.tables a, syscat.viewdep b
WHERE a.type = 'T' AND a.tabname = b.bname AND a.tabschema = b.bschema
  AND b.btype = 'T' AND b.viewname = 'COLUMNS' AND b.viewschema = 'SYSCAT';

此查詢等同於 Control Center「顯示相關物件」功能產生的 SQL。

5. 儲存管理與分區資訊

-- 查詢 MELNYK 表所在表空間屬性
SELECT SUBSTR(t.tabname,1,12) AS tabname, t.tbspaceid AS tsp_id,
       s.tbspacetype AS tsp_type, s.datatype, s.extentsize, s.pagesize, s.dbpgname
FROM syscat.tables t, syscat.tablespaces s
WHERE t.tabschema = 'MELNYK' AND t.type = 'T' AND t.tbspaceid = s.tbspaceid;

-- 查詢資料庫分區組與分區對應關係
SELECT n.dbpgname, SUBSTR(n.definer,1,16) AS definer, n.pmap_id, n.create_time,
       d.dbpartitionnum, d.in_use
FROM syscat.dbpartitiongroups n, syscat.dbpartitiongroupdef d
WHERE n.dbpgname = d.dbpgname;

可更新編目視圖與統計資訊維護

SYSSTAT 模式視圖包含查詢優化器所需的統計欄位（如資料分布）。擁有 CONTROL 權限或 DBADM 權限的用戶可手動更新這些統計值以影響存取計劃，但建議優先執行 RUNSTATS 確保基準準確。手動調整統計值屬進階調校，超出本文範圍。

編目存取控制最佳實踐

資料庫建立時，SYSCAT 視圖的 SELECT 權限預設授予 PUBLIC。若資料庫含敏感資訊，應回收此權限並按需授予特定用戶，操作需 DBADM 或 SYSADM 權限。範例：創建僅列出當前用戶擁有 SELECT 權限表的視圖 MYSELECTS，再連接 SYSCAT.TABLES 獲取表類型。