DB数据库管理培训教程

DB数据库管理培训教程第1页/共76页1232基本概念监控与维护备份与恢复目录4性能优化5常见问题处理第2页/共76页1基本概念1.1DB2数据库对象1.2对象层次关系第3页/共76页DB2数据库体系系统实例数据库数据库对象表、视图、索引、模式、存储过程、程序包等缓冲池、日志文件、表空间物理存储、表空间容器、目录、文件系统或裸设备1.1DB2数据库对象第4页/共76页基本概念DB2体系结构中的最高一层是系统，一个系统表示DB2的一个安装。在由很多机器组成的网络环境中，我们有时也称系统为数据库分区。一个系统可以包含多个DB2实例，每个实例能够管理一个或多个数据库。从操作系统角度看，DB2的实例是一组进程和一组共享内存。实例为一个数据库的集合，共同运行在一个逻辑服务单元中（同一个端口）。在一台机器上可以有多个相互独立的实例，实例之间彼此独立，同时运行，不会相互影响。每个实例可以管理若干个数据库，一个数据库只属于一个实例。表空间是介于逻辑表和物理容器之间的逻辑对象,所有的表、索引和其他数据都存储在表空间中，表空间总是关联到相同页大小的缓冲池视图是虚拟的表，不需要物理存储，不包含真实的用户物理数据第5页/共76页系统、实例、数据库第6页/共76页1.2对象层次关系 DB2中的数据库对象主要分为数据库、表空间、表、索引、视图、容器（存储组）。右图全局概要描述了各个对象在DB2中的关系。第7页/共76页2监控与维护2.1监控工具2.2监控要点2.3第三方监控工具第8页/共76页即时监控工具--snapshot--db2pd--db2top事件监控工具–Eventmonitor–新事件监控工具(9.7)2.1监控工具第9页/共76页snapshot需要打开实例级监控器开关，有一定性能损耗--snapshot命令行监控--snapshot管理视图（DB2V9后）管理视图较容易分析，通常用于查找存在性能问题的动态sqldb2pd不需要建立与数据库的连接，基本不会对系统造成影响常用参数，以下参数常组合使用，导出文件进行分析-appl应用程序-bufferpool缓冲池信息-logs日志信息-tablespaces表空间信息-locks锁信息-agents代理信息-dynamic动态sql信息-tcbstats表状态信息第10页/共76页db2pd常用方法用途一

抓取锁信息，进行分析db2pd–ddbname–app–tran–agent–dyn–lockswait>db2pd.out分析db2pd.out第一步--sts列W表示等待G代表被授权获得锁

第11页/共76页第二步由第一步输出中的TranHdl对应到应用的AppHandl。得到TranHdl2和6分别对应到应用的AppHandl30和34

第三步获得应用程序的更多信息 C-AnchID/C-StmtUID

和L-AnchID/L-StmtUID

上。“C”代表当前（current），“L”代表最近（last）的语句UID第12页/共76页第四步检查动态语句缓存的内容经分析得出锁等待的语句为UPDATEEMPLOYEESETSALARY=SALARY*0.02拥有锁的APP的语句为UPDATEEMPLOYEESETBONUS=SALARY*0.1WHEREJOB='MANAGER'第13页/共76页用途二

查看表重组进度db2pd–ddbname–reorg

用途四

查看HADR同步状态第14页/共76页db2topdb2top

监视实用程序快速高效地监视复杂的DB2环境。它结合来自所有数据库分区的DB2快照信息，使用基于文本的用户界面提供正在运行的DB2系统的动态实时视图。db2top不支持上下滚屏，一般用于了解表空间状态，缓冲池命中率，app执行sql，了解处于锁等待状态的应用。进而用db2pd等工具分析示例：db2top–ddbname

第15页/共76页9.7新的锁事件监控Locktimeoutevents(MON_LOCKTIMEOUT)=hist_and_valuesDeadlockevents(MON_DEADLOCK)=hist_and_valuesLockwaitevents(MON_LOCKWAIT)=hist_and_valuesLockwaiteventthreshold(MON_LW_THRESH)=10000createeventmonitorlockevmonforlockingwritetounformattedeventtable(tablelocks)Callsysproc.EVMON_FORMAT_UE_TO_TABLES('LOCKING',NULL,NULL,NULL,NULL,NULL,'RECREATE_FORCE',-1,'SELECT*FROMlocksORDERBYevent_timestamp')只需用一个事件监控器就可捕获死锁、锁超时、锁等的语句

