DB2系列之数据库规划(一)

数据库规划

1硬件选型

1.1 主机

从响应时间(应用向数据库服务器发起请求，服务器完成处理并返回结果给应用，中间总共消耗的时间，它反映了数据库服务器的处理速度)角度出发。

• 关键业务系统优先选择Power服务器。

• 从性价比考虑可以选择X86服务器。

• 由于预算的原因，可以先选择X86服务器作为过渡，随着数据量和用户量的变大，到了一定阶段再迁移到Power服务器上。

选择主机的第一步是决定CPU的内核个数，主机的CPU内核个数，可以采用倒推法。对于内存，推荐1个Power处理器内核配备8G内存，一个X86处理器内核配备4GB内存；对于存储，1个Power处理器内核可以处理300~500GB活动数据，1个X86处理器内核可以处理200~300GB活动数据。

按照经验：OLTP系统中，对于大小为200G(通过db2"callGET_DBSIZE_INFO(?,?,?,0)"得到的数值，不是数据库容量值)的数据库，为了达到90%的缓冲池命中率(最低要求)，缓冲池大小建议为20G，物理内存大小建议为32G，因此CPU内核个数建议为4个，如果SQL特别复杂、表关联很多、计算很多，那么CPU个数需要相应的增加。

1.2 网络

包括数据库系统内部使用的网络环境和外部应用访问数据库的网络环境。

数据库系统内部使用的网络环境：DB2 DPF(千兆网以上)、DB2 pureScale(无限带宽)。

外部应用访问数据的网络环境：例如常见的IBM中间件WAS与DB2数据库之间。如果使用独立的高速网络，将会大幅提升部署在WAS上的应用系统访问数据的性能。

1.3 存储

存储是存放数据和日志的地方，因此存储的设计格外重要。特别是随着现代服务器技术的发展，磁盘技术的发展远远落后于CPU和内存的技术发展，这个时候如果出现了存储设计不合理，那么由此导致的I/O瓶颈往往会成为性能最薄弱的地方。

单块磁盘的I/O能力往往无法满足应用的性能需要。通常时候还是使用磁盘阵列设备(RAID)。磁盘阵列使用条带化技术将I/O负载均衡分布在多个物理磁盘上，一次逻辑I/O请求分割成多个小部分，每个小部分同时访问存储在不同物理磁盘上的数据，这样可以最大程度地降低磁盘冲突(多个进程同时访问同一块磁盘)，提升I/O并行度，从而提升I/O性能。

RAID

磁盘阵列包括RAID0、RAID1、RAID5、RAID10等，如图1-1所示，由5块磁盘组成的磁盘阵列以及相应的RAID0、RAID1和RAID5级别。            

图1-1 磁盘阵列示意图

条带化

条带化将数据均匀地分布到不同的磁盘上，同时也将负载分担到多个磁盘上。这样，多个磁盘就可以并行工作，吞吐量会大大提高。

未经条带化处理的数据：如图1-2所示，所有的数据会依次写满三个磁盘，先写满第一个，然后再写第二个、第三个。

图1-2 未经条带化处理的连续数据

条带化处理的数据：如图1-3所示，以扩展分区（Extent）为单位，依次写入三个磁盘，然后不断循环往复。

图1-3已经被条带化处理的连续数据

条带化对连续数据的读写有更大的并发能力，需要读取 1 到 3 扩展分区中的数据时，如果没有条带化，只能从第一块磁盘中读取；如果经过条带化，那么就可以同时访问三个磁盘，条带化磁盘的速度是未经条带化磁盘的三倍。同样，如果写入大量数据，只能操作一个物理磁盘进行写操作，条带化可以同时对三块物理磁盘进行写操作。

条带深度：一个扩展块大小

条带宽度：条带深度*条带宽度=一个预取块大小

RAID级别选取：表空间使用RAID5，活动日志使用RAID10。

条带的实施：存储支持条带化，UNIX/LINUX等都在LVM（逻辑卷管理）上支持条带化，DB2通过多容器支持条带化。建议只在存储层实施条带化，最多也不要超过两层。

裸设备与文件系统的比较

多个盘构建在一起组成RAID5阵列，该阵列划分为一个LUN，之后在该LUN上是创建裸设备还是文件系统给表空间使用呢？这里对裸设备和文件系统进行个比较。

