程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> 在數據庫應用項目的生命周期中充分利用 DB2 目錄視圖的最佳實踐

在數據庫應用項目的生命周期中充分利用 DB2 目錄視圖的最佳實踐

編輯:DB2教程

簡介

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 數據庫應用的各個階段中了解和使用目錄視圖的重要性,目錄視圖是數據庫的基礎設施,也是數據庫知識中重要的一環。希望讀者通過本文的閱讀將目錄視圖作為數據庫學習和工作中的常用參考和工具。

本文示例源代碼或素材下載

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved