DB2入門培訓(xùn)講義

1、認(rèn)識認(rèn)識 DB2DB2認(rèn)識 DB2DB2的版本 DB2 UDBl V7、V8、V9l PE (Personal Edition)l EE (Enterprise Edition)l EEE (Enterprise - Extended Edition )其他IBM 主機平臺如AS/400上的DB2與DB2 UDB有很大不同，老版本的主機DB2甚至不支持SQL。DB2在招行的應(yīng)用情況l AS/400 DB2l RS/6000 UDB V5 V7 V8 V9l 數(shù)據(jù)倉庫l 開發(fā)用：DB2 UDB PE V8 For Windows XP/2KDB2 UDB vs SQL Server /Sybas

2、el SQL Server/Sybase是個黑盒子l DB2 是個相對透明的黑盒子l DB2 所支持的SQL語句更完整l DB2 對數(shù)據(jù)倉庫所做的擴展支持非常好，如多分區(qū)，MDC技術(shù)等。l DB2 具有平臺優(yōu)勢和體系優(yōu)勢，對高端應(yīng)用有更好的支持。DB2 UDB的Create Table語句SQL Server 的Create TableCREATE TABLE database_name. .owner. . | owner. . table_name( ( | column_name AS computed_column_expression | , ,.n)ON filegroup | D

3、EFAULT TEXTIMAGE_ON filegroup | DEFAULT l Windows 下的安裝、使用 僅安裝客戶端 DB2 Connect 安裝服務(wù)器端 DB2 PE Server + DB2 Connectl RS/6000下的安裝、使用 如RS/6000上已安裝DB2，則只要在登錄用戶的profile中引用db2inst1的profile或手工添加相應(yīng)環(huán)境變量，即可使用DB2，前提是DBA已授權(quán)此用戶足夠權(quán)限。DB2安裝、使用相關(guān)的下載及文檔URL:http:/ 配置助手 l 控制中心l 命令行工具 命令窗口命令窗口、命令行處理器l 信息中心l 開發(fā)中心l 健康中心使用 DB

4、2工具 - windowsn 實例實例 Instancen 數(shù)據(jù)庫數(shù)據(jù)庫 Databasen 容器容器 Containern 表空間表空間 Tablespacen 擴展區(qū)擴展區(qū) Extendn 表表 Tablen 頁頁 PageDB2 UDB中一些基本概念n Viewn SQL存儲過程存儲過程 n 觸發(fā)器觸發(fā)器n 事務(wù)處理事務(wù)處理n 索引索引DB2 UDB中的標(biāo)準(zhǔn)SQL設(shè)施n 模式是一個標(biāo)識符，如用戶 ID， 它幫助分組表和其他數(shù)據(jù)庫對象。模式可以歸個人擁有，擁有者可以控制對數(shù)據(jù)以及其中的對象的存取。 模式也可以是數(shù)據(jù)庫中的對象。它可以在創(chuàng)建模式中的第一個對象時 自動創(chuàng)建。這樣的對象可以是任何

5、可以由模式名限定的對象，如表、索引、視圖、程序包、單值類型、函數(shù)或觸發(fā)器。若要自動創(chuàng)建模式，則您必須 擁有 IMPLICIT_SCHEMA 權(quán)限，也可以隱式地創(chuàng)建模式。 模式名用作兩部分對象名的第一部分。創(chuàng)建一個對象時，可將其分配給 特定模式。若不指定模式，則它被分配給缺省模式，缺省模式通常是創(chuàng)建該對象的人員的用戶 ID。名稱的第二部分是對象名。例如，名為 Smith 的用戶可以 有一個名為 SMITH.PAYROLL 的表。n 簡單的比喻：假如DB等同于公司，模式相當(dāng)一個部門名稱。DB2 數(shù)據(jù)庫中的模式名 - SCHEMADB2 專用存儲器n CURRENT DATEn USERn CURR

6、ENT TIMESTAMPn CURRENT TIMEn CURRENT TIMEZONEn CURRENT SERVER 相當(dāng)于相當(dāng)于SQL Server中的全局變量中的全局變量n 常用的常用的 DB2 DB2 指令指令n CREATE / DROPn LIST / DESCRIBEn FORCEn GET / UPDATEn IMPORT / EXPORT / LOADn BIND / REBINDn REORG / RUNSTATSn n 常用的常用的 DB2 DB2 系統(tǒng)指令系統(tǒng)指令n db2adminn db2advin db2batchn db2cc / db2cmdn db2cf

7、exp / db2cfimpn db2start / db2stopn 數(shù)據(jù)庫分區(qū)與表空間數(shù)據(jù)庫分區(qū)與表空間數(shù)據(jù)庫分區(qū)與表空間l 數(shù)據(jù)庫分區(qū)l 一個數(shù)據(jù)庫可以建立一個或多個分區(qū)（即節(jié)點），數(shù)據(jù)可以根據(jù)表所定義的分區(qū)鍵通過HASH算法存放于各個分區(qū)中。l作為表格的分區(qū)鍵，建議使用高基數(shù)字段（擁有多個不同的值），這樣可以使數(shù)據(jù)被均勻的分布，可以利用節(jié)點組中所有的節(jié)點資源來執(zhí)行操作。也可以指定多個字段同時作為分區(qū)鍵來滿足要求。二、數(shù)據(jù)庫分區(qū)與表空間l 表空間的定義DB2是以表空間來定義數(shù)據(jù)庫中存放數(shù)據(jù)的實體磁盤空間。一個數(shù)據(jù)庫中至少需要具備3個表空間，分別是：lSYSCATSPACE： 存放數(shù)據(jù)庫中

8、的所有系統(tǒng)表lTEMPSPACE1：存放DB2在執(zhí)行SQL指令時所產(chǎn)生的臨時數(shù)據(jù)表lUSERSPACE1：存放用戶所建立的表二、數(shù)據(jù)庫分區(qū)與表空間l 表空間的分類根據(jù)存儲的數(shù)據(jù)種類區(qū)分l 規(guī)則表空間：存放系統(tǒng)表格過用戶建立的表格的數(shù)據(jù)與索引。l 臨時表空間：存放在執(zhí)行SQL指令時產(chǎn)生的臨時數(shù)據(jù)。l 大型表空間：存放LONG VARCHAR與LOB類型的字段數(shù)據(jù)。根據(jù)管理方式區(qū)分l 數(shù)據(jù)庫管理空間：DMS，是由DB2直接格式化與管理的空間l 系統(tǒng)管理空間：SMS，是由DB2通過操作系統(tǒng)所管理的空間二、數(shù)據(jù)庫分區(qū)與表空間數(shù)據(jù)庫中的對象數(shù)據(jù)庫中的對象三、數(shù)據(jù)庫中的對象l 對象名稱：DB2 的所有對象

9、，都有其對象名稱，DB2 會將他全部轉(zhuǎn)成大寫，但如果把對象名稱用雙引號括起來，則就區(qū)分大小寫。帶引號的對象名稱也可以包含SQL的保留字，但并不提倡。三、數(shù)據(jù)庫中的對象l 表格 注意指定表空間和分區(qū)鍵 可增加字段，新增或刪除限制 可更改VARCHAR類型的長度 可用LIKE定義同樣的一張表，但除了字段類型和默認(rèn)值外其他屬性不繼承l(wèi) 索引l 視圖 只讀視圖/可更新視圖l 別名數(shù)據(jù)庫中的對象l 限制 NOT NULL Constraints Column Defaults Unique Constraints Check Constraints Primary Key Constraints For

10、eign Key Constraintsl 觸發(fā)器 前觸發(fā)器 / 后觸發(fā)器l 實例化查詢表（MQT）： REFRESH DEFERRED REFRESH IMMEDIATE三、數(shù)據(jù)庫中的對象數(shù)據(jù)的鎖數(shù)據(jù)的鎖四、數(shù)據(jù)的鎖l LOCK TABLE 指令l LOCK TABLE DB2.TABLENAME IN SHARE MODEl LOCK TABLE DB2.TABLENAME IN EXCLUSIVE MODEl 鎖定的對象l 數(shù)據(jù)庫 / 表空間 / 表格 / 記錄l 鎖定的模式（強度）l 寫鎖定，包括 X、W、NW、Z 等l 讀鎖定，包括 S、NS、U 等四、數(shù)據(jù)的鎖l 鎖定等待l LOC

11、K WAITl 鎖定超時l LOCKTIMEOUTl 鎖定升級l LOCKLIST：定義數(shù)據(jù)庫中預(yù)留多少空間存放鎖定信息l MAXLOCKS：定義每個事務(wù)可以使用多少LOCKLIST內(nèi)存區(qū)四、數(shù)據(jù)的鎖數(shù)據(jù)庫系統(tǒng)效能數(shù)據(jù)庫系統(tǒng)效能五、數(shù)據(jù)庫系統(tǒng)效能l DB2 Explain 工具l db2expln執(zhí)行該語句可以查看Package中的SQL指令執(zhí)行計劃。這是從unix或是doc窗口執(zhí)行的指令，如下：Db2expln -d dbname -c vicky -p cursor -o expln.outl dynexpln顯示SQL指令的執(zhí)行計劃，如：dynexpln -d sample -s “sq

12、l指令”-o expln.outDynexpln -d sample -f sql文件 -o expln.outl Visual Explain從控制中心或命令行中心執(zhí)行的explain sql 五、數(shù)據(jù)庫系統(tǒng)效能l 幾張關(guān)鍵的系統(tǒng)表l SYSCAT.TABLESl SYSCAT.COLUMNSl SYSCAT.COLDISTl SYSCAT.INDEXESl SYSCAT.TABLESPACESl RUNSTATS l 索引l 建立索引的方法l where條件的索引的順序l 索引的副作用五、數(shù)據(jù)庫系統(tǒng)效能五、數(shù)據(jù)庫系統(tǒng)效能l REORGl MQT（物化視圖）l DATA INITIALLY

13、DEFERREDl REFRESH DEFERREDl REFRESH IMMEDIATEl ENABLE/DISABLE QUERY OPTIMIZATIONCREATE TABLE sch.mqt1 AS( select name, location, salary from sch.tab1 a, sch.tab2where a.dept = b.deptnumb and a.salary 20000) DATA INITIALLY DEFERRED REFRESH DEFERRED ;REFRESH TABLE sch.mqt1 ;l程序包與BINDl嵌入式SQLlCLIlJDBClS

14、QLJDB2客戶端程序開發(fā)實際應(yīng)用實際應(yīng)用六、實際應(yīng)用l SQL存儲過程l CREATE PROCEDUREl DROP PROCEDUREl IN/OUT 參數(shù)l 調(diào)用 CALLl 錯誤陷阱l 循環(huán)語句l COMMIT WORKl BEGIN/END六、實際應(yīng)用lSQL存儲過程lC存儲過程/函數(shù)lJava存儲過程l其他：COBOL、OLE存儲過程 lStore Procedure BuilderlDW：通過一個C函數(shù)寫成的Function向調(diào)試文件寫日志存儲過程的調(diào)試方法 SQL存儲過程的調(diào)試 l SQLCODE，SQLSTATEl 為什么要有Condition Handling?l 固定寫

15、法： Declare SQLSTATE CHAR(5) DEFAULT 00000; Declare SQLCODE INT DEFAULT 0; 必須要這么寫才能得到SQL Statement執(zhí)行情況，且只能寫在存儲過程的開頭(Outermost Scope) 為什么？為什么？ 主機風(fēng)格主機風(fēng)格l Condition Handling Declare continue handler for sqlstate 42774 begin endl SQL存儲過程的異常處理方法 Condition Handlingl Create procdure xx.xx(in xx, out xx) spe

16、cific specific-name Language SQL begin xxx end DB2允許定義同名但參數(shù)不同的存儲過程，因此在刪除存儲過程時使用specific-name刪除。 假如調(diào)用存儲過程的參數(shù)錯誤，db2只說找不到對應(yīng)的存儲過程，而不會說參數(shù)錯誤。 這一點類似面向?qū)ο蟮膮?shù)重載。 DB2 SQL存儲過程的specific specific-namel 靜態(tài)SQLl 靜態(tài)SQL (static SQL) 語句嵌入到應(yīng)用程序中，在應(yīng)用運行之前，所有的嵌入靜態(tài)SQL語句必須經(jīng)過預(yù)編譯并被綁定到程序包(package) 中。l DB2編譯這些語句時，將基于編譯和綁定這些語句時的系