裸设备与文件系统的最大不同在于它可以避开文件系统的缓存以及锁机制，因此，可以通过裸设备提高数据库的性能。但是，裸设备的弊端是可管理性差，容易出问题，不被DB2的自动存储管理特性所支持，不支持大字段表空间的FILE SYSTEM CACHING。

随着文件系统的技术发展，现在许多文件系统，例如AIX下面的JFS2都支持CIO模式，也就是说可以利用文件系统的CIO或DIO能力达到与裸设备相似的性能，而且还兼顾了可管理性和安全性。

开启CIO/DIO有两种方法：第一种方法是将文件系统mount成CIO或DIO，例如mount –o cio home/dbdata，这样做会导致被mount的文件系统上所有文件都使用CIO或DIO；第二种方法在创建或修改表空间时指定NO FILE SYSTEM CACHING属性，它只是对表空间所对应的容器使用CIO/DIO，并不会影响到文件系统上的其他文件。

无论使用上述哪种方法开启CIO或DIO，都必须在操作系统中正确地安装和配置AIO库。

考虑到裸设备难于管理，而且性能也未必比文件系统好，建议选择文件系统。使用dd 命令测试文件系统的读写能力，验证存储的实际读写速度是否能满足数据库系统的 I/O 要求。dd if=/dev/zero of=/home/dbdata/test.file bs=8192 count=5000000，一般要求能达到200MB/S。

2数据库

对于OLTP(在线事务处理)数据库，建议数据库页大小设置为8K，代码集为UTF-8，territory为CN(决定日期和时间的显示格式)，禁用自动存储，命令如下：

db2"create db bitscn automatic storage no using codeset utf-8 territory cn pagesize 8192"

备注：

●代码页对照表

如果AIX的LANG=en_US(对应codepage为819)，DB2数据库的codeset为GBK(对应codepage为1386)，那么连数据库的时候将报错：

这是因为数据库的代码页1386无法直接转换到819。解决办法是设置注册表变量DB2CODEPAGE=1208。

codepage的使用比较复杂，明细请参考另一篇文章《关于DB2CODEPAGE的总结》

3表空间

3.1 表空间管理方式

SMS

系统管理表空间是指在创建表空间时指定MANAGED BY SYSTEM选项，并且指定一个或若干操作系统路径。

CREATE TABLESPACE SMSTS MANAGED BY SYSTEM USING ('/path1', '/path2', '/path3')

采用系统管理表空间，用户无须自己管理，一切由系统进行打理，好处是比较简单，管理方便，但因为页是按需分配，分配不连续，所以性能上不是很好。一般系统临时表空间会使用系统管理表空间，对于OLTP中的临时表空间，可以用默认的SMS，对于OLAP中的临时表空间，建议用DMS。

DMS

数据库管理表空间是指在创建表空间时指定 MANAGED BY DATABASE 选项，并且指定具体的文件或裸设备作为容器，表空间的所有对象都被放在这些容器中。文件的存储空间会预先分配好，在预分配的存储空间用完以后可以手动或或者自动追加空间，DMS因为表空间大小是预分配的，页的分配大体上是连续的，因此比SMS效率要高5~10%。所有表空间都可以定义为DMS类型。

CREATE TABLESPACE DMSTS MANAGED BY DATABASE

USING (FILE  '/path1/dmsdata1' 5000,FILE '/path2/dmsdata2' 5000,FILE '/path3/dmsdata3' 5000)

采用数据库管理表空间，好处是性能比较高效，但是管理上稍微复杂一点。

DMS表空间读写机制是采取轮循的方式。也就是说，当一个扩展块的数据写到容器1下面的文件后，接下来一个扩展块的数据写到容器2下面的文件中，再接着一个扩展块数据写到容器3下面的文件中，最后，又会翻回来写容器1，如此反复，如图3-1所示。

图3-1 DMS表空间容器的存储结构

● DMS表空间修改

要使用命令行将容器添加到DMS表空间，请输入以下内容：

ALTER TABLESPACE <NAME> ADD(DEVICE ‘<PATH>’ <SIZE>,FILE ‘<FILENAME>’ <SIZE>)

例如：

alter tablespace TRX_INDEX_DMS_8K add(file '/home/dbdata/bitscn/ECIF_8K' 19660000);

alter tablespace TRX_INDEX_DMS_8K drop(file '/home/dbdata/bitscn/ECIF_8K');

添加容器会涉及到表空间的重新平衡，但扩展不会重新平衡表空间数据。

原来大小为1000页，使用如下命令将从1000页增大至1200页

ALTER TABLESPACE TS1 EXTEND(ALL 200)

