DB2数据库管理培训课件

1、母版题样式DB2数据库管理2021-10-14浪潮软件21232基本概念监控与维护备份与恢复目录4性能优化5常见问题处理31基本概念n1.1 DB2数据库对象n1.2 对象层次关系4lDB2数据库体系系统实例数据库l数据库对象表、视图、索引、模式、存储过程、程序包等缓冲池、日志文件、表空间物理存储、表空间容器、目录、文件系统或裸设备n1.1 DB2数据库对象5基本概念DB2体系结构中的最高一层是系统，一个系统表示DB2的一个安装。在由很多机器组成的网络环境中，我们有时也称系统为数据库分区。一个系统可以包含多个DB2实例，每个实例能够管理一个或多个数据库。从操作系统角度看，DB2的实例是一组进程

2、和一组共享内存。实例为一个数据库的集合，共同运行在一个逻辑服务单元中（同一个端口）。在一台机器上可以有多个相互独立的实例，实例之间彼此独立，同时运行，不会相互影响。每个实例可以管理若干个数据库，一个数据库只属于一个实例。表空间是介于逻辑表和物理容器之间的逻辑对象,所有的表、索引和其他数据都存储在表空间中，表空间总是关联到相同页大小的缓冲池视图是虚拟的表，不需要物理存储，不包含真实的用户物理数据6系统、实例、数据库7n1.2 对象层次关系DB2中的数据库对象主要分为数据库、表空间、表、索引、视图、容器（存储组）。右图全局概要描述了各个对象在DB2中的关系。82监控与维护n2.1 监控工具n2.2

3、 监控要点n2.3 第三方监控工具9即时监控工具-snapshot-db2pd-db2top事件监控工具 Event monitor 新事件监控工具(9.7)n2.1 监控工具10snapshot需要打开实例级监控器开关，有一定性能损耗-snapshot命令行监控-snapshot管理视图（DB2V9后）管理视图较容易分析，通常用于查找存在性能问题的动态sqldb2pd不需要建立与数据库的连接，基本不会对系统造成影响常用参数，以下参数常组合使用，导出文件进行分析-appl 应用程序-bufferpool 缓冲池信息-logs 日志信息-tablespaces 表空间信息-locks 锁信息-a

4、gents 代理信息-dynamic 动态sql信息-tcbstats 表状态信息11db2pd常用方法常用方法用途一用途一 抓取锁信息，进行分析抓取锁信息，进行分析db2pd d dbname app tran agent dyn locks waitdb2pd.out分析db2pd.out第一步第一步-sts列 W表示等待 G代表被授权获得锁 12第二步第二步由第一步输出中的TranHdl对应到应用的AppHandl。得到TranHdl 2和6 分别对应到应用的AppHandl 30和34 第三步第三步获得应用程序的更多信息C-AnchID/C-StmtUID 和L-AnchID/L-St

5、mtUID 上。“C” 代表当前（current），“L” 代表最近（last）的语句 UID13第四步检查动态语句缓存的内容第四步检查动态语句缓存的内容经分析得出锁等待的语句为UPDATE EMPLOYEE SET SALARY = SALARY * 0.02拥有锁的APP的语句为UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = MANAGER14用途二用途二 查看表重组进度查看表重组进度db2pd d dbname reorg 用途四用途四 查看查看HADR同步状态同步状态15db2topdb2top 监视实用程序快速高效地监视复杂的

6、DB2环境。它结合来自所有数据库分区的 DB2 快照信息，使用基于文本的用户界面提供正在运行的 DB2 系统的动态实时视图。db2top不支持上下滚屏，一般用于了解表空间状态，缓冲池命中率，app执行 sql，了解处于锁等待状态的应用。进而用db2pd等工具分析示例：db2top d dbname 169.7新的锁事件监控新的锁事件监控Lock timeout events (MON_LOCKTIMEOUT) = hist_and_valuesDeadlock events (MON_DEADLOCK) = hist_and_valuesLock wait events (MON_LOCKWA

7、IT) = hist_and_valuesLock wait event threshold (MON_LW_THRESH) = 10000create event monitor lockevmon for locking write to unformattedevent table (table locks)Call sysproc.EVMON_FORMAT_UE_TO_TABLES(LOCKING,NULL,NULL,NULL,NULL,NULL,RECREATE_FORCE,-1,SELECT* FROM locks ORDER BY event_timestamp)只需用一个事件监

