簡介
IBM DB2 數據庫同 Oracle,MySQL,MS SQL Server 等其他流行數據庫一樣,擁有自己的一套數據字典去存儲數據庫對象的屬性和狀態信息。在 DB2 數據庫中,用戶可以通過訪問目錄視圖(Catalog VIEws)來獲取這些信息。
在 DB2 數據庫應用項目的開發過程中,需要項目的設計開發人員和管理維護人員對目錄視圖有必要的了解,因為在實際軟件開發項目生命周期的各個階段(通常都要包括設計,開發,管理和錯誤分析),都可以利用對目錄視圖的查詢和分析,去有效的提高工作效率,同時避免潛在錯誤,以及更好的管理數據庫。
一些 DB2 的初學者和開發人員,往往只會專注於應用程序(SQL/PL)的實現而忽視了對目錄視圖這一部分的了解。希望初學者能夠結合目錄視圖去加深對各種數據庫對象的認識;希望數據庫開發人員能將目錄視圖(數據字典)做為自己的常規武器運用到日常工作中。當您需要從基於其他數據庫的開發工作轉入 DB2 數據庫開發時,熟悉 DB2 目錄視圖是一個很好的切入點。為了方便您學習和加深印象,本文會結合一些實際的例子描述在 DB2 數據庫應用項目的生命周期中各個階段如何充分使用 DB2 目錄視圖,最後還將介紹一個學習和使用目錄視圖的工具。
請注意文中所采用的例程適用於 DB2 for Linux, UNIX and Windows V8.0 之後的版本。通過 IBM 產品技術支持站點 可以獲得 DB2 for Linux, UNIX and Windows V8.0 到最新 V9.7 的各個版本的在線和離線文檔。目錄視圖相關信息可以查閱 DB2 各個版本 PDF 文檔中的 SQL 參考卷一中的附錄 D(SQL Reference Volume.1 - Appendix D. Catalog vIEws)。
設計階段的實踐案例
實踐一 : 避免數據庫設計冗余
當數據庫應用設計人員面對新的需求需要創建新表(customer_info)時,可以通過查詢目錄視圖中的表字段信息(syscat.columns),查看在其他已經存在的表中是否有重復的字段定義,精簡表字段的數量,滿足數據庫設計的第三范式即避免數據的冗余。在 清單 1.中通過例程就可以查出表 customer 和 customer_info 中關於 cust_name 有重復的定義。在正在進行的項目中我們可以根據這個思路改變例程以滿足自己的定制需求。
清單 1. 通過例程就可以查出表 customer 和 customer_info 中關於 cust_name 有重復的定義
db2=> select c.colname, c.tabschema, c.tabname from
(
select colname, count(*) from syscat.columns
where colname in (select colname from syscat.columns
where tabname = upper('customer_info')
and tabschema = upper('test'))
group by colname
having count(*) > 1
) as temp
join syscat.columns c
on temp.colname = c.colname
COLNAME TABSCHEMA TABNAME
----------- ----------- ------------------
CUST_ID TEST CUSTOMER
CUST_ID TEST CUSTOMER_INFO
CUST_NAME TEST CUSTOMER
CUST_NAME TEST CUSTOMER_INFO
4 record(s) selected.
開發階段的實踐案例
實踐二 : 解決開發過程中遇到的錯誤
在數據庫開發和調試階段,會遇到各種各樣的錯誤,查詢目錄視圖是對錯誤分析的一個有益補充。如 清單 2.所示,當試圖刪除 customer_info 表中的列 cust_name 時發生錯誤。通過分析目錄視圖 syscat.tables 了解到該表設置了 DATA CAPTURE 屬性,意味著該表總是將數據變化寫入系統日志,這個條件阻礙了刪除表字段的操作。在關閉了這個表屬性(DATA CAPTURE NONE)後,字段 cust_name 可以從表中刪除。
清單 2. 當試圖刪除 customer_info 表中的列 cust_name 時發生錯誤
db2=> alter table test.customer_info drop cust_name
DB21034E The command was processed as
an SQL statement because it was not a
valid Command Line Processor command.
During SQL processing it returned:
SQL0270N Function not supported
(Reason code = "100"). SQLSTATE=42997
db2=> select DATACAPTURE from syscat.tables
where tabname = upper('customer_info')
and tabschema = upper('test')
DATACAPTURE
-----------
Y
db2=> alter table test.customer_info data capture none
DB20000I The SQL command completed successfully.
db2=> select DATACAPTURE from syscat.tables
where tabname = upper('customer_info')
and tabschema = upper('test')
DATACAPTURE
-----------
N
db2=> alter table test.customer_info drop cust_name
DB20000I The SQL command completed successfully.
部署階段的實踐案例
實踐三 : 部署前分析依賴關系
在數據庫對象部署前,有經驗的開發或管理人員會檢查數據庫對象之間的依賴關系。除了部署修改過定義(代碼)的對象以外,還需要准備步驟去處理與修改過對象有依賴關系的其他對象。清單 3.顯示了如何查詢依賴於一個表的所有數據庫對象,包括引用這個表的視圖(從 SYSCAT.VIEWDEP),用戶自定義函數(從 SYSCAT.FUNCDEP)和存儲過程(從 SYSCAT.ROUTINEDEP 和 SYSCAT.PACKAGEDEP),如結果所示,其中還返回多層次關系(例如調用這個表的視圖又被其他用戶自定義函數調用)。
清單 3. 如何查詢依賴於一個表的所有數據庫對象
db2=> WITH OBJ as (
select TABSCHEMA as SCHEMA, TABNAME as NAME from SYSCAT.TABLES
where TABSCHEMA = upper('test')
and TABNAME = upper('customer')
),
PROC as (
select PROCSCHEMA, PROCNAME, rd.bname as PKGNAME
from SYSCAT.PROCEDURES p
join syscat.routinedep rd
on( p.PROCSCHEMA = rd.routineschema
and p.specificname = rd.routinename)
),
RPL (
ROOTTYPE, ROOTSCHEMA, ROOTNAME, LEVEL, SCHEMA, NAME, DTYPE,
BTYPE, BSCHEMA, BNAME
) AS (
SELECT
BTYPE AS ROOTTYPE, BSCHEMA AS ROOTSCHEMA, BNAME AS ROOTNAME,
1 as LEVEL, VIEWSCHEMA as SCHEMA, VIEWNAME as NAME,
DTYPE, BTYPE, BSCHEMA,
BNAME
FROM SYSCAT.VIEWDEP ROOT
UNION ALL
SELECT
ROOTTYPE, ROOTSCHEMA, ROOTNAME, PARENT.LEVEL+1,
CHILD.VIEWSCHEMA as SCHEMA,
CHILD.VIEWNAME as NAME, CHILD.DTYPE, CHILD.BTYPE,
CHILD.BSCHEMA, CHILD.BNAME
FROM RPL PARENT, SYSCAT.VIEWDEP CHILD
WHERE PARENT.SCHEMA = CHILD.BSCHEMA
AND PARENT.NAME = CHILD.BNAME
AND PARENT.LEVEL < 50
UNION ALL
SELECT
BTYPE AS ROOTTYPE, BSCHEMA AS ROOTSCHEMA, BNAME AS ROOTNAME,
1 as LEVEL, FUNCSCHEMA as SCHEMA, FUNCNAME as NAME,
'F' as DTYPE, BTYPE,
BSCHEMA, BNAME
FROM SYSCAT.FUNCDEP ROOT
UNION ALL
SELECT
ROOTTYPE, ROOTSCHEMA, ROOTNAME, PARENT.LEVEL+1,
CHILD.FUNCSCHEMA as SCHEMA,
CHILD.FUNCNAME as NAME, 'F' as DTYPE, CHILD.BTYPE,
CHILD.BSCHEMA, CHILD.BNAME
FROM RPL PARENT, SYSCAT.FUNCDEP CHILD
WHERE PARENT.SCHEMA = CHILD.BSCHEMA
AND PARENT.NAME = CHILD.BNAME
AND PARENT.LEVEL < 50
UNION ALL
SELECT
BTYPE AS ROOTTYPE, BSCHEMA AS ROOTSCHEMA, BNAME AS ROOTNAME,
1 as LEVEL, PROC.PROCSCHEMA as SCHEMA,
PROC.PROCNAME as NAME, 'P' as DTYPE,
BTYPE, BSCHEMA, BNAME
FROM SYSCAT.PACKAGEDEP ROOT, PROC PROC
where PROC.PROCSCHEMA = ROOT.PKGSCHEMA
and PROC.PKGNAME = ROOT.PKGNAME
UNION ALL
SELECT
ROOTTYPE, ROOTSCHEMA, ROOTNAME, PARENT.LEVEL+1,
PROC.PROCSCHEMA as SCHEMA,
PROC.PROCNAME as NAME, 'P' as DTYPE, CHILD.BTYPE,
CHILD.BSCHEMA, CHILD.BNAME
FROM RPL PARENT, SYSCAT.PACKAGEDEP CHILD, PROC PROC
WHERE PARENT.SCHEMA = CHILD.BSCHEMA
AND PARENT.NAME = CHILD.BNAME
and PROC.PROCSCHEMA = CHILD.PKGSCHEMA
AND PROC.PKGNAME = CHILD.PKGNAME
AND PARENT.LEVEL < 50
)
SELECT distinct ROOTTYPE, ROOTSCHEMA, ROOTNAME, LEVEL, RPL.SCHEMA,
CASE WHEN DTYPE = 'F'
THEN (SELECT ROUTINENAME FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA = RPL.SCHEMA
AND SPECIFICNAME = RPL.NAME )
ELSE RPL.NAME
END AS NAME,
DTYPE, BTYPE, BSCHEMA,
CASE WHEN BTYPE = 'F'
THEN (SELECT ROUTINENAME FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA = BSCHEMA
AND SPECIFICNAME = BNAME )
ELSE BNAME
END AS BNAME
FROM RPL, OBJ
where ROOTSCHEMA = OBJ.SCHEMA
and ROOTNAME = OBJ.NAME
ORDER BY ROOTTYPE, ROOTSCHEMA, ROOTNAME, LEVEL
ROOTTYPE ROOTSCHEMA ROOTNAME LEVEL SCHEMA NAME DTYPE BTYPE BSCHEMA BNAME
-------- ---------- -------- ----- ------- -------- ----- ----- ------- --------
T TEST CUSTOMER 1 TEST F_CUST_1 F T TEST CUSTOMER
T TEST CUSTOMER 1 TEST V_CUST_1 V T TEST CUSTOMER
T TEST CUSTOMER 2 TEST P_CUST_1 P F TEST F_CUST_1
T TEST CUSTOMER 2 TEST P_CUST_2 P V TEST V_CUST_1
T TEST CUSTOMER 2 TEST V_CUST_2 V V TEST V_CUST_1
T TEST CUSTOMER 3 TEST F_CUST_2 F V TEST V_CUST_2
T TEST CUSTOMER 4 TEST P_CUST_3 P F TEST F_CUST_2
T TEST CUSTOMER 4 TEST P_CUST_4 P F TEST F_CUST_2
8 record(s) selected
T - Table -- 表
F - UDF -- 用戶自定義函數
V - VIEw -- 視圖
P - Stored Procedure -- 存儲過程
對於結果中所顯示的對象,如果他們本身沒有對應新功能的修改,視圖需要被重新部署,用戶自定義函數和存儲過程所對應的程序包(Package)需要被重新綁定(Rebind)本文不會去詳解這個遞歸 SQL 的邏輯,以及其中用到的各個目錄視圖的屬性。希望讀者先能體會到對目錄視圖的理解結合 SQL 的技巧可以完成復雜的工作,有時間再去仔細琢磨這段較為復雜的 SQL。
管理和支持的實踐案例
實踐四 : 查詢並修復錯誤的訪問權限。
這裡說的訪問權限是指數據庫帳戶在數據庫對象上的授權(Grant),錯誤的訪問權限使系統存在安全隱患。對於企業級的數據庫應用,訪問權限是審計(Audit) 審核的重要部分。清單 4.的場景描述了當前數據庫中的程序(Routines)只能由 PROGRAM 這個帳戶來調用,其他任何具有程序執行權限的帳戶都是不合法的。從例程中通過查詢目錄視圖(syscat.routines 和 syscat.routineauth)所返回的報告中,DEVELOPER 和 TESTER 擁有了對程序的執行權限,通過這個報告,可以取消(REVOKE)這些錯誤的權限。
清單 4. 查詢並修復錯誤的訪問權限
db2=>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'
ROUTINETYPE ROUTINESCHEMA ROUTINENAME GRANTEE GRANT_TIME EXECUTEAUTH
----------- ------------- ----------- --------- -------------------------- -----------
F TEST FUNCTION1 DEVELOPER 2009-10-05-16.52.05.233664 Y
P TEST PROCEDURE1 TESTER 2009-10-05-16.52.06.130705 Y
db2=> REVOKE EXECUTE ON FUNCTION TEST.FUNCTION1 FROM USER DEVELOPER RESTRICT
DB20000I The SQL command completed successfully.
db2=> REVOKE EXECUTE ON PROCEDURE TEST.PROCEDURE1 FROM USER TESTER RESTRICT
DB20000I The SQL command completed successfully.
除了對權限的監控外,現今數據庫管理員和支持人員會結合目錄視圖通過腳本或者程序創建計劃任務,實現對程序運行狀態和數據庫容量狀態的監控和管理。
基於查詢目錄視圖的數據庫對象定義抓取工具
熟悉 DB2 的讀者都知道 DB2 本身提供了圖形界面或命令行(db2look)的方式以獲取數據庫對象定義(Data Definition Language,DDL),或者也可以通過其他開源工具(如 Eclipse 插件 SQL Explorer 等),其實這些獲取數據庫對象定義的工具都是基於訪問 DB2 目錄視圖獲得的。對於有一定編程經驗的初學者來說,實現這種工具並不困難。
下面簡要介紹筆者在初學 DB2 時用 Java 語言編寫的一個數據庫對象定義抓取工具 DB2ExtractDDL,它可以用來抓取表、視圖、用戶自定義函數、存儲過程和觸發器的 DDL,包含了日常工作中所需要的功能,如表中列的詳細定義,索引,對象的權限等等。清單 5.中給出了用 DB2ExtractDDL 抓取表定義的例子。
清單 5. 用 DB2ExtractDDL 抓取表定義
D:\>Java DB2ExtractDDL -d sample
-u useranme -p passWord -o TEST.TEST_TABLE
Object: TEST.TEST_TABLE on SAMPLE
Begin at: Thu Jan 07 16:50:49 CST 2010
---------------------------------------------------------------
DROP TABLE TEST.TEST_TABLE
@
CREATE TABLE TEST.TEST_TABLE
(
PK_COL CHARACTER(10) NOT NULL,
COL1 CHARACTER(1) NOT NULL,
COL2 DATE ,
COL3 CHARACTER(35) DEFAULT NULL,
COL4 INTEGER
)
IN TEST_DATA_01
INDEX IN TEST_INDX_01
@
ALTER TABLE TEST.TEST_TABLE DATA CAPTURE CHANGES
@
ALTER TABLE TEST.TEST_TABLE NOT VOLATILE
@
ALTER TABLE TEST.TEST_TABLE APPEND OFF
@
ALTER TABLE TEST.TEST_TABLE LOCKSIZE ROW
@
ALTER TABLE TEST.TEST_TABLE
ADD CONSTRAINT TEST_PK
PRIMARY KEY (PK_COL)
@
CREATE INDEX TEST.INDEX_1
ON TEST.TEST_TABLE(COL1,COL2)
@
CREATE UNIQUE INDEX TEST.INDEX_2
ON TEST.TEST_TABLE(PK_COL,COL1)
@
GRANT SELECT ON TEST.TEST_TABLE TO GROUP PROGRAM
@
---------------------------------------------------------------
Complete!
End at: Thu Jan 07 16:50:53 CST 2010
這個工具還可以加以批量控制和集成,滿足實際項目中的需要。對於初學者來說可以迅速掌握幾十個目錄視圖的使用方法。本文的附件提供了這個工具的下載和使用說明。
結束語
本文結合實際的案例描述了在 DB2 數據庫應用的各個階段中了解和使用目錄視圖的重要性,目錄視圖是數據庫的基礎設施,也是數據庫知識中重要的一環。希望讀者通過本文的閱讀將目錄視圖作為數據庫學習和工作中的常用參考和工具。
本文示例源代碼或素材下載