原来为1000页，使用如下命令文件大小将从1000页减少至800页

ALTER TABLESPACE PAYROLL REDUCE(FILE ‘d:\hldr\finane’ 200)

原来为1000页，使用如下命令文件大小将从1000页调整到2000页

ALTER TABLESPACE PAYROLL RESIZE(FILE ‘d:\hldr\finane’ 2000)

管理表空间容器尺寸的最佳实践是保证容器的扩展是等大小的，这样可以防止热区的出现。所谓热区，指的是表空间的某一个或若干容器比其他容器大，导致某一块磁盘访问频率明显高于其他磁盘。如图3-2所示，扩展块13-15不能同时被并行访问，并且导致某个磁盘访问量大于其他磁盘，产生读写冲突，影响读写性能。

图3-2  DMS表空间容器的热区

● 表空间高水位降低

高水位标记是表空间中的最高已分配页，处于高水位标记下的空闲页是不能被其他新表使用的，降低高水位有助于提高表空间的利用，提高数据库性能。

V9.7及之后的版本可通过alter tablespace回收空余空间来降低HWM，语法如下：

db2 "alter tablespace tablespace-name lower high water mark"-针对DMS表空间

db2 "alter tablespace tablespace-name reduce max"-针对自动存储管理表空间

在降低高水位的时候会一起移动必须移动的数据块，这个过程可能会影响系统性能，如果需要停止数据块移动，使用STOP参数终止。

db2 "alter tablespace tablespace-name lower high water mark stop"-针对DMS表空间

db2 "alter tablespace tablespace-name reduce max stop"-针对自动存储管理表空间

自动存储

自动存储管理底层使用的仍然是DMS或SMS类型，只不过在创建表空间时不需要指定任何路径或者文件。对于数据表空间，DB2会选用DMS方式管理；对于临时表空间，会选用SMS方式管理。

CREATE TABLESPACE <NAME>

CREATE TABLESPACE <NAME> MANAGED BY AUTOMATIC STORAGE

需要在数据库层面开启自动存储属性

db2"create db bitscn automatic storage yes on home/fs1,/home/fs2,/home/fs3 dbpath on home/db2ibits using codeset utf-8 territory cn pagesize 8192"

下面的语句，给数据库增加了三个存储路径，分别为/path1、/path2 和/path3：

ALTER DATABASE ADD STORAGE ON '/path1', '/path2', '/path3'

自动存储管理表空间的数据存放在建库时指定的ON目录，也叫自动存储路径，数据库默认支持自动存储管理，通常在建库时使用automatic storage no来关闭自动存储管理表空间。

通过db2pd –d bitscn –tablespaces，得到的Tablespace Autoresize Statistics:中的AS，AR分别表示Automatic Storage（自动存储），Auto-Resize（自动调整）。

不建议开启自动存储属性，自动存储相当于在数据库层面做了一层条带，其他没任何好处。

3.2 表空间管理方式

系统编目表空间

在创建数据库之后，DB2 默认会自动创建，名字为 SYSCATSPACE，用来存放表的定义、授权和统计信息等系统数据。

系统临时表空间

默认创建，名字为TEMPSPACE1，用于存储SQL操作期间包括排序、表连接等所需要的内部临时数据。

db2 "create temporary tablespace temp32K pagesize 32k managed by system using('/home/dbdata/temp32K') bufferpoolbp 32k“

用户临时表空间

默认不创建，用于存储临时表。

CREATE USER TEMPORARY TABLESPACE usr_tbsp MANAGED BY DATABASE USING(FILE'd:\db2data\user_tbsp'5000,FILE'e:\db2data\user_tbsp'5000)

常规表空间

用于存放数据或索引，常规表空间只能支持单表最大尺寸为 512GB 的数据，现在不建议使用常规表空间了。

db2 "create tablespace TBS_DATA_DMS_32K pagesize 32k managed by database using(file'/home/dbdata/TBS_DATA_DMS_32K'100M) extentsize 32 prefetchsize automatic bufferpool bp32k no file system caching"

大型表空间

从 DB2 V9 开始引入，从字面上可以看出大型表空间的容量要比常规表空间更大，可以支持单表最大尺寸为 16TB 的数据，建议使用。

常规表空间与大型表空间比较：

DB29之后，创建的DMS表空间默认都为大型表空间，SMS表空间只能为常规表空间。

表空间中的页的最大数量受限于行指示器（RID）的大小。RID是表中一行的参考位置，它包含了表空间中的页面数量以及页中行的槽位数量。