8、控器就可捕获死锁、锁超时、锁等的语句 17日常监控日常监控l 数据库状态l 表空间状态、使用率l applications状态l 锁情况l 性能指标常用性能指标常用性能指标l 读有效性rows_read/rows_selectedl 寻找性能较差的动态SQLl Buffpool命中率n2.2 监控要点18数据库状态db2 get snapshot for db on dbname表空间状态及占用率db2 get snapshot for tablespaces on dbnamedb2pd d dbname tabdb2top d dbname 出现界面后按t缓冲池命中率db2pd d dbn

9、ame bufdb2top d dbname 出现界面后按b是否有锁等待db2pd d dbname locks waitdb2top d dbname 出现界面后按U日常监控19l 读有效性rows_read/rows_selecteddb2 get snapshot for db on dbname:Rows read /Rows selected管理视图查询管理视图查询:select rows_read/rows_selected from sysibmadm.snapdb;对于OLTP，最好小于20对于OLAP，最好小于100 当读有效性值较高时，表示可能在发生表扫描，可通过索引等手段

10、进行优化 l 找到性能较差的dynamic SQL语句 通过Sysibmadm.snapdyn_sql视图，根据不同字段进行排序，找到性能较差的SQL语句排序较多的排序较多的sqlselect num_executions,sort_overflows,total_sort_time,stmt_text from sysibmadm.snapdyn_sql order by sort_overflows desc fetch first 10 rows only;执行耗时最长的执行耗时最长的sql select num_executions,total_exec_time,stmt_text

11、from sysibmadm.snapdyn_sql order by total_exec_time desc fetch first 10 rows only;常用性能指标20db2advis建议索引工具建议索引工具 采用db2advis建立合适的索引，使得其能够走索引查询，由于索引的范围和排序，可以直接跳过许其他行，定位到符合我们需要的行。前提条件前提条件创建分析表 db2 tvf &installpath/misc/EXPLAIN.DDL做数据库升级或数据迁移后，若版本与原有版本不一致，应先drop掉分析表，创建新的分析表常见用法如下:创建db2advis.in文件，保存要分析

12、的sqldb2advis d dbname i db2advis.in可参考建立索引21常用第三方监控工具QuestCentral.exen2.3 第三方监控工具22常用第三方监控工具QuestCentral.exe23APP状态：UOW EXECUTING:正在执行Wait for uow:等待执行Wait for lock：锁等待2.执行成本：该sql执行成本，数值越大，成本越高，执行越慢。如有多条成本较高的相同SQL，执行较慢，可考虑优化该SQL。3. 挂起锁的数量该数值表示挂起锁的数量，可观察哪些app挂起锁较多。4.该APP对应的SQL直观显示该APP对应的SQL，方便抓取运行缓慢，

13、锁等待等状态的sql24WAIT FOR LOCK状态APP锁信息监控25当前持有锁：显示当前持有锁的AGENT ID，锁名称及级别，对应锁的表等信息当前APP锁等待的APP显示当前APP正在哪个APP执行，信息包括被等待的APP的所名称及级别，持有锁的AGENT ID，根据AGENT ID可以得到被等待锁的APP所对应的SQL，考虑进行SQL优化或表优化等相关操作以上功能为较为常用及实用的监控功能，该工具在管以上功能为较为常用及实用的监控功能，该工具在管理及维护方便也较为方便安全。理及维护方便也较为方便安全。263备份与恢复n3.1 目的n3.2 基本概念n3.3 备份恢复n3.4 适用场景

14、27数据库备份和恢复的目的 从故障中恢复数据库系统，有效保护数据。 故障场景 系统崩溃(掉电、硬件故障、OS失败、数据库实例失败等) 事务失败(语句失败、进程失败等) 人为错误(误操作等) 存储媒介故障（存储器物理损坏等） 灾难(自然灾害等) n3.1 目的28事务事务 (事务=工作单元) 事务是组合在一起的一个或多个 SQL 操作序列，也被称为一个工作单元，用来保证数据的完整性和一致性。 特性如下: A 原子性操作序列不可分割，或全部成功或全部失败。 C 一致性事务执行前后数据库都处于一致性状态。 I 隔离性并发事务相互隔离，互不影响。 D 持久性事务被成功提交后，对数据库的更新被持久化，不

