DB2入門培訓(xùn)講義(共49張)(PPT 49頁)

1、DB2 基本知識介紹第1頁，共49頁。認(rèn)識 DB2認(rèn)識 DB2第2頁，共49頁。DB2的版本 DB2 UDB V7、V8、V9 PE (Personal Edition) EE (Enterprise Edition) EEE (Enterprise - Extended Edition )其他IBM 主機(jī)平臺如AS/400上的DB2與DB2 UDB有很大不同，老版本的主機(jī)DB2甚至不支持SQL。第3頁，共49頁。DB2在招行的應(yīng)用情況 AS/400 DB2 RS/6000 UDB V5 V7 V8 V9 數(shù)據(jù)倉庫 開發(fā)用：DB2 UDB PE V8 For Windows XP/2K第4頁，

2、共49頁。DB2 UDB vs SQL Server /Sybase SQL Server/Sybase是個黑盒子 DB2 是個相對透明的黑盒子 DB2 所支持的SQL語句更完整 DB2 對數(shù)據(jù)倉庫所做的擴(kuò)展支持非常好，如多分區(qū)，MDC技術(shù)等。 DB2 具有平臺優(yōu)勢和體系優(yōu)勢，對高端應(yīng)用有更好的支持。第5頁，共49頁。DB2 UDB的Create Table語句第6頁，共49頁。SQL Server 的Create TableCREATE TABLEdatabase_name.owner.| owner. table_name(| column_name AS computed_column_

3、expression| ,.n)ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT 第7頁，共49頁。 Windows 下的安裝、使用 僅安裝客戶端 DB2 Connect 安裝服務(wù)器端 DB2 PE Server + DB2 Connect RS/6000下的安裝、使用 如RS/6000上已安裝DB2，則只要在登錄用戶的profile中引用db2inst1的profile或手工添加相應(yīng)環(huán)境變量，即可使用DB2，前提是DBA已授權(quán)此用戶足夠權(quán)限。DB2安裝、使用第8頁，共49頁。 配置助手 控制中心 命令行工具 命令窗口、命令行處理器