在DB29之前的版本，行指示器有4个字节的长度。其中，3个字节用于在常规表空间中指向页，另外的那个字节则代表页中的行号。所以，该指示器表空间含有的页数为2^24=16777216（个），一页中可包含的行记录数理论上为2^8=256（个），所以页面大小为4KB时，表空间的大小为16777216*4=64GB。对于不同的页面有最小的行长度限制，如下图：

为了能够让页中容纳更多的行以及表空间中能够容纳更多的页，在DB29中引入了一种新的行指示器的格式。页的数量从3个字节增加到了4个字节，槽位的数量从1字节增加到了2字节。这种新型的6字节RID格式把表空间大小扩充到了原来的32倍，一页中所容纳的行数从256增加到了大约2300行（理论值为65536行，但最小的行大小约为12字节，所以能容纳大约2300行），对于新格式不同的页面有最小的行长度限制，如下图：

旧格式现在主要是指常规表空间，新格式被称为是大型表空间。大型表空间现在是DB29数据库中DMS表空间的默认类型。如果需要继续创建较小的表空间格式，就需要指定表空间类型为REGULAR。在表数据的某个页面内，保留了68字节给DB2，其余4028字节可以提供给用户数据所使用。没有一个单独的行长度能够超过4005字节（8KB的页为8101）（这是结论值，不要通过4096-68的方式来计算，记住4005就行）。在一个使用4KB页面大小的表空间中，一个表能定义的最大的列数为500，而使用8、16以及32KB页面的则是1012列。

注意：

1)、LOB和长字段数据可以存放在任意的页面大小的表空间中。

2)、SMS表空间，仅支持REGULAR表空间。

3)、为了将表空间修改为LARGE格式，命令如下：alter tablespace test_lob_32k convert to large。一旦表空间被修改，为了更好地利用较大RID的优势需要采取另外两个步骤：

• 重组索引

• 重组数据

重组索引将允许表空间增加所分配的页的数量，为了将表空间容量增长至超过64GB的限制，这个步骤是必需的。如果不进行索引的重组，表空间将会延续原有的64GB的限制。

重组表与重组索引的重要程度不同。索引控制着表空间中页数量的增长，表的重组能够使每页有更多的行。

3.3 表空间管理方式

页大小

表空间支持的页大小，有4KB、8KB、16KB、32KB。

在一个使用4KB页面大小的表空间中，一个表能定义的最大的列数为500，而使用8、16以及32KB页面的则是1012列。

OLTP事务型应用，以随机读写为主，可以采取较小的页面，例如8KB更合适，太大容易浪费缓冲池，降低并发度，太小需要发起较多IO，可能引起IO瓶颈。而且8KB的大小也能兼顾分析型，因为OLTP数据库也有许多比较复杂的查询，可能需要读一个表中的大多数数据进行加工处理。

扩展块大小(extent)

数据是以extent为单位存放的，一个extent由多个页组成，大小在创建表空间时指定。

预取大小

预取操作在查询使用所需的数据之前读入这些数据，因为数据已经在内存中了，这样就不必等待执行IO了。建议将Prefetch Size设置为Automatic，由数据库采用以下的公式进行计算：

预读取页面数量=容器数量*每个容器的物理主轴数量*扩展块大小

其中物理主轴数量可以通过实例级注册表变量DB2_PARALLEL_IO来设定。

可以通过db2look –d sample –l –o db2look.sql查看数据库中表空间PREFETCHSIZE的值，如果为AUTOMATIC；通过db2 list tablespaces show detail中的Prefetchsize(pages)可以看到数据库计算的实际值。

数据库的读分三种：顺序预取、列表预取、数据库管理器代理程序预取。

顺序预取：应用程序在需要这些页之前，将连续的页读至缓冲池中的一种机制，一次发出N个预取请求，每个预取请求最多只能读一个prefechSize页面。

列表预取：又称为列表顺序预取，有效预取一组非连续的数据页。

这两种预取数据页的方法是对数据库管理代理程序预取方法的补充。当仅检索一页或少量连续页，但只传送一页数据时，才使用数据库管理器代理程序预取方法。

数据库代理程序直接调度非预取I/O，即同步I/O，因此非预取I/O不受NUM_IOSERVERS约束。

顺序预取、列表预取是预取I/O和异步I/O，所以需要依赖NUM_IOSERVERS的个数。

3.4  表空间设计最佳实践