15、会丢失。 n3.2 基本概念29崩溃恢复崩溃恢复 从数据库日志控制文件中获取不一致起点信息，并根据日志内容对所有在不一致起点后成功提交事务进行再应用，而对其他未完成的事务进行回滚，从而把数据库带回可用、一致状态。 1.自动 - 数据库参数AUTORESTART=ON(缺省)2.手动 - 数据库参数AUTORESTART=OFF。使用db2 restart database30版本恢复版本恢复版本恢复=完全恢复，恢复数据库所有数据和结构。 循环日志模式下：备份和恢复都必须离线进行。执行DB2命令(backup/database)来进行全备份和版本恢复。 归档日志模式下：备份可以在线，而恢复必须离

16、线进行。在执行DB2命令(restore)进行恢复的时候，需指定without rolling forward选项来清除数据库的ROLLFORWARD PENDING状态。31前滚恢复前滚恢复前滚恢复=时间点恢复，恢复数据到一个指定的时间点。 归档日志模式下执行，有数据库完整备份,必须保留备份后到特定时间点之间所有的日志文件。 数据库前滚恢复必须离线进行。 在执行不带without rolling forward选项的DB2命令restore后，执行前滚恢复命令rollforward 将使数据库恢复至备份后的某一特定时间点或者日志末尾。32部分恢复部分恢复 部分恢复，恢复数据库一部分数据和结构

17、，工作原理同前滚恢复，区别是备份和恢复的对象是表空间 。主要特点主要特点 相对数据库备份，表空间备份和恢复在时间上占有优势，并且方法灵活。 系统编目表空间的备份和恢复必须离线进行,并且必须前滚至日志的末尾。而其他的表空间都可在线进行，并且可以前滚至某个特定时间点。 通过设定注册表变量DB2_COLLECT_TS_REC_INFO=ON，前滚命令将跳过不需要的日志来缩短恢复时间。 即使对象表空间不同，备份和恢复也不能同时进行。执行方式执行方式 利用DB2命令backup对所有关联表空间进行同时备份。 恢复时,利用DB2命令restore对所有关联表空间进行同时恢复，恢复完成时，表空间处于前滚暂挂

18、状态(ROLLFORWARD PENDING)。 利用DB2命令rollforward对表空间进行前滚恢复。 33离线全备 停止应用程序 断开所有数据库连接 执行备份命令 检查备份文件可用性 n3.3 备份恢复完全备份完全备份34在线全备 前提： 设置LOGARCHMETH1参数，更改参数后，必须需要完全离线全备份数据库 执行备份命令 检查备份文件可用性 完全备份完全备份35离线全备恢复 完全备份恢复步骤完全备份恢复步骤36在线全备恢复 完全备份恢复步骤完全备份恢复步骤37增量备份增量备份38增量备份增量备份-修改参数修改参数39增量备份增量备份-离线增量备份离线增量备份40离线增量备份离线增

19、量备份恢复步骤恢复步骤41增量备份增量备份-在线增量备份在线增量备份42在线增量备份恢复在线增量备份恢复43差量备份差量备份-离线差量备份离线差量备份恢复方式同离线增量备份恢复44差量备份差量备份-在线差量备份在线差量备份恢复方式同在线增量备份恢复45监控监控 可以使用db2 list utilities show detail命令监控执行状况。具体监控内容如下:1.backup显示需要处理的字节数的初始评估，随着备份进度更新已处理字节数。显示的字节数不对应备份镜像大小，也不能作为镜像大小的评估依据。2.restore & recovery - 初始字节数显示为UNKNOWN，实际读取

20、的字节数被更新，对于自动增量恢复，分阶段跟踪进度。每个阶段都提供增量链中的恢复镜像，初始状态只显示一个阶段。随着镜像的恢复，阶段数量和恢复的字节被更新。3.crash recovery & rollforward 有FORWARD和BACKWARD两个阶段。在FORWARD阶段，读取日志记录并应用于数据库。而在BACKWARD阶段，回滚未成功提交的事务。这两个阶段都显示已处理的字节数。46检查检查1.db2ckbkp用来检查备份镜像的完整性及是否可以用于恢复。也可以用于显示存储在备份文件头部的元数据。2.db2ckrst检查增量备份镜像的恢复顺序和时间戳，并获取恢复操作命令的序列。47