第16页/共76页日常监控数据库状态表空间状态、使用率applications状态锁情况性能指标常用性能指标读有效性rows_read/rows_selected寻找性能较差的动态SQLBuffpool命中率2.2监控要点第17页/共76页数据库状态db2getsnapshotfordbondbname表空间状态及占用率db2getsnapshotfortablespacesondbnamedb2pd–ddbname–tabdb2top–ddbname出现界面后按t缓冲池命中率db2pd–ddbname–bufdb2top–ddbname出现界面后按b是否有锁等待db2pd–ddbname–lockswaitdb2top–ddbname出现界面后按U日常监控第18页/共76页读有效性rows_read/rows_selecteddb2getsnapshotfordbondbname:Rows

read/Rowsselected管理视图查询:selectrows_read/rows_selectedfromsysibmadm.snapdb;对于OLTP，最好小于20对于OLAP，最好小于100当读有效性值较高时，表示可能在发生表扫描，可通过索引等手段进行优化找到性能较差的dynamicSQL语句

通过Sysibmadm.snapdyn_sql视图，根据不同字段进行排序，找到性能较差的SQL语句排序较多的sqlselectnum_executions,sort_overflows,total_sort_time,stmt_textfromsysibmadm.snapdyn_sqlorderbysort_overflowsdescfetchfirst10rowsonly;执行耗时最长的sql·selectnum_executions,total_exec_time,stmt_textfromsysibmadm.snapdyn_sqlorderbytotal_exec_timedescfetchfirst10rowsonly;常用性能指标第19页/共76页 db2advis建议索引工具

采用db2advis建立合适的索引，使得其能够走索引查询，由于索引的范围和排序，可以直接跳过许其他行，定位到符合我们需要的行。

前提条件

创建分析表db2–tvf&installpath/misc/EXPLAIN.DDL

做数据库升级或数据迁移后，若版本与原有版本不一致，应先drop掉分析表，创建新的分析表

常见用法如下:

创建db2advis.in文件，保存要分析的sql db2advis–ddbname–idb2advis.in

可参考建立索引第20页/共76页常用第三方监控工具QuestCentral.exe2.3第三方监控工具第21页/共76页常用第三方监控工具QuestCentral.exe第22页/共76页APP状态：UOWEXECUTING:正在执行Waitforuow:等待执行Waitforlock：锁等待2.执行成本：

该sql执行成本，数值越大，成本越高，执行越慢。如有多条成本较高的相同SQL，执行较慢，可考虑优化该SQL。3.挂起锁的数量

该数值表示挂起锁的数量，可观察哪些app挂起锁较多。4.该APP对应的SQL

直观显示该APP对应的SQL，方便抓取运行缓慢，锁等待等状态的sql第23页/共76页WAITFORLOCK状态APP锁信息监控第24页/共76页当前持有锁：

显示当前持有锁的AGENTID，锁名称及级别，对应锁的表等信息当前APP锁等待的APP

显示当前APP正在哪个APP执行，信息包括被等待的APP的所名称及级别，持有锁的AGENTID，根据AGENTID可以得到被等待锁的APP所对应的SQL，考虑进行SQL优化或表优化等相关操作

以上功能为较为常用及实用的监控功能，该工具在管理及维护方便也较为方便安全。第25页/共76页3备份与恢复3.1目的3.2基本概念3.3备份恢复3.4适用场景第26页/共76页数据库备份和恢复的目的

从故障中恢复数据库系统，有效保护数据。

故障场景系统崩溃(掉电、硬件故障、OS失败、数据库实例失败等)事务失败(语句失败、进程失败等)人为错误(误操作等)存储媒介故障（存储器物理损坏等）灾难(自然灾害等)3.1目的第27页/共76页事务(事务=工作单元)

事务是组合在一起的一个或多个SQL操作序列，也被称为一个工作单元，用来保证数据的完整性和一致性。

