db2基礎(chǔ)培訓(xùn)(共48張)(PPT 48頁)

1、DB2基礎(chǔ)培訓(xùn)Asiainfo Technologies (China), Inc. 分析型CRM解決方案二部 沈世平第1頁，共48頁。Agenda基本概念及數(shù)據(jù)庫對象介紹分區(qū)數(shù)據(jù)庫介紹 常用命令介紹 db2使用注意事項(xiàng)第2頁，共48頁。數(shù)據(jù)庫對象數(shù)據(jù)庫1數(shù)據(jù)庫2系統(tǒng)容器2實(shí)例1實(shí)例2節(jié)點(diǎn)組1節(jié)點(diǎn)組2表空間1表空間2索引表大數(shù)據(jù)對象第3頁，共48頁。數(shù)據(jù)庫實(shí)例 (instance)第4頁，共48頁。表空間(tablespace)-存儲所有數(shù)據(jù)對象第5頁，共48頁。 容器: 物理存儲設(shè)備，有三種類型：目錄(SMS)文件(DMS)裸設(shè)備(DMS)容器(Containers)文件目錄裸設(shè)備 第6頁，

2、共48頁。表空間與容器關(guān)系表1表2表3數(shù)據(jù)庫表空間 1表空間2表空間3表空間4索引(Index)數(shù)據(jù)(Data)大對象Long Data第7頁，共48頁。表空間類型系統(tǒng)管理表空間 SMS (System Managed Space) 數(shù)據(jù)庫管理表空間- DMS (Database Managed Space)第8頁，共48頁。系統(tǒng)管理表空間SMS特性數(shù)據(jù)存儲在文件中數(shù)據(jù)和索引放在同一表空間文件動(dòng)態(tài)增長表空間大小的上限由下幾種因素決定:容器數(shù)文件系統(tǒng)的大小限制文件系統(tǒng)對單個(gè)文件大小的限制不能新增容器UNIX平臺上文件系統(tǒng)大小可以增加 管理容易 建議臨時(shí)表空間用SMS第9頁，共48頁。數(shù)據(jù)庫管理表

3、空間(DMS)特性在創(chuàng)建時(shí)分配空間容器可增加（數(shù)據(jù)分配自動(dòng)重新平衡)容器大小可修改 數(shù)據(jù)分配自動(dòng)重新平衡空間大小只限于物理存儲特性文件容器使用文件系統(tǒng)的IO裸設(shè)備使用直接操作硬件，不通過文件系統(tǒng)很高的性能 (特別對于 OLTP系統(tǒng)) 數(shù)據(jù)分配靈活第10頁，共48頁。SMS與DMS對比SMSDMS對象管理操作系統(tǒng)數(shù)據(jù)庫空間分配需要時(shí)動(dòng)態(tài)增長預(yù)分配管理方便程度很少需要人工管理容器大小隨文件系統(tǒng)增加需要人工指定一些參數(shù),例如定義容器大小等可通過增加容器建立大容量的表空間使用限制數(shù)據(jù)和索引必須在同一表空間容器不能增加數(shù)據(jù)和索引可根據(jù)需要靈活存放容器可根據(jù)需要增加或者修改容器類型只有一種容器：目錄兩種容

4、器：文件裸設(shè)備性能動(dòng)態(tài)擴(kuò)展，略差于優(yōu)于采用裸設(shè)備容器性能可提升5-10%第11頁，共48頁。創(chuàng)建表空間示例sms:CREATE TABLESPACE sms MANAGED BY SYSTEMUSING (/database/sms1,/database/sms2) EXTENTSIZE 4 PREFETCHSIZE 8dms:CREATE TABLESPACE dms MANAGED BY DATABASEUSING (FILE /database/dms/data.1 1000, DEVICE /dev/rlv1 3000) EXTENTSIZE 16 PREFETCHSIZE 32第12

5、頁，共48頁。表空間管理命令list tablespaces show detail顯示數(shù)據(jù)庫所有表空間狀況list tablespace containers for tablespace_id show detail顯示一個(gè)表空間的所有容器狀況alter tablespace 對 DMS 增加容器 增加DMS中已有容器大小修改表空間參數(shù)：prefetchsize為表空間分配 bufferpoolrename tablespacedrop tablespace所有與該表空間相關(guān)對象被刪除或標(biāo)志為不可用.第13頁，共48頁。緩沖池(Bufferpool)內(nèi)存緩沖區(qū)：減少物理磁盤IO 經(jīng)常要訪問