21、适用情况：数据库容器变更V9.1前：1. Restore 语句此语句用来标示一个重定向的恢复操作命令开始，它在普通恢复的命令上加了 redirect 参数。RESTORE DATABASE SAMPLE FROM /db2_backup/db2inst1/sample TAKEN AT 20101023180128 INTO SAMPLE REDIRECT;2. set containers 语句当目标数据库所的物理存储设备与原来的数据库不一样时，就需要下面的命令来指定新的物理容器。SET TABLESPACE CONTAINERS FOR 0 USING ( PATH /db2inst1/S

22、AMPLE ); SET TABLESPACE CONTAINERS FOR 1 USING ( PATH / db2inst1/temp ); SET TABLESPACE CONTAINERS FOR 2 USING ( DEVICE /dev/rsample_1G 131072 ) 3. restore continue 语句：此语句代表重定向恢复语句序列完成，系统开始恢复数据库。RESTORE DATABASE SAMPLE CONTINUE; 重定向恢复48V9.1后：使用RESTRORE . REDIRECT GENERATE SCRIPT 产生重定向恢复数据库的脚本db2 res

23、tore db SAMPLE FROM /db2_backup/db2inst1/sample taken at 20101023180128 REDIRECT GENERATE SCRIPT sample.db2修改生成的脚本,常见修改内容： 数据库名 数据库目录 容器信息执行修改后的脚本： db2 tvf sample.db249注意事项 如果数据库有自动存储表空间，则需要在自动生成的脚本内将 ON /DBPATH部分去掉注释并设置，否则会将自动存储表空间容器路径创建到原备份库的路径。 因异常情况，导致set container报 SQL0299N Container is already

24、 assigned to the table space解决办法： 执行db2untag -f /dev/lvname操作，去掉容器上表空间标识50适用情况 利用表空间备份重建数据库 还原部分表空间，重建数据库db2 restore db test rebuild with tablespace (SYSCATSPACE,USERSPACE1,USERSPACE2) taken at 20120516135136 注意事项： 必须包含SYSCATSPACE表空间 使用一组表空间镜像重建数据库，需要最早及最晚镜像时间点范围内归档日志，保证数据库一致性数据库重建51场景一 生产库由于误操作误删数据

25、，需要恢复误删数据如生产库为较不重要业务系统 ,且数据库较小，可采用离线全备分或在线全备份进行全库恢复，使用归档日志进行前滚恢复到误删数据前时间点。如生产库为核心业务系统，且数据库较大。则需要使用数据库备份异机进行恢复，若异机存储空间足够，可考虑全库恢复；若异机存储空间不足，考虑使用数据库重建技术，仅恢复丢失数据表所在表空间。n3.4 适用场景52场景二 将当前数据库，在同一台机器复制一份新的同样的数据库由于同一台机器，两个数据库不能有相同容器，否则会造成容器冲突，恢复失败。因此需要使用重定向恢复，修改容器名或容器目录。场景三 数据库所在存储损坏如数据库所在存储损坏，可紧急使用数据库备份进行全

26、库恢复。有全库数据库备份:使用全库数据库备份，在备用机建立相同容器，可直接完全恢复。若备用机无法建立相同容器，则需要使用重定向恢复修改容器仅有表空间备份：若仅有部分表空间备份，则仅能使用数据库重建，利用表空间备份重建数据库。53参考命令上述备份恢复方式均可组合使用，下列参考上述备份恢复方式均可组合使用，下列参考db2 “restore db xsmdb rebuild with tablespace(syscatspace,ec_trade) taken at 20120726170416 into xsmtest logtarget /home/db2inst2 redirect gener