特性如下:A原子性–操作序列不可分割，或全部成功或全部失败。C一致性–事务执行前后数据库都处于一致性状态。I隔离性–并发事务相互隔离，互不影响。D持久性–事务被成功提交后，对数据库的更新被持久化，不会丢失。3.2基本概念第28页/共76页崩溃恢复从数据库日志控制文件中获取不一致起点信息，并根据日志内容对所有在不一致起点后成功提交事务进行再应用，而对其他未完成的事务进行回滚，从而把数据库带回可用、一致状态。1.自动-数据库参数AUTORESTART=ON(缺省)2.手动-数据库参数AUTORESTART=OFF。使用db2restartdatabase第29页/共76页版本恢复版本恢复=完全恢复，恢复数据库所有数据和结构。循环日志模式下：备份和恢复都必须离线进行。执行DB2命令(backup/database)来进行全备份和版本恢复。归档日志模式下：备份可以在线，而恢复必须离线进行。在执行DB2命令(restore)进行恢复的时候，需指定withoutrollingforward选项来清除数据库的ROLLFORWARDPENDING状态。第30页/共76页前滚恢复前滚恢复=时间点恢复，恢复数据到一个指定的时间点。归档日志模式下执行，有数据库完整备份,必须保留备份后到特定时间点之间所有的日志文件。数据库前滚恢复必须离线进行。在执行不带withoutrollingforward选项的DB2命令restore后，执行前滚恢复命令rollforward将使数据库恢复至备份后的某一特定时间点或者日志末尾。第31页/共76页部分恢复部分恢复，恢复数据库一部分数据和结构，工作原理同前滚恢复，区别是备份和恢复的对象是表空间。主要特点相对数据库备份，表空间备份和恢复在时间上占有优势，并且方法灵活。系统编目表空间的备份和恢复必须离线进行,并且必须前滚至日志的末尾。而其他的表空间都可在线进行，并且可以前滚至某个特定时间点。通过设定注册表变量DB2_COLLECT_TS_REC_INFO=ON，前滚命令将跳过不需要的日志来缩短恢复时间。即使对象表空间不同，备份和恢复也不能同时进行。执行方式利用DB2命令backup对所有关联表空间进行同时备份。恢复时,利用DB2命令restore对所有关联表空间进行同时恢复，恢复完成时，表空间处于前滚暂挂状态(ROLLFORWARDPENDING)。利用DB2命令rollforward对表空间进行前滚恢复。第32页/共76页离线全备

停止应用程序

断开所有数据库连接

执行备份命令

检查备份文件可用性

3.3备份恢复完全备份第33页/共76页在线全备

前提：设置LOGARCHMETH1参数，更改参数后，必须需要完全离线全备份数据库

执行备份命令

检查备份文件可用性

完全备份第34页/共76页离线全备恢复

完全备份恢复步骤第35页/共76页在线全备恢复

完全备份恢复步骤第36页/共76页增量备份第37页/共76页增量备份-修改参数第38页/共76页增量备份-离线增量备份第39页/共76页离线增量备份恢复步骤第40页/共76页增量备份-在线增量备份第41页/共76页在线增量备份恢复第42页/共76页差量备份-离线差量备份恢复方式同离线增量备份恢复第43页/共76页差量备份-在线差量备份恢复方式同在线增量备份恢复第44页/共76页监控