(1) 页大小设计为8KB，可以较好的支持事务型，也能兼顾分析型

(2) 扩展块大小设置为32，对于RAID5条带大小设置为32*8KB=256KB

(3) 预取大小设置为AUTOMATIC，由数据库进行计算给出预取最佳值

(4) 放弃自动存储管理，选用DMS表空间，这样可以灵活定义

(5) 数据、索引、大字段使用不同的表空间存储

(6) 大表、小表、热表、冷表分开存放，定义在不同的表空间中，也可以按照模块划分，比如产品、批量、组件、工作流使用不同的表空间。

(7) 数据表空间使用单独的缓冲池、所有索引使用同一个缓冲池、大字段不使用缓冲池。

(8) 创建一个32K的系统临时表空间，以应对临时数据页面大小超过16K的情况。

(9) 建议每个表空间只配置一个容器，这样方便维护和重定向恢复，对于OLTP系统也实用，容器可以是裸设备、也可以是文件系统或目录，建议为CIO文件系统。

(10) 大对象（CLOB、BLOB）数据的获取直接通过磁盘，无法通过Bufferpool缓存，建议对大对象数据表空间使用file system caching选项。

3.5 表空间设计案例

假设数据库初始大小为300G，每年增长100G，4年内，数据库总大小为700G，其中分流200G到历史库，之后正式库大小维持在500G，历史库每年增长100G。对于正式库大小为500G，估计数据分布为：其中业务数据100G、工作流140G、JUMP_TF占10G、组件表占250G。

删除默认创建的用户表空间USERSPACE1

删除默认创建的系统临时表空间TEMPSPACE1。

4缓冲池

创建缓冲池：

Create bufferpool BP32K size 3000 pagesize 32K;

使用缓冲池：

Create tablespace DATA_DMS_32K pagesize 32k managed by database using(file '/home/dbdata/bitscn/DATA_DMS_32K' 10000) bufferpool BP32K;

查看表空间使用缓冲池：

db2"select a.tbspace,b.bpname from syscat.tablespaces a,syscat.bufferpools b where a.bufferpoolid=b.bufferpoolid"

修改缓冲池：

db2"alter bufferpool BP32K immediate size 32768"

(缓冲池自动调整，初始值为65536页)

db2"alter bufferpool BP32K immediate size 65536 AUTOMATIC"

db2"alter bufferpool BP32K immediate size automatic"

db2"alter bufferpool BP32K immediate size -1"

syscat.bufferpools表中NPAGES对应的值分别为

32768

-2

-2

-1

如果为-2，表示由数据库管理器进行内存自动调整。

如果为-1，表示取数据库参数BUFFPAGE的值；

块缓冲池：

CREATE BUFFERPOOL TRX_DATA_BP16K SIZE 1000 numblockpages 320 blocksize 32 PAGESIZE 16384;

定义db2的缓冲池需要谨慎，如果缓冲池设置过大会导致物理内存耗尽。结果是数据库连接不上，解决方案为：

设置db2set注册变量db2_override_bpf的值为一个较小值，比如1000，这样将会限制每个缓冲池占用的内存大小为1000个页面，要然该设置生效，需要重启实例，之后连接数据库应该能够成功，连接上数据库后，立即修改缓冲池大小到合适的页面。修改完毕，最后通过db2set db2_override_bpf=恢复设置，再重启实例。

4.1  缓冲池设计最佳实践

(1) 数据表空间和索引表空间使用不同的缓冲池，方便管理和提高性能。

(2) 针对不同的应用特点量身定做缓冲池，对于OLTP，使用多个缓冲池，以便长时间缓存应用所使用的数据页。

(3) 为频繁使用的热表设立单独的缓冲池，以防被大表交换出去导致反复读取。

4.2 缓冲池设计案例

建议物理内存大小为64GB，虚拟内存大小为96GB

物理内存分配如下（缓冲池占80%，即51.2G，其他用于操作系统）

5日志

采用归档日志，关于日志的参数建议值如下：

日志和数据分开放在不同的RAID上，因为日志需要很高的写的效率，所以建议用RAID10，同时指定镜像日志路径。

数据中心归档一周之前的日志。

6数据库备份与恢复

备份：

对于大容量数据库，每次全备需要占用大量时间和空间。因为BITSCN数据库大小估计在500G左右，如果进行每日在线全量备份将耗时250分钟左右，这样影响数据库时间长、占用资源多。所以，计划采用在线全备、累积增量备份、迭代增量备份三者结合的方式。