27、ate script rebuild.db2“该命令为数据库重建，重定向恢复，在线备份恢复组合使用的示例。以上备份恢复方式不支持跨平台数据迁移，跨平台数据迁移需要db2look+db2move配合使用544性能优化n4.1 存储原理n4.2 规划设计n4.3 建议参数n4.4 表优化55 存储RAID级别常用RAID级别：RAID5，RAID10RAID5：最少需要3块盘，(n-1)/n的总磁盘容量(n为磁盘数)，读性能较好，由于奇偶校验，随机写及连续写性能一般，磁盘空间利用率较高。RAID10：最少需要4块盘，更好的读写性能，更好的可靠性，最多可允许N/2块磁盘故障，(N为磁盘总数)，磁盘空

28、间利用率较低，只能使用一半容量。n4.1 存储原理56相关概念条带条带条带（strip）是把连续的数据分割成相同大小的数据块，把每段数据分别写入到阵列中的不同磁盘上的方法。简单的说，条带是一种将多个磁盘驱动器合并为一个卷的方法57当使用当使用 LVM 或者硬件条带化时，决定条带化效果的因素是条带深度或者硬件条带化时，决定条带化效果的因素是条带深度(stripe depth)和条带宽度和条带宽度(stripe width)条带深度：这个参数指的是写在每块磁盘上的条带数据块的大小。条带宽度：是指同时可以并发读或写的条带数量。这个数量等于RAID中的物理硬盘数量。58基于RAID组划分，RAID组内

29、划分出的逻辑盘，操作系统里实际认到的物理盘。LUN的IO会经由条带，写入到RAID组各物理磁盘LUNDB2写容器的过程表空间是由很多容器组成的，我们每次写容器的时候，写的单位为一个扩展数据块 extent，EXTENTSIZE 指定数据库可以写到一个容器中的 PAGESIZE 页面数量，达到这个数量之后将跳到下一个容器。59n4.2 规划设计存储层：存储层：根据系统业务性能需求、成本估算、存储设备配置合理的RAID级别。如存储磁盘个数较多，建议划分多RAID组，不同RAID组间可IO分离。根据业务需求规划数据库表空间，根据不同表空间，对应不同RAID级别及底层存储。根据业务类型，设置合适的条带

30、大小。OLTP类型，可设置较大的条带深度，提高并发，一次物理 I/O 可以同时响应多个并发的逻辑 I/O ；OLAP类型，可设置较小的条带深度，使一个逻辑 I/O 分布到多个磁盘上，从而实现 I/O 的负载均衡。60数据库层：数据库层：EXTENTSIZE 大小选择大小选择：对于 OLAP 数据库和大部分对表的访问包括许多查询或处理大量数据的事务 ( 仅限于查询 ) 的表，或者增长速度很快的表，那么从表中预取数据可以显著改善性能，应使用较大的 extent 。反之，对于较小的频繁更改、频繁随机读取的小的配置表，建议使用较小的 extent 。PREFETCHSIZE大小选择大小选择DB2V9后

31、，如果把 PREFETCHSIZE 设置为 AUTOMATIC，添加容器后，数据库会自动调整 prefetchsize 的大小，所以强烈建议把 PREFETCHSIZE 设置为 AUTOMATIC 或 1。61优化优化 RAID 设备上表空间性能设备上表空间性能 在一组 RAID 设备上创建表空间时，应该把表空间容器创建在多个 RAID GROUP 上。 根据业务类型不同设置不同表空间，对应不同表空间参数 为表空间选择适当的扩展数据块 (extent) 大小。 文件系统型容器，关闭FILE SYSTEM CACHING，避免文件系统缓冲与数据库缓冲池多层缓冲，降低性能。62 LOCKLISK,

32、MAXLOCKS,LOCKTIMEOUTdb2 get snapshot for db on hljyctdNumber of Threshold Violations = 0Locks held currently = 118Lock waits = 2034Time database waited on locks (ms) = 3608810Lock list memory in use (Bytes) = 1572736Deadlocks detected = 0Lock escalations = 0Exclusive lock escalations = 0Agents curre

33、ntly waiting on locks = 0Lock Timeouts = 0Number of indoubt transactions = 0Lock list memory in use (Bytes)超过locklist大小50%，可增加locklist数量。如Lock escalations或Exclusive lock escalations0,应增加locklist或maxlocks。生产OLTP，locktimeout可设置10S，OLAP可设置60S，不建议设置默认值-1，会一直锁等待。n4.3 建议参数63 MAXAPPS若应用程序返回以下错误，表明连接到该数据库的应