可以使用db2listutilitiesshowdetail命令监控执行状况。具体监控内容如下:1.backup–显示需要处理的字节数的初始评估，随着备份进度更新已处理字节数。显示的字节数不对应备份镜像大小，也不能作为镜像大小的评估依据。2.restore&recovery-初始字节数显示为UNKNOWN，实际读取的字节数被更新，对于自动增量恢复，分阶段跟踪进度。每个阶段都提供增量链中的恢复镜像，初始状态只显示一个阶段。随着镜像的恢复，阶段数量和恢复的字节被更新。3.crashrecovery&rollforward–有FORWARD和BACKWARD两个阶段。在FORWARD阶段，读取日志记录并应用于数据库。而在BACKWARD阶段，回滚未成功提交的事务。这两个阶段都显示已处理的字节数。第45页/共76页检查1.db2ckbkp–用来检查备份镜像的完整性及是否可以用于恢复。也可以用于显示存储在备份文件头部的元数据。2.db2ckrst–检查增量备份镜像的恢复顺序和时间戳，并获取恢复操作命令的序列。第46页/共76页适用情况：数据库容器变更V9.1前：1.Restore语句此语句用来标示一个重定向的恢复操作命令开始，它在普通恢复的命令上加了redirect参数。RESTOREDATABASESAMPLEFROM'/db2_backup/db2inst1/sample'TAKENAT20101023180128INTOSAMPLEREDIRECT;2.setcontainers语句当目标数据库所的物理存储设备与原来的数据库不一样时，就需要下面的命令来指定新的物理容器。SETTABLESPACECONTAINERSFOR0USING(PATH'/db2inst1/SAMPLE');SETTABLESPACECONTAINERSFOR1USING(PATH'/db2inst1/temp');SETTABLESPACECONTAINERSFOR2USING(DEVICE'/dev/rsample_1G'131072)……

3.restorecontinue语句：此语句代表重定向恢复语句序列完成，系统开始恢复数据库。RESTOREDATABASESAMPLECONTINUE;

重定向恢复第47页/共76页V9.1后：使用RESTRORE...REDIRECTGENERATESCRIPT<SCRIPTNAME>产生重定向恢复数据库的脚本db2restoredbSAMPLEFROM‘/db2_backup/db2inst1/sample’takenat20101023180128

REDIRECTGENERATESCRIPTsample.db2修改生成的脚本,常见修改内容：数据库名数据库目录容器信息执行修改后的脚本：db2–tvfsample.db2第48页/共76页注意事项如果数据库有自动存储表空间，则需要在自动生成的脚本内将ON/DBPATH部分去掉注释并设置，否则会将自动存储表空间容器路径创建到原备份库的路径。

因异常情况，导致setcontainer报SQL0299N

Containerisalreadyassignedtothetablespace

解决办法：

执行db2untag-f/dev/lvname操作，去掉容器上表空间标识第49页/共76页适用情况利用表空间备份重建数据库还原部分表空间，重建数据库

db2restoredbtestrebuildwithtablespace(SYSCATSPACE,USERSPACE1,USERSPACE2)takenat20120516135136

注意事项：必须包含SYSCATSPACE表空间使用一组表空间镜像重建数据库，需要最早及最晚镜像时间点范围内归档日志，保证数据库一致性数据库重建第50页/共76页场景一生产库由于误操作误删数据，需要恢复误删数据如生产库为较不重要业务系统,且数据库较小，可采用离线全备分或在线全备份进行全库恢复，使用归档日志进行前滚恢复到误删数据前时间点。如生产库为核心业务系统，且数据库较大。则需要使用数据库备份异机进行恢复，若异机存储空间足够，可考虑全库恢复；若异机存储空间不足，考虑使用数据库重建技术，仅恢复丢失数据表所在表空间。3.4适用场景第51页/共76页场景二将当前数据库，在同一台机器复制一份新的同样的数据库由于同一台机器，两个数据库不能有相同容器，否则会造成容器冲突，恢复失败。因此需要使用重定向恢复，修改容器名或容器目录。场景三数据库所在存储损坏如数据库所在存储损坏，可紧急使用数据库备份进行全库恢复。有全库数据库备份:使用全库数据库备份，在备用机建立相同容器，可直接完全恢复。若备用机无法建立相同容器，则需要使用重定向恢复修改容器仅有表空间备份：若仅有部分表空间备份，则仅能使用数据库重建，利用表空间备份重建数据库。第52页/共76页参考命令上述备份恢复方式均可组合使用，下列参考db2“restoredbxsmdbrebuildwithtablespace(syscatspace,ec_trade)takenat20120726170416intoxsmtestlogtarget/home/db2inst2redirectgeneratescriptrebuild.db2“该命令为数据库重建，重定向恢复，在线备份恢复组合使用的示例。以上备份恢复方式不支持跨平台数据迁移，跨平台数据迁移需要db2look+db2move配合使用第53页/共76页4性能优化4.1存储原理4.2规划设计4.3建议参数4.4表优化第54页/共76页存储RAID级别常用RAID级别：RAID5，RAID10RAID5：最少需要3块盘，(n-1)/n的总磁盘容量(n为磁盘数)，读性能较好，由于奇偶校验，随机写及连续写性能一般，磁盘空间利用率较高。RAID10：最少需要4块盘，更好的读写性能，更好的可靠性，最多可允许N/2块磁盘故障，(N为磁盘总数)，磁盘空间利用率较低，只能使用一半容量。4.1存储原理第55页/共76页相关概念条带条带（strip）是把连续的数据分割成相同大小的数据块，把每段数据分别写入到阵列中的不同磁盘上的方法。简单的说，条带是一种将多个磁盘驱动器合并为一个卷的方法第56页/共76页