在线全备：备份过程中数据库仍然是可读可写的，要求日志是归档模式。

累积增量备份(cumulative backup)：DB2备份自上一次完整数据库备份以来发生变化的所有数据，incremental表示增量累积备份。

迭代增量备份(delta backup)：DB2备份自上一次成功执行的完整备份、增量备份或delta备份以来发生变化的数据，incremental delta表示迭代增量备份。

备份策略：

第一种

第二种

CHNBITS系统，结合批处理的需要，考虑每天批前做累积增量备份、批后做迭代增量备份、周六凌晨02：00做一个在线全备，如下图：

备份命令分别为：

周六凌晨两点在线全备：

db2 backup db bitscn online to home/dbbackup/bitscn/all compress include logs

产生的备份镜像为20131120193936

累积增量备份：

db2 backup db bitscn online incremental to home/dbbackup/bitscn/incremental compress include logs

产生的备份镜像为20131120194021

delta备份：

db2 backup db bitscn online incremental delta to home/dbbackup/bitscn/delta compress include logs

产生的备份镜像为20131120194040

恢复：

先把这些文件复制到/home/db2ibits/dbbackup目录

如果需要恢复到20131120193936，恢复命令为：

db2 restore db bitscn incremental automatic taken at 20131120193936

如果需要重定向，则执行：

db2 restore database bitscn incremental automatic from home/db2ibits/dbbackup taken at 20131120193936 into bitscn redirect generate script bitsdb.clp WITHOUT PROMPTING

db2 –tvf bitsdb.clp

然后解压缩日志并前滚该日志，使数据库可用：

db2 "restore db bitscn logs taken at 20131120193936 logtarget home/db2ibits/dbbackup/logs"

db2 "rollforward db bitscn to end of logs and stop overflow log path (/home/db2ibits/dbbackup/logs) noretrieve"

备份通过批处理作业实现，作业清单填写在CTRL-M调度表中，供数据中心调度执行。

数据中心归档一周之前的日志，两周之前的数据库备份(因为存在增量备份、差分备份和在线全备，只对两周之前的备份归档，方便从生产环境提取最近两周内的备份文件)。

7建库语句

db2 create db bitscn automatic storage no using codeset utf-8 territory cn pagesize 8192 CATALOG TABLESPACE MANAGED BY SYSTEM USING ('/home/dbdata/bitscn/SYSCATSPACE') EXTENTSIZE 32 PREFETCHSIZE 32;

--数据库日志参数配置

db2 connect to bitscn

db2 update db cfg using NEWLOGPATH home/dbactivelog

db2 update db cfg using LOGARCHMETH1 DISK:/home/dbarchivelog

db2 update db cfg using LOGPRIMARY 10

db2 update db cfg using LOGSECOND 30

db2 update db cfg using LOGFILSIZ 25600

db2 update db cfg using LOGBUFSZ 8192

db2 update db cfg using TRACKMOD ON

db2 update db cfg using LOCKTIMEOUT 120

db2 update db cfg using MON_LOCKTIMEOUT HIST_AND_VALUES MON_DEADLOCK HIST_AND_VALUES MON_LOCKWAIT NONE

db2 update db cfg using SECTION_ACTUALS base

db2 backup db bitscn  --BACKUP PENDING

--设置实例注册表变量：

db2set DB2_RESTRICT_DDF=TRUE

db2set DB2COMM=tcpip

db2set DB2CODEPAGE=1208

--数据库管理器参数配置

db2 update dbm cfg using DFT_MON_BUFPOOL ON DFT_MON_LOCK ON DFT_MON_SORT ON DFT_MON_STMT ON DFT_MON_TABLE ON DFT_MON_TIMESTAMP ON DFT_MON_UOW ON

db2 update dbm cfg using MON_HEAP_SZ 20000

db2 update dbm cfg using SVCENAME 50000

--断开所有连接、重启实例，使设置生效

db2 connect reset

db2stop force

db2start

--创建缓冲池，块缓冲池约占缓冲池总大小的20%：

connect to bitscn;

CREATE BUFFERPOOL  TRX_DATA_BP16K  SIZE 32768  numblockpages 6400 blocksize 32 PAGESIZE 16384;

CREATE BUFFERPOOL  DATA_BP8K  SIZE 65536 numblockpages 6400 blocksize 32 PAGESIZE 8192;

CREATE BUFFERPOOL  INDEX_BP8K  SIZE 65536 numblockpages 6400 blocksize 32 PAGESIZE 8192;