34、用程序数达到最大值SQL1040N The maximum number of applications is already connected to the database可考虑增加maxapps设置 NUM_IOCLEANERS，CHNGPGS_THRESH NUM_IOCLEANERS异步页面清除器用于将更改后的页面从缓冲池写到磁盘。如果没有配置页清除程序，则应用程序可能会遇到严重的性能问题。一般禁止设置为0。CHNGPGS_THRESH缓冲池内被更改页面所占百分比，达到后将启动页面清除器，OLTP可设置成20-40，更新活动庞大，可设置较低值。 NUM_IOSERVERS该参数值就

35、是预取器的数量。一般该值等于数据库数据所在的磁盘数目。 MAXFILOP服务器打开文件的最大数目db2 get snapshot for db on testdb |grep -i close若Database files closed = 0不为0，需增大该值。64 LOGBUFSZ日志缓冲区，指定将日志记录写到磁盘前的缓冲区数量get snapshot for database on | grep i “Log pages”Log pages read = 0Log pages written = 12644理想状态，Log pages read为0，如果该值比较大，考虑增加LOGBUFS

36、Z值 UTIL_HEAP_SZ指定BACKUP,RESTORE和LOAD应用程序同时使用的最大内存数，可提高速度。 SORTHEAP、SHEAPTHRES_SHR和SHEAPTHRES在DB2 v9.1中，sheapthres_shr参数是一个软限制。需要时，排序内存堆可使用其他未使用的数据库共享内存。对于v9.1，建议用缺省值。（dbm cfg: sheapthres=0 ; db cfg: sheapthres_shr=automatic, sortheap=automatic65 REORG离线重组三个阶段：1.build2.replace3.index recreate1. Build

37、使用表空间中空余空间（或指定表空间）临时生成一个表进行表的重组，即使中断也不会对原有表或索引产生影响2. Replace替换原表阶段是无法认为手工中断的，即使在此阶段发出了中断命令（CTRL+C）也对等此步骤完成后，索引未重创前才中断，此时会明确告诉用户索引未创建。3. Index recreate重建索引，可手工中断，同时明确告诉用户索引未创建。（SQL1279W Some indexes may not have been recreated.）n4.4 表优化66 若表所在表空间剩余容量大于表自身大小，可在原表空间reorg 若表所在表空间剩余容量小于表自身大小，需要指定use spac

38、e（系统临时表空间），且系统临时表空间与表所在表空间页大小一致，容量大于表自身大小。 在线reorg表比离线reorg慢很多，因为在线不会创建数据副本，分批次重组，每次只有一部分数据 在线reorg表也叫inplace reorg( 原定reorg),reorg可随时启动和停止，为了保证恢复性，在线reorg会记录大量的日志，需要在日志空间依赖于移动的行数，表上索引的个数和索引键大小，因此可能是表大小的几倍 如果在线重组的表很多，那么可能导致I/O cpu资源占用很多，并且可能会消耗所有的活动日志，影响系统正常运行67RUNSTATS 建议对一个频繁进行大量更新、插入或者删除操作的表进行 RU

39、NSTATS。 runstats 使用distrubution 参数收集数据分布 runstats有allow write access（默认）和allow read acess 表很大，或表数据频繁变动，可以考虑在某些字段上runstats，而不是整个表 对于静态语句，还需要对package重新绑定，以便生成新的访问计划685常见问题处理n5.1 常用工具n5.2 常见问题及经验分享695.1 常用工具常用工具1)db2cfexp ,db2cfimp 实例配置信息导入导出2)db2exfmt,db2expln是用来查看已收集并写入解释表（explain_instance)的全面解释数据db2 set current explain mode explain执行sql db2 set current explain mode no db2exfmt -d hljyctd -g TIC -w -1 -n % -s % -# 0 -o limit_A_test1.out 3）访问计划是db2用于执行db2语句的路径和步骤，这是所有可用解释工具显示的信息4）syscat.packages是用来存储数据库中的以程序包的形式的访问计划5）db2exfmt是直接处理已收集并存在解释表中的全面解释数据或解释快照数据，输出是基于文本的报告706）db2advis索引设计工