当使用LVM或者硬件条带化时，决定条带化效果的因素是条带深度(stripedepth)和条带宽度(stripewidth)条带深度：这个参数指的是写在每块磁盘上的条带数据块的大小。条带宽度：是指同时可以并发读或写的条带数量。这个数量等于RAID中的物理硬盘数量。第57页/共76页

基于RAID组划分，RAID组内划分出的逻辑盘，操作系统里实际认到的物理盘。LUN的IO会经由条带，写入到RAID组各物理磁盘LUNDB2写容器的过程

表空间是由很多容器组成的，我们每次写容器的时候，写的单位为一个扩展数据块extent，EXTENTSIZE指定数据库可以写到一个容器中的PAGESIZE页面数量，达到这个数量之后将跳到下一个容器。第58页/共76页4.2规划设计存储层：

根据系统业务性能需求、成本估算、存储设备配置合理的RAID级别。如存储磁盘个数较多，建议划分多RAID组，不同RAID组间可IO分离。

根据业务需求规划数据库表空间，根据不同表空间，对应不同RAID级别及底层存储。

根据业务类型，设置合适的条带大小。OLTP类型，可设置较大的条带深度，提高并发，一次物理I/O可以同时响应多个并发的逻辑I/O；OLAP类型，可设置较小的条带深度，使一个逻辑I/O分布到多个磁盘上，从而实现I/O的负载均衡。

第59页/共76页数据库层：

EXTENTSIZE

大小选择：

对于OLAP数据库和大部分对表的访问包括许多查询或处理大量数据的事务(仅限于查询)的表，或者增长速度很快的表，那么从表中预取数据可以显著改善性能，应使用较大的extent。反之，对于较小的频繁更改、频繁随机读取的小的配置表，建议使用较小的extent。

PREFETCHSIZE大小选择 DB2V9后，如果把PREFETCHSIZE设置为AUTOMATIC，添加容器后，数据库会自动调整prefetchsize的大小，所以强烈建议把PREFETCHSIZE设置为AUTOMATIC或–1。第60页/共76页优化RAID设备上表空间性能

在一组RAID设备上创建表空间时，应该把表空间容器创建在多个RAIDGROUP上。根据业务类型不同设置不同表空间，对应不同表空间参数为表空间选择适当的扩展数据块(extent)大小。文件系统型容器，关闭FILESYSTEMCACHING，避免文件系统缓冲与数据库缓冲池多层缓冲，降低性能。第61页/共76页LOCKLISK,MAXLOCKS,LOCKTIMEOUTdb2getsnapshotfordbonhljyctdNumberofThresholdViolations=0Locksheldcurrently=118Lockwaits=2034Timedatabasewaitedonlocks(ms)=3608810Locklistmemoryinuse(Bytes)=1572736Deadlocksdetected=0Lockescalations=0Exclusivelockescalations=0Agentscurrentlywaitingonlocks=0LockTimeouts=0Numberofindoubttransactions=0 Locklistmemoryinuse(Bytes)超过locklist大小50%，可增加locklist数量。

如Lockescalations或Exclusivelockescalations>0,应增加locklist或maxlocks。

生产OLTP，locktimeout可设置10S，OLAP可设置60S，不建议设置默认值-1，会一直锁等待。4.3建议参数第62页/共76页MAXAPPS若应用程序返回以下错误，表明连接到该数据库的应用程序数达到最大值SQL1040N

Themaximumnumberofapplicationsisalreadyconnectedtothe