17、統(tǒng)表中的統(tǒng)計信息和數(shù)據(jù)庫參數(shù)為每一個靜態(tài)SQL語句生成一個訪問計劃(access plan)。l 這些訪問計劃在運行時永遠(yuǎn)被使用；這些訪問計劃不會改變，除非程序包被重新綁定。EXEC SQL SELECT d.deptname, count(e.empno), avg(e.salary) FROM department d left outer join employee e on (e.workdept = d.deptno) WHERE e.salary = :salary_baseline:salary_baseline OR e.salary IS NULL GROUP BY d.de

18、ptname ;六、實際應(yīng)用l 動態(tài)SQLl 動態(tài)SQL (dynamic SQL) 語句在程序運行時被prepare和執(zhí)行。l DB2運行動態(tài)SQL語句時，將基于當(dāng)前的系統(tǒng)表中的統(tǒng)計信息和數(shù)據(jù)庫參數(shù)為該語句生成一個訪問計劃(access plan)。l 該訪問計劃在運行時可能會不斷變化。 stmt = “select empno, salary from employee where empno=?”; EXEC SQL PREPARE s1 FROM :stmt; EXEC SQL EXECUTE s1;六、實際應(yīng)用六、實際應(yīng)用l MERGE SQL UPSERTl 對目標(biāo)表進(jìn)行更新插入的

19、過程，當(dāng)存在滿足條件的記錄時就更新目標(biāo)表，當(dāng)不存在滿足條件的記錄時就向目標(biāo)表插入新記錄。MERGE INTO account AS a USING (SELECT id, sum(balance) sum_balance from transaction GROUP BY id) AS t ON a.id = t.idWHEN MATCHED THEN UPDATE SET balance = a.balance + t.sum_balanceWHEN NOT MATCHED THEN INSERT (id, balance) = (t.id, t.sum_balance);六、實際應(yīng)用l 權(quán)限設(shè)置l 過程/函數(shù)GRANT EXECUTE ON PROCEDURE/FUNCTIOIN PROCNAME/FUNCNAME TO USER/GROUP USERNAME/GROUPNAME ;l 表GRANT ALL/DELETE/UPDATE/SE