CREATE BUFFERPOOL  BP32K  SIZE 16384 numblockpages 6400 blocksize 32 PAGESIZE 32768;

ALTER BUFFERPOOL IBMDEFAULTBP  IMMEDIATE  SIZE 65536;

ALTER BUFFERPOOL IBMDEFAULTBP  numblockpages 6400 blocksize 32;

(如果是一开始没有指定块缓冲池，修改的方式为：

alter bufferpool TRX_DATA_BP16K numblockpages 6400 blocksize 32

alter bufferpool DATA_BP8K numblockpages 6400 blocksize 32

alter bufferpool index_bp8k numblockpages 6400 blocksize 32

alter bufferpool BP32K numblockpages 6400 blocksize 32

alter bufferpool IBMDEFAULTBP numblockpages 6400 blocksize 32

)

--创建表空间：

--创建系统临时表空间

create system temporary tablespace TEMPSPACE32K pagesize 32k managed by system using ('/home/dbdata/bitscn/TEMPSPACE32K') bufferpool BP32K;

create system temporary tablespace TEMPSPACE8K pagesize 8k managed by system using ('/home/dbdata/bitscn/TEMPSPACE8K') bufferpool IBMDEFAULTBP;

drop tablespace TEMPSPACE1;

--修改系统编目表空间

alter tablespace SYSCATSPACE bufferpool IBMDEFAULTBP;

alter tablespace SYSCATSPACE prefetchsize automatic;

--删除默认用户数据表空间

drop tablespace USERSPACE1;

--创建用户数据表空间

create tablespace TRX_DATA_DMS_16K pagesize 16k managed by database using (file '/home/dbdata/bitscn/TRX_DATA_DMS_16K' 10000) bufferpool TRX_DATA_BP16K;

create tablespace TRX_LOB_DMS_16K pagesize 16k managed by database using (file '/home/dbdata/bitscn/TRX_LOB_DMS_16K' 10000) bufferpool TRX_DATA_BP16K;

create tablespace TRX_INDEX_DMS_8K pagesize 8k managed by database using (file '/home/dbdata/bitscn/TRX_INDEX_DMS_8K' 10000) bufferpool INDEX_BP8K;

create tablespace COMP_DATA_DMS_8K managed by database using (file '/home/dbdata/bitscn/COMP_DATA_DMS_8K' 10000) bufferpool DATA_BP8K;

create tablespace COMP_LOB_DMS_8K managed by database using (file '/home/dbdata/bitscn/COMP_LOB_DMS_8K' 10000) bufferpool DATA_BP8K;

create tablespace COMP_INDEX_DMS_8K managed by database using (file '/home/dbdata/bitscn/COMP_INDEX_DMS_8K' 10000) bufferpool INDEX_BP8K;

create tablespace WF_DATA_DMS_8K managed by database using (file '/home/dbdata/bitscn/WF_DATA_DMS_8K' 10000) bufferpool DATA_BP8K;

create tablespace WF_LOB_DMS_8K managed by database using (file '/home/dbdata/bitscn/WF_LOB_DMS_8K' 10000) bufferpool DATA_BP8K;

create tablespace WF_INDEX_DMS_8K managed by database using (file '/home/dbdata/bitscn/WF_INDEX_DMS_8K' 10000) bufferpool INDEX_BP8K;

create tablespace TF_DATA_DMS_8K managed by database using (file '/home/dbdata/bitscn/TF_DATA_DMS_8K' 10000) bufferpool DATA_BP8K;

create tablespace TF_LOB_DMS_8K managed by database using (file '/home/dbdata/bitscn/TF_LOB_DMS_8K' 10000) bufferpool DATA_BP8K;

create tablespace TF_INDEX_DMS_8K managed by database using (file '/home/dbdata/bitscn/TF_INDEX_DMS_8K' 10000) bufferpool INDEX_BP8K;

--设置大字段表空间启用文件系统缓存

alter tablespace TRX_LOB_DMS_16K File system caching;

alter tablespace COMP_LOB_DMS_8K File system caching;

alter tablespace WF_LOB_DMS_8K File system caching;

alter tablespace TF_LOB_DMS_8K File system caching;

--对表的BLOB大字段进行扩展，默认为1M，现在修改为100M

connect to bitscn;

ALTER TABLE TF_STEP_CONTEXT ALTER COLUMN CONTEXT SET DATA TYPE BLOB(104857600);