database可考虑增加maxapps设置NUM_IOCLEANERS，CHNGPGS_THRESH NUM_IOCLEANERS异步页面清除器用于将更改后的页面从缓冲池写到磁盘。如果没有配置页清除程序，则应用程序可能会遇到严重的性能问题。一般禁止设置为0。 CHNGPGS_THRESH缓冲池内被更改页面所占百分比，达到后将启动页面清除器，OLTP可设置成20-40，更新活动庞大，可设置较低值。NUM_IOSERVERS该参数值就是预取器的数量。一般该值等于数据库数据所在的磁盘数目。MAXFILOP服务器打开文件的最大数目db2getsnapshotfordbontestdb|grep-i‘close若Database

filesclosed=0不为0，需增大该值。第63页/共76页LOGBUFSZ日志缓冲区，指定将日志记录写到磁盘前的缓冲区数量getsnapshotfordatabaseon|grep–i“Logpages”Logpagesread=0Logpageswritten=12644理想状态，Logpagesread为0，如果该值比较大，考虑增加LOGBUFSZ值UTIL_HEAP_SZ指定BACKUP,RESTORE和LOAD应用程序同时使用的最大内存数，可提高速度。SORTHEAP、SHEAPTHRES_SHR和SHEAPTHRES

在DB2v9.1中，sheapthres_shr参数是一个软限制。需要时，排序内存堆可使用其他未使用的数据库共享内存。对于v9.1，建议用缺省值。（dbmcfg:sheapthres=0;dbcfg:sheapthres_shr=automatic,sortheap=automatic第64页/共76页REORG

离线重组三个阶段：1.build—2.replace—3.indexrecreate 1.Build使用表空间中空余空间（或指定表空间）临时生成一个表进行表的重组，即使中断也不会对原有表或索引产生影响 2.Replace替换原表阶段是无法认为手工中断的，即使在此阶段发出了中断命令（CTRL+C）也对等此步骤完成后，索引未重创前才中断，此时会明确告诉用户索引未创建。 3.Indexrecreate重建索引，可手工中断，同时明确告诉用户索引未创建。（SQL1279W

Someindexesmaynothavebeenrecreated.）4.4表优化第65页/共76页若表所在表空间剩余容量大于表自身大小，可在原表空间reorg若表所在表空间剩余容量小于表自身大小，需要指定usespace（系统临时表空间），且系统临时表空间与表所在表空间页大小一致，容量大于表自身大小。在线reorg表比离线reorg慢很多，因为在线不会创建数据副本，分批次重组，每次只有一部分数据在线reorg表也叫inplacereorg(原定reorg),reorg可随时启动和停止，为了保证恢复性，在线reorg会记录大量的日志，需要在日志空间依赖于移动的行数，表上索引的个数和索引键大小，因此可能是表大小的几倍如果在线重组的表很多，那么可能导致I/Ocpu资源占用很多，并且可能会消耗所有的活动日志，影响系统正常运行第66页/共76页RUNSTATS建议对一个频繁进行大量更新、插入或者删除操作的表进行RUNSTATS。runstats使用distrubution参数收集数据分布runstats有allowwriteaccess（默认）和allowreadacess表很大，或表数据频繁变动，可以考虑在某些字段上runstats，而不是整个表对于静态语句，还需要对package重新绑定，以便生成新的访问计划第67页/共76页5常见问题处理5.1常用工具5.2常见问题及经验分享第68页/共76页5.1常用工具 1)db2cfexp,db2cfimp实例配置信息导入导出

2)db2exfmt,db2expln是用来查看已收集并写入解释表（explain_instance)的全面解释数据

db2setcurrentexplainmodeexplain

执行sqldb2setcurrentexplainmodeno db2exfmt-dhljyctd-gTIC-w-1-n%-s%-#0-olimit_A_test1.out

3）访问计划是db2用于执行db2语句的路径和步骤，这是所有可用解释工具显示的信息 4）syscat.packages是用来存储数据库中的以程序包的形式的访问计划 5）db2exfmt是直接处理已收集并存在解释表中的全面解释数据或解释快照数据，输出是基于文本的报告第69页/共76页 6）db2advis索引设计工具，测试