6、的數(shù)據(jù)或者索引保留在內(nèi)存里 可以保存大量的數(shù)據(jù)頁在內(nèi)存創(chuàng)建數(shù)據(jù)庫時(shí)會(huì)建默認(rèn)的緩沖池：IBMDEFAULTBP 可修改默認(rèn)IBMDEFAULTBP的大小第14頁，共48頁。緩沖池一覽默認(rèn)IBMDEFAULTBPMYBUFF1MYBUFF2MYBUFF3Table spacesBuffer poolsSYSCATSPACETEMPSPACE1USERSPACE1MYREGSPACEMYTEMPSPACEMYLONGSPACE第15頁，共48頁。如何分配Bufferpools大小單個(gè) bufferpool大多數(shù)情況下使用一個(gè)大的緩沖池較好需要建立多個(gè)bufferpool的原因同一數(shù)據(jù)庫內(nèi)有多個(gè)不同頁

7、大小的表空間OLTP系統(tǒng)中有些表訪問頻率很高經(jīng)常對一些大表進(jìn)行全表掃描bufferpool的大小設(shè)定可用內(nèi)存*75%(OLTP), 可用內(nèi)存*50%(數(shù)據(jù)倉庫)察看bufferpoolSelect * from syscat.bufferpools第16頁，共48頁。數(shù)據(jù)庫日志記錄所有數(shù)據(jù)庫對象及數(shù)據(jù)的修改所有改變的數(shù)據(jù)寫到磁盤之前都先寫到內(nèi)存中日志緩沖區(qū)(log buffer)里 日志可以寫在裸設(shè)備上第17頁，共48頁。兩種日志類型A循環(huán)日志（circle logging）B.歸檔日志 (Archival Logging)第18頁，共48頁。循環(huán)日志（circle logging）默認(rèn)的日志

8、方式主日志文件記錄所有修改；事務(wù)提交后重用輔日志文件在主日志文件不夠時(shí)創(chuàng)建支持意外恢復(fù)/版本恢復(fù)，不支持前滾恢復(fù)日志滿或者磁盤空間不足時(shí)整個(gè)事務(wù)回滾 PRIMARYSECONDARY1n231n第19頁，共48頁。歸檔日志(Archival Logging/Log Retain)LOGRETAIN參數(shù)設(shè)為ON日志文件不會(huì)刪除. 通過設(shè)USEREXIT參數(shù)將日志文件備份到二級磁帶等二級存儲上支持前滾恢復(fù)及在線備份ONLINE ARCHIVE -Contains informationfor committed andexternalized transactions.Stored in the

9、ACTIVElog subdirectory.OFFLINE ARCHIVE -Archive moved fromACTIVE log subdirectory.(May also be on other media)1213141516Manual orUserexitACTIVE - Containsinformation fornon-committed or non-externalizedtransactions第20頁，共48頁。數(shù)據(jù)遷移工具EXPORT-將表中數(shù)據(jù)導(dǎo)出成文件IMPORT-將文件中數(shù)據(jù)導(dǎo)入表LOAD-將文件中數(shù)據(jù)導(dǎo)入表DB2MOVE-數(shù)據(jù)庫遷移DB2LOOK-導(dǎo)出

10、數(shù)據(jù)庫對象 第21頁，共48頁。安全性控制DB2 用戶認(rèn)證依賴于操作系統(tǒng)訪問控制層次第22頁，共48頁。Agenda基本概念及數(shù)據(jù)庫對象介紹分區(qū)數(shù)據(jù)庫介紹常用命令介紹 db2使用注意事項(xiàng)第23頁，共48頁。分區(qū)數(shù)據(jù)庫第24頁，共48頁。數(shù)據(jù)庫分區(qū)概念每個(gè)分區(qū)有獨(dú)立的數(shù)據(jù)、配置文件、索引和事務(wù)日志。多分區(qū)數(shù)據(jù)庫（也稱分區(qū)數(shù)據(jù)庫），是具有兩個(gè)或多個(gè)分區(qū)的數(shù)據(jù)庫。單分區(qū)數(shù)據(jù)庫是一種只有一個(gè)分區(qū)的數(shù)據(jù)庫。分區(qū)這個(gè)概念只適用于數(shù)據(jù)庫這一級，而不適用于實(shí)例這一級。第25頁，共48頁。節(jié)點(diǎn)配置文件（db2nodes.cfg）db2nodes.cfg 文件包含關(guān)于數(shù)據(jù)庫分區(qū)和這些分區(qū)所在的服務(wù)器的信息。db2