4、 信息中心 開發(fā)中心 健康中心使用 DB2工具 - windows第9頁，共49頁。 實(shí)例 Instance 數(shù)據(jù)庫 Database 容器 Container 表空間 Tablespace 擴(kuò)展區(qū) Extend 表 Table 頁 PageDB2 UDB中一些基本概念第10頁，共49頁。 View SQL存儲過程 觸發(fā)器 事務(wù)處理 索引DB2 UDB中的標(biāo)準(zhǔn)SQL設(shè)施第11頁，共49頁。模式是一個標(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 的表。簡單的比喻：假如DB等同于公司，模式相當(dāng)一個部門名稱。DB2 數(shù)據(jù)庫中的模式名 - SCHEMA第12頁，共49頁。DB2 專用存儲器 CURRENT DAT

6、E USER CURRENT TIMESTAMP CURRENT TIME CURRENT TIMEZONE CURRENT SERVER 相當(dāng)于SQL Server中的全局變量第13頁，共49頁。 常用的 DB2 指令 CREATE / DROP LIST / DESCRIBE FORCE GET / UPDATE IMPORT / EXPORT / LOAD BIND / REBIND REORG / RUNSTATS 第14頁，共49頁。 常用的 DB2 系統(tǒng)指令 db2admin db2advi db2batch db2cc / db2cmd db2cfexp / db2cfimp d

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

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

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

10、imary Key Constraints Foreign Key Constraints 觸發(fā)器前觸發(fā)器 / 后觸發(fā)器 實(shí)例化查詢表（MQT）：REFRESH DEFERRED REFRESH IMMEDIATE三、數(shù)據(jù)庫中的對象第23頁，共49頁。數(shù)據(jù)的鎖四、數(shù)據(jù)的鎖第24頁，共49頁。LOCK TABLE 指令LOCK TABLE DB2.TABLENAME IN SHARE MODELOCK TABLE DB2.TABLENAME IN EXCLUSIVE MODE鎖定的對象數(shù)據(jù)庫 / 表空間 / 表格 / 記錄鎖定的模式（強(qiáng)度）寫鎖定，包括 X、W、NW、Z 等讀鎖定，包括 S、NS

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

12、計劃，如：dynexpln -d sample -s “sql指令”-o expln.outDynexpln -d sample -f sql文件 -o expln.out Visual Explain從控制中心或命令行中心執(zhí)行的explain sql 五、數(shù)據(jù)庫系統(tǒng)效能第28頁，共49頁。幾張關(guān)鍵的系統(tǒng)表 SYSCAT.TABLES SYSCAT.COLUMNS SYSCAT.COLDIST SYSCAT.INDEXES SYSCAT.TABLESPACESRUNSTATS 索引 建立索引的方法 where條件的索引的順序 索引的副作用五、數(shù)據(jù)庫系統(tǒng)效能第29頁，共49頁。五、數(shù)據(jù)庫系統(tǒng)效能

13、REORGMQT（物化視圖） DATA INITIALLY DEFERRED REFRESH DEFERRED REFRESH IMMEDIATE 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 ;

14、第30頁，共49頁。程序包與BIND嵌入式SQLCLIJDBCSQLJDB2客戶端程序開發(fā)第31頁，共49頁。實(shí)際應(yīng)用六、實(shí)際應(yīng)用第32頁，共49頁。SQL存儲過程CREATE PROCEDUREDROP PROCEDUREIN/OUT 參數(shù)調(diào)用 CALL錯誤陷阱循環(huán)語句COMMIT WORKBEGIN/END六、實(shí)際應(yīng)用第33頁，共49頁。SQL存儲過程C存儲過程/函數(shù)Java存儲過程其他：COBOL、OLE存儲過程 第34頁，共49頁。Store Procedure BuilderDW：通過一個C函數(shù)寫成的Function向調(diào)試文件寫日志存儲過程的調(diào)試方法 SQL存儲過程的調(diào)試 第35頁，

15、共49頁。SQLCODE，SQLSTATE為什么要有Condition Handling?固定寫法： Declare SQLSTATE CHAR(5) DEFAULT 00000; Declare SQLCODE INT DEFAULT 0; 必須要這么寫才能得到SQL Statement執(zhí)行情況，且只能寫在存儲過程的開頭(Outermost Scope) 為什么？ 主機(jī)風(fēng)格 Condition Handling Declare continue handler for sqlstate 42774 begin end SQL存儲過程的異常處理方法 Condition Handling第36頁

16、，共49頁。Create procdure xx.xx(in xx, out xx) specific specific-name Language SQL begin xxx end DB2允許定義同名但參數(shù)不同的存儲過程，因此在刪除存儲過程時使用specific-name刪除。 假如調(diào)用存儲過程的參數(shù)錯誤，db2只說找不到對應(yīng)的存儲過程，而不會說參數(shù)錯誤。 這一點(diǎn)類似面向?qū)ο蟮膮?shù)重載。 DB2 SQL存儲過程的specific specific-name第37頁，共49頁。靜態(tài)SQL靜態(tài)SQL (static SQL) 語句嵌入到應(yīng)用程序中，在應(yīng)用運(yùn)行之前，所有的嵌入靜態(tài)SQL語句必須經(jīng)

17、過預(yù)編譯并被綁定到程序包(package) 中。DB2編譯這些語句時，將基于編譯和綁定這些語句時的系統(tǒng)表中的統(tǒng)計信息和數(shù)據(jù)庫參數(shù)為每一個靜態(tài)SQL語句生成一個訪問計劃(access plan)。這些訪問計劃在運(yùn)行時永遠(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 O

18、R e.salary IS NULL GROUP BY d.deptname ;六、實(shí)際應(yīng)用第38頁，共49頁。動態(tài)SQL動態(tài)SQL (dynamic SQL) 語句在程序運(yùn)行時被prepare和執(zhí)行。DB2運(yùn)行動態(tài)SQL語句時，將基于當(dāng)前的系統(tǒng)表中的統(tǒng)計信息和數(shù)據(jù)庫參數(shù)為該語句生成一個訪問計劃(access plan)。該訪問計劃在運(yùn)行時可能會不斷變化。 stmt = “select empno, salary from employee where empno=?”; EXEC SQL PREPARE s1 FROM :stmt; EXEC SQL EXECUTE s1;六、實(shí)際應(yīng)用第39

19、頁，共49頁。六、實(shí)際應(yīng)用MERGE SQL UPSERT對目標(biāo)表進(jìn)行更新插入的過程，當(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);第40頁，共49頁。六、實(shí)際應(yīng)用權(quán)限設(shè)置過程/函數(shù)GRANT EXECUTE ON PROCEDURE/FUNCTIOIN PROCNAME/FUNCNAME TO USER/GROUP USERNAME/GROUPNAME ;表GRANT ALL/DELETE/UPDATE/SELECT/INSERT ON TABLE TABNAME TO USER/GROUP USERNAME/GROUPNAME ;