ALTER TABLE TF_FLOW_CONTEXT ALTER COLUMN CONTEXT SET DATA TYPE BLOB(104857600);

REORG TABLE TF_STEP_CONTEXT;

REORG TABLE TF_FLOW_CONTEXT;

--设置用户db2obits具有dbadm的权限，在db2ibits用户下执行

db2 connect to bitscn

db2 grant dbadm on database to user db2obits

--设置用户checkb具有查询的权限，在db2obits用户下执行

授予checkb用户具有查询某个表的权限

db2 "grant connect on database to user checkb"

db2 "grant usage on WORKLOAD SYSDEFAULTUSERWORKLOAD to user checkb"

db2 "grant execute on PACKAGE NULLID.SQLC2J25 to user checkb"

db2 "grant select on table db2obits.WF_ROLE to user checkb"

批量执行select和export授权

select 'grant select on table db2obits.'||tabname||' to user checkb;' from syscat.tables where tabschema='DB2OBITS'

得到如下赋权限的语句，并用db2obits用户执行

grant export on table db2obits.ACCOUNTING_EVENT to user checkb;

--用db2ibits用户创建锁事件监视器等（下面的语句都用DB2IBITS用户操作）

db2 "create event monitor lockevmon for locking write to unformatted event table (table locks)"

db2 set event monitor lockevmon state=1

--补充创建记录长度超过16K的表

create tablespace DATA_DMS_32K pagesize 32k managed by database using (file '/home/dbdata/bitscn/DATA_DMS_32K' 10000) bufferpool BP32K;

--删除系统默认创建的死锁事件监视器

db2 "set event monitor DB2DETAILDEADLOCK state 0"

db2 "drop event monitor DB2DETAILDEADLOCK"

--创建新的锁事件监视器并激活

db2 "create event monitor lockevmon for locking write to unformatted event table (table locks)"

db2 "set event monitor lockevmon state=1"

db2 "set event monitor lockevmon state=0"

db2 "call sysproc.EVMON_FORMAT_UE_TO_TABLES('LOCKING',NULL,NULL,NULL,NULL,NULL,'RECREATE_FORCE',-1,'SELECT * FROM LOCKS ORDER BY EVENT_TIMESTAMP')"

这个存储过程的结果是一组关系表，以下是这几张表的含义。

LOCK_EVENT：表对应发生的锁事件，每个事件对应一条记录

LOCK_PARTICIPANTS：表标识锁事件的参与者，每个参与的应用程序对应一行记录

LOCK_PARTICIPANT_ACTIVITIES：表包含了参与事件的应用程序曾今和当前正在执行的语句。

LOCK_ACTIVITY_VALUES：存放SQL语句中参数的值

LOCKWAITEVM：作用不明确

可通过SQL语句对锁相关事件进行查询(完全可找出引起死锁、锁超时、锁等待的SQL语句)：

select substr(lp.xmlid,1,64),lp.participant_no,lp.participant_type,lp.application_handle,lp.participant_no_holding_lk,lpa.activity_id,lpa.activity_type,varchar(lpa.stmt_text,50) as statement from LOCK_PARTICIPANTS lp,LOCK_PARTICIPANT_ACTIVITIES lpa where lp.xmlid=lpa.xmlid and lp.participant_no=lpa.participant_no order by lp.xmlid desc,lp.participant_no,lpa.activity_id

--创建package cache事件监视器并激活

A、 创建并激活事件监视器

创建一个程序包高速缓存事件监视器是为了在语句从程序包高速缓存中被交换出去时将其捕获。

db2 "create event monitor switchstmts for package cache write to unformatted event table"

语句的信息写入到与事件监视器同名的表中。

db2 set event monitor switchstmts state 1

B、 收集信息并停止事件监视器

db2 set event monitor switchstmts state 0

C、 提取表SWITCHSTMTS中的二进制数据

利用如下存储过程，格式化二进制数据到如下表

PKGCACHE_EVENT

PKGCACHE_METRICS

db2 "call evmon_format_ue_to_tables('PKGCACHE',NULL,NULL,NULL,NULL,NULL,NULL,-1,'SELECT * FROM SWITCHSTMTS')"

PKGCACHE_EVENT包含最常用的监视元素及与捕获的每个事件相关联的度量值，

PKGCACHE_METRICS包含每个事件的详细度量值。

word版共享链接：https://pan.baidu.com/s/1geT4x5h

  供稿 | 黄海  编辑 | lin