11、nodes.cfg(經(jīng)營分析系統(tǒng)）:0 BI_DB1_BOOT 01 BI_DB1_BOOT 12 BI_DB1_BOOT 23 BI_DB1_BOOT 34 BI_DB2_BOOT 05 BI_DB2_BOOT 16 BI_DB2_BOOT 27 BI_DB2_BOOT 3第26頁，共48頁。分區(qū)數(shù)據(jù)庫home目錄一個(gè)實(shí)例映射到一個(gè)操作系統(tǒng)用戶，擁有自己的主目錄。每臺分區(qū)的機(jī)器上單獨(dú)創(chuàng)建的所有實(shí)例都必須使用相同的名稱 和口令所有實(shí)例的主目錄必須是相同的目錄，這個(gè)目錄必須在一個(gè)共享磁盤（NFS 共享）上創(chuàng)建DB2實(shí)例屬主機(jī)器第27頁，共48頁。數(shù)據(jù)庫分區(qū)在 /data 目錄中創(chuàng)建的一個(gè)分區(qū)的數(shù)

12、據(jù)庫 /data/NODE0000 節(jié)點(diǎn)/data/NODE0000/SQL0001 數(shù)據(jù)庫缺省為instance主目錄，nfs共享性能不佳第28頁，共48頁。db2參數(shù)全局級概要注冊表（global level profile registry）-分區(qū)不共享 存放在文件 default.env 中，該文件位于 /var 下的一個(gè)子目錄中。對于每臺機(jī)器，都存在該文件的一個(gè)不同的本地副本。/var/db2/v81/default.env db2set all : g實(shí)例級概要注冊表-分區(qū)共享 db2set all : I db2set lr db2set DB2COMM=tcpip數(shù)據(jù)庫管理器配

13、置（dbm cfg）-分區(qū)共享 db2 get dbm cfg db2 update dbm cfg using xxxxx xxxx數(shù)據(jù)庫配置文件（db cfg） - 分區(qū)不共享 db2 get db cfg for jxcrm db2 update db cfg for jxcrm using xxxxx xxxx 建議參數(shù)值在所有分區(qū)上都應(yīng)該是相同 ： db2_all第29頁，共48頁。編目分區(qū)SYSCATSPACE包含了 DB2 編目（Catalog）。SYSCATSPACE 不能分區(qū)，駐留在一個(gè)分區(qū)內(nèi)，這個(gè)分區(qū)就是編目分區(qū)執(zhí)行create database 命令時(shí)所在的那個(gè)分區(qū)lis

14、t db directory 來確定哪個(gè)分區(qū)是編目分區(qū)System Database Directory Number of entries in the directory = 4Database 1 entry: Database alias = JXCRM Database name = JXCRM Local database directory = /home/db2nfs Database release level = 9.00 Comment = Directory entry type = Indirect Catalog node number = 0編目分區(qū)會(huì)顯示 SYS

15、CATSPACE 表空間 db2 list tablespaces第30頁，共48頁。分區(qū)組將一個(gè)或多個(gè)分區(qū)組成一組三個(gè)默認(rèn)的分區(qū)組 IBMDEFAULTGROUP: 所有數(shù)據(jù)庫分區(qū)組成。USERSPACE1 IBMTEMPGROUP: 所有數(shù)據(jù)庫分區(qū)組成的。TEMPSPACE1 IBMCATGROUP: 一個(gè)分區(qū)。Syscatspace創(chuàng)建分區(qū)組create database partition group pg23 on dbpartitionnums (2,3) create database partition group pgrpall on all dbpartitionnums

16、第31頁，共48頁。分區(qū)環(huán)境下的表空間CREATE TABLESPACE tbs_agg IN NDGRP5 PAGESIZE 32K MANAGED BY database using ( DEVICE /dev/rlvbiagg_nd02_1 1000 ) ON DBPARTITIONNUM(2) using ( DEVICE /dev/rlvbiagg_nd03_1 1000 ) ON DBPARTITIONNUM(3) using ( DEVICE /dev/rlvbiagg_nd04_1 1000 ) ON DBPARTITIONNUM(4) using ( DEVICE /dev/

17、rlvbiagg_nd05_1 1000 ) ON DBPARTITIONNUM(5) using ( DEVICE /dev/rlvbiagg_nd06_1 1000 ) ON DBPARTITIONNUM(6)EXTENTSIZE 64 PREFETCHSIZE 64 BUFFERPOOL POOL_32K;Db2_all “db2 connect to crm;db2 list tablespaces show detail”DB2NODE=0 export DB2NODE db2 terminate 第32頁，共48頁。協(xié)調(diào)節(jié)點(diǎn)協(xié)調(diào)代理所在的那個(gè)分區(qū)默認(rèn)為當(dāng)前連接的分區(qū)可設(shè)置；SET

18、CLIENT CONNECT_NODE n查詢?yōu)槔?發(fā)出select -協(xié)調(diào)代理分發(fā)到各個(gè)節(jié)點(diǎn)的子代理進(jìn)程子代理返回各結(jié)果在協(xié)調(diào)節(jié)點(diǎn)上合并起來- 返回結(jié)果到應(yīng)用程序第33頁，共48頁。分區(qū)圖和分區(qū)鍵 分區(qū)圖（partitioning map）多分區(qū)數(shù)據(jù)庫分區(qū)組有4,096條目，單分區(qū)數(shù)據(jù)庫分區(qū)組一個(gè)條目。數(shù)據(jù)庫分區(qū)組里的分區(qū)號是以一種循環(huán)（round-robin）方式指定的。 分區(qū)鍵（partitioning key）是一個(gè)列（或者一組列），用于確定某一行特定數(shù)據(jù)所在的那個(gè)分區(qū)第34頁，共48頁。Agenda基本概念及數(shù)據(jù)庫對象介紹分區(qū)數(shù)據(jù)庫介紹常用命令介紹db2使用注意事項(xiàng)第35頁，共48

19、頁。db2環(huán)境變量及常用命令環(huán)境變量if -f /db2home/sqllib/db2profile ; then . /db2home/sqllib/db2profileFi命令執(zhí)行方式 1 db2 “command” 2 db2 ENTER 進(jìn)入db2命令交互模式,quit退出 db2=第36頁，共48頁。db2環(huán)境變量及常用命令列出所有instance名稱 db2ilist 列出數(shù)據(jù)庫目錄 list database directory 連接數(shù)據(jù)庫 connect to db_name user user_name using password查看表空間 list tablespaces

20、 show detail查看表空間容器 list tablespace containers for tbs_id列出指定模式下的所有表 list tables for schema xxx 第37頁，共48頁。db2環(huán)境變量及常用命令查看表結(jié)構(gòu) describe table tab_name 查看表的索引 describe indexes for table table_name中斷數(shù)據(jù)庫連接 terminate信息幫助 ？ xxxx 例:db2 ? SQL30081 第38頁，共48頁。db2環(huán)境變量及常用命令獲取及修改實(shí)例級別參數(shù) get dbm cfg show detail upda

21、te dbm cfg using para_name para_value獲取及修改數(shù)據(jù)庫級別參數(shù) get db cfg FOR database-alias show detail update db cfg for db_name using para_name para_value查看當(dāng)前的schema values current schema 設(shè)置當(dāng)前schemaset current schema xxx查看當(dāng)前的instance名get instance第39頁，共48頁。db2環(huán)境變量及常用命令編目遠(yuǎn)程節(jié)點(diǎn)Catalog tcpip node node_name remote

22、hostname server server name編目遠(yuǎn)程數(shù)據(jù)庫Catalog database database_name as alias on path |at node node name批量執(zhí)行sql語句 Db2 -tvf filename -t Set statement termination character -v Echo current command -f Read from input file 第40頁，共48頁。db2環(huán)境變量及常用命令列出所有db2命令選項(xiàng) db2 list command options結(jié)果輸出到文件 db2 “sql_statement”