db2 分区数据库详解

2022-08-18 17:08:30 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

本文主要介绍什么是 DB2 数据库分区，为什么采用数据库分区，并以 Balanced Warehouse E7100 为例介绍数据库分区管理的基本方法及应用实践。

DB2 数据库分区是 DB2 企业版 DPF(Data Partitioning Feature)选件提供的，它主要用来为大规模数据处理、高并发数据访问提供支持。DB2 数据库分区采用 Share-nothing 体系结构，数据库在一个非共享的环境中被分解为独立的分区，每个分区都具有自己的资源，例如内存，CPU 和磁盘以及自己的数据、索引、配置文件和事务日志。数据库分区有时称为节点或数据库节点。

数据通过 Hash 算法均允地散列到不同的分区内，每个分区只负责处理自己的数据。当用户发出 SQL 操作后，被连接的分区被称为 Coordinate Node，它负责处理用户的请求，并根据 Partition key 将用户的请求分解成多个子任务交由不同分区并行处理，最后将不同分区的执行结果经过汇总返回给用户，分区对应用来说是透明的。

在 DB2 中，数据库分区可以部署在集群或 MPP 环境下，也就是说数据库分区分布在不同的机器上；数据库分区也可以部署在同一台 SMP 机器上，在同一台机器上的分区我们称为逻辑分区。同时，我们还可以在集群或 MPP 环境下部署多个分区，在集群或 MPP 每一个节点上部署多个逻辑分区。

DB2 数据库分区提供了强大的可扩展能力。由于采用 Share-nothing 体系结构，每个分区(节点)只处理它那一部分数据，分区之间尽可能独立，这就减少了节点间共享资源的争用，允许数据库有效地伸缩以支持更大的数据规模及更多的用户访问。DB2 数据库分区提供 scale up (垂直扩展)及 scale out (水平扩展)能力。垂直扩展是通过增加机器的物理资源如 cpu、磁盘、内存来实现的；水平扩展是通过增加物理机器来实现的，DB2 中，最多可以支持 1000 个分区。在规划 DB2 数据库分区时，我们需要考虑是通过增加逻辑分区还是物理分区来实现扩展能力。如果一台物理机器上有多个 CPU，其物理资源可以允许多个分区共享该资源，我们可以通过增加逻辑分区来实现扩展；如果一台物理机器上的物理资源不能满足应用需求，我们就需要通过增加机器，也就是物理分区来实现扩展能力。

DB2 数据库分区还提供了强大的并行处理能力。首先，它提供了 inter-partition parallelism 分区间的并行机制，通过hash算法将数据库请求分成多个任务在不同的分区上并行执行，同时，提供了 intra-partition parallelism 分区内的并行机制，将任务分解成不同的子任务，在不同的 CPU 上并行执行，另外，我们还可以同时利用 inter-partition parallelism、intra-partition parallelism 来实现完全的并行处理能力。DB2 数据库的查询操作、backup/restore/load 等实用程序及 I/O 操作都可以通过上述的并行处理能力来显著提高其性能。

为什么采用数据库分区

采用数据库分区，可以为您带来如下好处：

  • 查询扩展性

这是采用数据库分区最主要的原因之一。将一个大的数据库分成多个小的数据库可以提高查询的性能，因为每个数据库分区拥有自己的一小部分数据。假设您想扫描1亿条记录，对一个单一分区的数据库来讲，该扫描操作需要数据库管理器独立扫描一亿条记录，如果您将数据库系统做成50个分区，并将这1亿条记录平均分配到这50个分区上，那么每个数据库分区的数据库管理器将只扫描200万记录。

  • 架构限制

在DB2 V8和以前版本，非分区数据库的最大的表取决于页面大小，4K页最大支持64 GB，32K页最大支持512 GB数据量。表和表空间大小限制是每个分区上的限制，因此将数据库分成N个分区可以将表的最大尺寸增加为单个分区表最大尺寸的N倍。内存也可能是个限制，特别是在32为操作系统环境，因为每个数据库分区管理并拥有自己的资源，因此通过数据库分区可以克服这个限制。

  • 数据库装载性能

数据库分区可以并行装载数据到所有数据库分区，极大减少单表的装载时间，这对于像实时商业智能系统那样对数据装载的时间要求特别高的系统特别重要。

  • 数据库维护性能

将数据库分散到多个数据库分区服务器可以加快系统维护，因为每个操作都运行在分区所管理的一个数据子集上面，这样可以通过数据库分区进一步减少创建索引的时间，减少搜集统计信息的时间，因为runstats仅运行在一个数据库分区上面，减少表重整(reorg)的时间。

  • 备份/恢复性能

将数据库分区到不同的数据库服务器上可以大大减少数据库备份的时间，这往往是决定是否使用数据库分区很重要的一点。DB2 通过为每个表空间分配独立的进程或线程来实现备份和恢复操作的并行处理的。在分区数据库环境的备份中，每个分区的备份是独立的，通过并行备份数据库分区可以大大减少备份整个数据库的时间。

  • 日志

在高度活动的系统中，数据库日志的性能可能会限制系统的整体吞吐量。在分区数据库环境中，每个分区有自己一套日志。当大量插入、更新、删除操作时，多个数据库分区可以提高性能，因为日志是在每个数据库分区上是并行写的，且每个单一的分区需要记录的日志更少。

DB2 随数据量或处理器和分区的增加，提供近线性的扩展能力，可是，数据库分区是否提供最多的益处依赖于处理的工作负荷、最大表的大小及其他因素。

回页首

什么时候采用数据库分区

设计数据库分区的基本原则是，尽量将大表分布在所有的分区上，提高并行处理能力；将小表放置在尽量少的分区上，一般是建议放在单一分区上；尽量减少分区间的通信。对于是否采用数据库分区，除了考虑上一节提到的分区的优势之外，我们也要根据分区设计原则来考虑：

  • 选择数据库分区的一个比较理想的场景是执行一条像 ” select count(*) from big_table”这样的语句。如果将这个表放在所有分区上，则每个分区都可以计算该表在其上的行数，并将这个局部总数（subtotal）发送到协调分区，以便计算总和，而这里的通信成本比起每个分区上所做的工作来可以忽略不计。
  • 另一个非常合适的场景是， 一个大表与几个非常小的很少更新的表相连接。大表是分区的，小表则被复制到每个分区上，这样就可以并置连接。
  • 不适合使用分区的是那些在连接时涉及很多大表和各种各样的表和列的 ad hoc 查询环境。在那些情况下， 很难或者不可能选择表的分区键，使得所有大的查询执行起来没有很多的分区间通信。
  • 同样不适合使用分区的是那些有多条不能在单个分区内处理的非常小的语句。在这种情况下，分区间通信的开销比起这些语句的本地执行来就相当高，而如果使用分区的话（尤其是跨多个物理系统），响应时间就会大大恶化。
  • 大多数工作负载和一些特定的任务都处于刚才讨论的这两种极端之间，这些地方都需要通过原型来研究使用分区所带来的影响。

回页首

数据库分区实现

下边，我们以 IBM InfoSphere Balanced Warehouse E7100 为例，介绍一下DB2 分区数据库在AIX下的基本管理方法及应用实践。DB2 分区数据库在 Windows 环境下的管理方法和 AIX 略有不同，具体请参阅相关手册。

IBM InfoSphere Balanced Warehouse 是IBM针对客户数据仓库系统提出的一整套完整的解决方案。当用户实施一个数据仓库系统时，对用户来说，一个非常大的挑战就是未来的数据仓库系统应该选择什么样的服务器，服务器的配置是什么，选择多少台服务器；选择什么样的存储设备，存储容量要多大，存储设备配置是什么；选择什么样的网络设备，它的配置是什么才能保证系统性能高效、稳定。同时，随着系统的应用，数据量会急剧增长，如何在保证系统性能的前提下，提供更好的系统扩展能力也是用户非常关心的问题。为了解决上述问题，IBM 结合自己多年实施客户数据仓库系统的经验，并协同IBM软件部门、服务器部门、存储部门及实验室，共同推出了 InfoSphere Balanced Warehouse 解决方案，有时也称为 BCU(Balanced Configuration Unit)。InfoSphere Balanced Warehouse 是一个包含服务器、存储、数据仓库软件在内的完整解决方案，它基于 IBM 最佳实践并得到充分验证，是一个预先配置好的、可立即使用的解决方案，客户无需靠猜测或假象去配置并验证，实现开箱即用。InfoSphere Balanced Warehouse 采用平衡的理念，每个组件（数据库、服务器和存储）提供均衡的性能确保整体方案性能最优。同时，它采用可扩展的模块化设计，数据仓库系统在整个生命周期中，可以以增量的方式进行扩展，达到的性能可预见、可度量。

InfoSphere Balanced Warehouse主要由以下几个模块组成：

Foundation Module: 有时也称为 administration BCU。该模块主要包括编目分区、协调分区以及单分区表。系统必须要有 1 个 Foundation Module。

Data Module: 有时也称为 data BCU。该模块主要保存分区表数据。根据数据量，可以有 1 个或多个 Data Module。

User Module: 如果系统有大量用户访问，我们可以考虑增加 User Module。

Failover Module: 用于满足 HA 的需求。

Application Module: 用于运行应用程序，比如说 ETL 应用就可以配置在 Application Module 上。

本次配置环境包括一个 administration BCU 和 2 个 data BCU

创建实例及配置通信连接

  1. 使用db2icrt命令创建实例
代码语言：javascript复制
/opt/IBM/db2/V9.1/instance/db2icrt -u bcufenc bcuaix
  1. 配置TCPIP通信服务
代码语言：javascript复制
  db2set DB2COMM=tcpip
  1. 修改DBM CFG 中的 SVCENAME参数
代码语言：javascript复制
db2 update dbm config using svcename xbcuaix
  1. 在实例级禁用fault monitor
代码语言：javascript复制
db2fm -i instance_name -f no
  1. 创建诊断文件目录

缺省的情况下，db2diag.log 文件创建在 ~/sqllib/db2dump 目录下，这个目录是 NFS-mounted，我们一般建议要将 db2diag.log 文件放在非 NFS-mounted 目录下。在 E7100 实施中，我们建议将该文件放到外部的存储上。

代码语言：javascript复制
Administration BCU: 
mkdir -p /db2path/bcuaix/NODE0000/SQL00001/db2dump 
Data BCU 1:
 mkdir -p /db2path/bcuaix/NODE0001/SQL00001/db2dump 
Data BCU 2:
 mkdir -p /db2path/bcuaix/NODE0009/SQL00001/db2dump

Administration BCU: 
ln -s /db2path/bcuaix/NODE0000/SQL00001/db2dump /db2path/bcuaix/db2dump  
Data BCU 1:
 ln -s /db2path/bcuaix/NODE0001/SQL00001/db2dump /db2path/bcuaix/db2dump 
Data BCU 2: 
ln -s /db2path/bcuaix/NODE0009/SQL00001/db2dump /db2path/bcuaix/db2dump

db2 update dbm config using diagpath /db2path/bcuaix/db2dump

定义数据库分区

在数据库分区环境下，数据库被分为多个分区，分区之间彼此独立工作，实现并行操作。数据库分区可以是物理分区也可以是逻辑分区。在一台物理机器上部署的一个分区，我们称为物理分区，如果是在一台 SMP 机器上部署多个分区，这些分区我们称为逻辑分区。我们可以选择物理分区，也可以选择逻辑分区。通常，如果决定采用大的 SMP 机器，有更多的 CPU、内存及硬盘，我们会采用逻辑分区；如果决定采用多台物理机器，我们会通过非共享的体系结构采用物理分区；如果决定采用多台 SMP 机器，我们则会采用物理分区和逻辑分区结合的方式。

在 DB2 数据库分区环境中，执行 CREATE DATABASE 语句所在的分区称为编目分区(catalog partition)。编目分区保存系统编目表。编目分区只能创建在一个分区上。通常，在实际生产环境中，我们建议采用一个专用编目分区，这个分区只包含编目表，不包含用户数据。这对 DB2 的一些实用程序运行效率有较大的提高。比如说 BACKUP 和 RESTORE 命令，需要先在编目分区上运行，之后才能在其他分区上执行。由于编目分区上没有用户数据，因此它的备份和恢复就可以很快完成，并且可以最小程度地延迟对其他分区的（并行）操作的开始。

在 DB2 数据库分区环境中，应用程序连接的分区，我们称为协调分区(coordinate partition)。它负责处理用户的请求，并根据 Partition key 将用户的请求分解成多个子任务交由不同分区并行处理，最后将不同分区的执行结果经过汇总返回给用户。任何一个数据库分区都可以是协调分区。在实际生产环境中，我们建议采用一个或几个专用协调分区。因为应用程序要通过一个或多个协调分区为用户连接转移大量的数据的话，那么就会消耗那些分区上的大部分 CPU，并降慢了数据访问速度。如果让分区什么也不做，只是充当协调者（coordinator），就不会降低数据分区数据访问速度。

在 InfoSphere Balanced Warehouse E7100 的设计中，我们在 administration BCU 中，分别为编目分区和协调分区分配了专用的分区，同时，根据数据库分区的基本原则，我们将系统中的小表创建在了一个单一分区上。用户的数据，我们创建在 data BCU 上，同时，根据数据库分区的基本原则，我们将系统中的大表尽量地分布到 data BCU 上的所有分区上。当用户数据增加后，我们可以通过增加更多的 data BCU 来实现增量的方式扩展、提供均衡的性能。如下说明：

代码语言：javascript复制
Database partition 0 (BPU 0) 包含：
 Catalog function (only one database partition has the database catalog) 
 Coordinator function 
 Single-partition data function 
 Query Patroller server and control tables (if implemented) 
 Located on the administration BCU

Database partition 1 - n (BPU1 - BPUn) 包含：
Database partitions with partitioned data 
Located on the data BCUs

DB2 节点配置文件(db2nodes.cfg)

用来定义数据库分区。在创建分区数据库之前，一定要先定义 db2nodes.cfg 文件。该文件放置在用户实例主目录下。系统中的每一个分区在该文件中都会有一项。

db2nodes.cfg 文件的基本格式如下：

代码语言：javascript复制
dbpartitionnum hostname logical-port netnam

其中：

代码语言：javascript复制
dbpartitionnum

数据库分区号唯一地定义数据库分区，可在 0 到 999 之间。数据库分区号必须以升序顺序排序。该顺序中可以有间隔。一旦指定了数据库分区号，就不能对其进行更改。否则，分布图（它指定数据分发方式）中的信息可能不正确。

代码语言：javascript复制
hostname

用作分区间通信的 IP 地址的主机名。

代码语言：javascript复制
logical-port

它指定该数据库分区的逻辑端口号。此号码与数据库管理器实例名一起用来标识 etc/services 文件中的 TCP/IP 服务名称条目。 对于每个主机名，一个逻辑端口必须为 0（零） 。

代码语言：javascript复制
netname

指定用于 FCM 高速互联的主机名称 。

下边是包括一个 administration BCU 和 2 个 data BCU 环境的 db2nodes.cfg 文件内容：

代码语言：javascript复制
0 adminbcu001 0 adminbcu001_fcm 
1 databcu001 0 databcu001_fcm 
2 databcu001 1 databcu001_fcm 
3 databcu001 2 databcu001_fcm 
4 databcu001 3 databcu001_fcm 
5 databcu001 4 databcu001_fcm 
6 databcu001 5 databcu001_fcm 
7 databcu001 6 databcu001_fcm 
8 databcu001 7 databcu001_fcm 
9 databcu002 0 databcu002_fcm 
10 databcu002 1 databcu002_fcm 
11 databcu002 2 databcu002_fcm 
12 databcu002 3 databcu002_fcm 
13 databcu002 4 databcu002_fcm 
14 databcu002 5 databcu002_fcm 
15 databcu002 6 databcu002_fcm 
16 databcu002 7 databcu002_fcm

在分区号的分配上，我们建议，catalog partition 分区号分配为 0，因为一个实例下只能有 1 个 catalog partition，分区号 990-999 分配给另外需要增加的 coordinator partitions，分区号 980-989 分配给另外需要增加的单分区的表。

配置分区间通信

在 DB2 数据库分区环境中，分区之间需要通过 DB2 Fast Communication

Manager 进行通信。在 /etc/services 文件中，需要为 DB2 FCM 通信设置相应的通信端口。

代码语言：javascript复制
xbcuaix 50000/tcp 
xbcuaix_int 50001/tcp 
DB2_bcuaix 60000/tcp 
DB2_bcuaix_END 60016/tcp

创建数据库

我们在 administration BCU 上创建数据库testdb。

代码语言：javascript复制
db2 "create database testdb automatic storage no on /db2path  
pagesize 16384 autoconfigure apply none"

创建数据库分区组(database partition groups)

数据库分区组是一个或多个数据库分区的集合。在 DB2 数据库分区环境中，数据库表空间创建在数据库分区组中。

在设计数据库分区组时，我们一般建议：

  • 几乎总要为小的表创建至少一个单分区的数据库分区组。
  • 几乎总要为大的表使用至少一个由所有分区组成的数据库分区组。这个数据库分区组可以是缺省的 IBMDEFAULTGROUP。
  • 分区数越多，就越可能存在一些对单分区来说太大、而要展开到所有分区上又太小的表，那么就越需要创建包含数个分区、但不是全部分区的数据库分区组。

当我们创建一个数据库后，系统会缺省创建 3 个数据库分区组：

IBMCATGROUP：编目数据库分区组，用来存储系统编目表。它只包含一个数据库分区。

SYSCATSPACE 表空间创建在这个分区组中。

在 BCU 设计中，IBMCATGROUP 创建在 0 号数据库分区上。

IBMTEMPGROUP.：临时数据库分区组，tempspace1 系统临时表空间创建在这个分区组中。它包含系统中的所有数据库分区。

IBMDEFAULTGROUP：缺省数据库分区组。用户表空间缺省创建在该分区组中。USERSPACE1 表空间包含在 IBMDEFAULTGROUP 中。

在BCU设计中，建议不使用IBMDEFAULTGROUP，而是创建了2个新的数据库分区组：

PDPG： 分布在data BCU分区上的数据包含在此数据库分区组中。PDPG 只包括data BCU 上的分区，但不包含administration BCU上的分区。它适用于中等数据规模到大数据规模的表。

SDPG：该数据库分区只包含 administration BCU 分区上的数据，它只包含一个数据库分区，即 0 号数据库分区。系统中的一些小表保存在此数据库分区组中，这些小表通常是一些维表(dimension tables)或 lookup tables。

代码语言：javascript复制
CREATE DATABASE PARTITION GROUP PDPG ON DBPARTITIONNUMS  (1 to 16)
CREATE DATABASE PARTITION GROUP SDPG ON DBPARTITIONNUMS (0)

创建 buffer pools

在本示例中，我们创建 2 个 16K 页的 buffer pools：

代码语言：javascript复制
CREATE BUFFERPOOL BP_16K ALL DBPARTITIONNUMS SIZE 53760 PAGESIZE 16K; 
CREATE BUFFERPOOL BPTMP_16K ALL DBPARTITIONNUMS SIZE 10752 PAGESIZE 16K;

创建表空间

在本次实例中，我们将创建如下表空间：

db2tmp —临时表空间

ts_pd_data_001—分区表数据空间

ts_pd_idx_001—索引表空间

ts_sd_small—单分区表空间。

在 data BCU 上创建如下表空间：

代码语言：javascript复制
CREATE TEMPORARY TABLESPACE db2tmp 
IN DATABASE PARTITION GROUP ibmtempgroup 
PAGESIZE 16K 
MANAGED BY DATABASE 
USING ( 
FILE ’/db2fs1p $N /bcuaix/databasename/db2tmp’ 25G) 
ON DBPARTITIONNUMS (0) 
USING ( 
FILE ’/db2fs1p $N /bcuaix/databasename/db2tmp’ 25G, 
FILE ’/db2fs2p $N /bcuaix/databasename/db2tmp’ 25G, 
FILE ’/db2fs3p $N /bcuaix/databasename/db2tmp’ 25G, 
FILE ’/db2fs4p $N /bcuaix/databasename/db2tmp’ 25G) 
ON DBPARTITIONNUMS (1 to 16) 
EXTENTSIZE 16 
PREFETCHSIZE AUTOMATIC 
BUFFERPOOL BPTMP_16K 
OVERHEAD 5.75 TRANSFERRATE 0.4 
AUTORESIZE YES MAXSIZE 400G 
NO FILE SYSTEM CACHING;

CREATE TABLESPACE ts_pd_data_001 
IN DATABASE PARTITION GROUP pdpg 
PAGESIZE 16K 
MANAGED BY DATABASE 
USING ( 
FILE ’/db2fs1p $N /bcuaix/databasename/ts_pd_data_001’ 50G, 
FILE ’/db2fs2p $N /bcuaix/databasename/ts_pd_data_001’ 50G, 
FILE ’/db2fs3p $N /bcuaix/databasename/ts_pd_data_001’ 50G, 
FILE ’/db2fs4p $N /bcuaix/databasename/ts_pd_data_001’ 50G) 
EXTENTSIZE 16 
PREFETCHSIZE AUTOMATIC 
BUFFERPOOL BP_16K 
OVERHEAD 5.75 TRANSFERRATE 0.4 
AUTORESIZE YES MAXSIZE 400G 
NO FILE SYSTEM CACHING; 

CREATE TABLESPACE ts_pd_idx_001 
IN DATABASE PARTITION GROUP pdpg 
PAGESIZE 16K 
MANAGED BY DATABASE 
USING ( 
FILE ’/db2fs1p $N /bcuaix/databasename/ts_pd_idx_001’ 25G, 
FILE ’/db2fs2p $N /bcuaix/databasename/ts_pd_idx_001’ 25G, 
FILE ’/db2f3p $N /bcuaix/databasename/ts_pd_idx_001’ 25G, 
FILE ’/db2fs4p $N /bcuaix/databasename/ts_pd_idx_001’ 25G) 
EXTENTSIZE 16 
PREFETCHSIZE AUTOMATIC 
BUFFERPOOL BP_16K 
OVERHEAD 5.75 TRANSFERRATE 0.4 
AUTORESIZE YES MAXSIZE 200G 
NO FILE SYSTEM CACHING;

在administration BCU上创建如下表空间：

代码语言：javascript复制
CREATE TABLESPACE ts_sd_small_001 
IN DATABASE PARTITION GROUP sdpg 
PAGESIZE 16K 
MANAGED BY DATABASE 
USING ( 
FILE ‘/db2fs1p0/bcuaix/databasename/ts_sd_data_001’ 100G) 
EXTENTSIZE 16 
PREFETCHSIZE AUTOMATIC
BUFFERPOOL BP_16K 
OVERHEAD 5.75 TRANSFERRATE 0.4 
AUTORESIZE YES MAXSIZE 200G 
NO FILE SYSTEM CACHING;

在创建分区数据库表空间时，我们经常会使用数据库分区表达式。它是由参数 ' N （注意在 N 之前有一个空格）来指定的，DB2 会将

创建表

当创建数据库分区组时，每一个数据库分区组都会对应一个分区图（partitioning map），它是一个包含 4096 个条目的数组，每个条目的值对应于数据库分区组中的某一个分区号。

分区键（partitioning key）是由一个表上的一个列或者多个列组成，用于确定某一行特定数据分布在哪个分区上。分区键是在 CREATE TABLE 语句来定义的。如果没有指定分区键，缺省的分区键是主键的第一列，如果没有这么一列，则选择有适合数据类型的第一列。

当向表中插入一条记录时，DB2 将该记录的分区键值散列（hash）到分区图中的一个条目上，并根据该条目找到要使用的分区号。

在定义分区表时，分区键的定义对今后性能的影响非常大，因此，在选择上一定要慎重。通常，在选择分区键时，要遵从如下原则：

  • 选择经常用于连接的列作为分区键。
  • 分区键应该不包括经常更新的列。
  • 除非一个表不是很重要，或者不知道一个好的分区键选择是什么，否则不应该随缺省情况选择分区键。缺省的分区键是主键的第一列，如果没有这么一列，则选择有适合数据类型的第一列。
  • 将一个表创建为分区表之后，就不能直接更改它的分区键。
  • 通过 ALTER TABLE 可以添加或删除分区键，但是这只对未分区表有效。
  • 那些处于表上定义的惟一性约束或主键约束中的列必须是分区键的一个超集（superset）
  • 数据类型：LOB 和 LONG 型的列不能作为分区键的一部分
  • 就效率而言，整数类型的列是最可取的，其次是字符型，然后是小数。
  • 选择基数较大的分区键列，以避免表中的行在各分区上分布不均衡。

在 DB2 数据库分区环境下，数据在不同分区的分布会影响表的连接策略。分区数据库环境下表连接策略主要包括：

并置连接(Collocated joins)--采用该种连接方式，表的连接以本地方式在数据所在的数据库分区上进行，不会在分区之间传输数据，这是效率最高的表连接方式。在分区数据环境下，应尽量采用该种连接方式。

定向连接（Directed joins）--采用该种连接方式，一个表中的数据会按照连接对中的另一个表的分区键值重新分发到其他分区上来完成表连接操作。它会在分区之间移动数据，对性能会有一定影响。当并置连接及未被采用，DB2优化器会选择定向连接方式。

广播连接(Broadcast joins)--采用该种连接方式，一个表中的所有数据会广播到另外表所在的所有分区上来完成表连接操作。如果在分区之间广播的数据量较大，对性能影响也会很大。当并置连接及定向表连接未被采用，DB2 优化器会选择广播连接方式。

在 DB2 数据库分区环境下，应尽量采用并置连接方式。要使用并置连接方式，被并置的表必须：

  • 在相同的数据库分区组内
  • 分区键必须有相同数量的列。
  • 分区键中相应的列必须是分区兼容的。

分区兼容性是在分区键中相应列的基本数据类型之间定义的。分区兼容的（partition-compatible）数据类型有一个特性，那就是对于两种不同类型的两个变量，假设变量有相同的值，则它们将通过相同的分区函数映射到相同的分区键索引。分区兼容性有以下特征：

  • 内部格式用于 DATE、TIME 和 TIMESTAMP。这些类型彼此不兼容，并且没有哪一个与 CHAR 或 VARCHAR 兼容。
  • 分区兼容性不受具有 NOT NULL 或 FOR BIT DATA 定义的列的影响。
  • 对于兼容数据类型的 NULL 值是一致处理的。而不兼容数据类型的 NULL 值可能产生不同的结果。
  • 可以使用 UDT 的基本数据类型来分析分区兼容性。
  • 分区键中具有相同值的小数是一致处理的，即使它们的标度（scale）和精度（precision）不一样也是如此。
  • 系统提供的散列函数将忽略字符串（CHAR、VARCHAR、GRAPHIC 或 VARGRAPHIC）的结尾空白。
  • 不同长度的 CHAR 或 VARCHAR 是兼容的数据类型。
  • 相等的 REAL 或 DOUBLE 值，即使它们的精度不同，也将被一致处理。

另外，我们也经常使用复制的具体化查询表（replicated MQT）来实现并置连接。我们往往会选择更新不多而又经常与大表进行连接的小表或中等大小的表来作为复制的具体化查询表。

下面是对复制表的一个示例定义：

代码语言：javascript复制
create table t1_rep as (select * from t1) data initially deferred   
refresh deferred in ts_pd_data_001 replicated

下边的例子，我们在 ts_pd_data_001 表空间上创建一个 LINEITEM 表：

代码语言：javascript复制
CREATE TABLE "DB2INST1"."LINEITEM" (
"L_ORDERKEY" INTEGER NOT NULL ,
"L_PARTKEY" INTEGER NOT NULL ,
"L_SUPPKEY" INTEGER NOT NULL ,
"L_LINENUMBER" INTEGER NOT NULL ,
"L_QUANTITY" DECIMAL(15,2) NOT NULL ,
"L_EXTENDEDPRICE" DECIMAL(15,2) NOT NULL ,
"L_DISCOUNT" DECIMAL(15,2) NOT NULL ,
"L_TAX" DECIMAL(15,2) NOT NULL ,
"L_RETURNFLAG" CHAR(1) NOT NULL ,
"L_LINESTATUS" CHAR(1) NOT NULL ,
"L_SHIPDATE" DATE NOT NULL ,
"L_COMMITDATE" DATE NOT NULL ,
"L_RECEIPTDATE" DATE NOT NULL ,
"L_SHIPINSTRUCT" CHAR(25) NOT NULL ,
"L_SHIPMODE" CHAR(10) NOT NULL ,
"L_COMMENT" VARCHAR(44) NOT NULL )
DISTRIBUTE BY HASH("L_ORDERKEY")
IN " ts_pd_data_001"

回页首

数据库分区管理相关命令

确定编目分区

在 DB2 数据库分区环境中， ROLLFORWARD DATABASE 等命令需要在编目分区上执行。我们可以通过 LIST DATABASE DIRECTORY 命令来确定编目分区。

代码语言：javascript复制
db2 LIST DATABASE DIRECTORY

分区切换

在 DB2 数据库分区环境中，我们可以通过设置 DB2NODE 环境变量 或使用 set client 命令进行分区间的切换。

代码语言：javascript复制
export DB2NODE=2
db2 terminate
 OR 
db2 set client connect_dbpartitionnum 2

增加数据库分区

代码语言：javascript复制
db2start DBPARTITIONNUM 4 ADD DBPARTITIONNUM HOSTNAME Clyde PORT 4

该命令会自动在 db2nodes.cfg 中增加新定义的分区信息，并在新的数据库分区上创建

TEMPSPACE1 表空间。

我们也可以用如下命令增加数据库分区：

代码语言：javascript复制
export DB2NODE=4
db2 terminate
db2 connect to testdb
db2 ADD DBPARTITIONNUM

使用该命令时，必须事先修改 db2nodes.cfg 文件包含新定义的分区信息，并在新增加的数据库分区上执行该命令。

删除数据库分区

在删除数据库分区时，只有那些不包含数据的分区才可以被删除。因此，在删除分区时，必须要先执行 DROP PARTITIONNUM VERIFY 命令检查一下该分区是否可以被删除。如果某一个分区正在被使用，要先执行 REDISTRIBUTE DATABASE PARTITION GROUP 命令将该分区上的数据分布到其他分区上。

代码语言：javascript复制
export DB2NODE=4
db2 TERMINATE
db2 DROP DBPARTITIONNUM VERIFY
SQL6034W Node "4" is not being used by any databases.
db2stop DROP DBPARTITIONNUM 4

如果数据库分区 4 上有数据，则执行下述命令：

代码语言：javascript复制
redistribute database partition group pg123  uniform drop dbpartitionnum (4)
db2 for application all
export DB2NODE=4
db2 drop dbpartitionnum verify
db2stop drop dbpartitionnum 4

查看数据库分区组

代码语言：javascript复制
db2 LIST DATABASE PARTITION GROUPS SHOW DETAIL

重新分布数据库分区组数据

代码语言：javascript复制
db2 "REDISTRIBUTE DATABASE PARTITION GROUP pg123 UNIFORM" 
or
db2 "REDISTRIBUTE DATABASE PARTITION GROUP pg123 using TARGETMAP pg123.map"

增加数据库分区到数据库分区组中

代码语言：javascript复制
db2 "ALTER DATABASE PARTITION GROUP pg123 ADD DBPARTITIONNUMS (4) WITHOUT TABLESPACES"
db2 "REDISTRIBUTE DATABASE PARTITION GROUP pg123 UNIFORM"

从数据库分区组中删除数据库分区

代码语言：javascript复制
db2 drop dbpartitionnum verify
SQL6034W Node "4" is not being used by any databases.
db2stop DROP DBPARTITIONNUM 4

删除数据库分区组

代码语言：javascript复制
db2 "DROP DATABASE PARTITION GROUP pg123"

查看表中的数据在各分区的分布情况

代码语言：javascript复制
SELECT DBPARTITIONNUM(distribution key), COUNT( * )
FROM schema.table
GROUP BY DBPARTITIONNUM(distribution key)
ORDER BY DBPARTITIONNUM(distribution key)

查看表中的数据在分区图（partition map）中的分布情况

代码语言：javascript复制
SELECT HASHEDVALUE(distribution key), COUNT( * )
FROM schema.table
GROUP BY HASHEDVALUE(distribution key)
ORDER BY HASHEDVALUE(distribution key)

回页首

DB2 分区数据库相关实用程序

db2_all 命令

在 DB2 分区环境下，很多操作都需要在各个分区上分别执行，如果每次都要到各个分区上单独执行，对用户来说非常繁琐。在 DB2 中，可以使用 db2_all 命令，它可以在指定的所有数据库分区服务器上运行该命令。

代码语言：javascript复制
db2_all "db2 UPDATE DB CFG FOR TESTDB USING LOGRETAIN ON"
db2_all ";db2 UPDATE DB CFG FOR TESTDB USING LOGRETAIN ON"
db2_all "db2 CONNECT TO testdb ;db2 LIST TABLESPACES SHOW DETAIL"

rah 命令

它指定在所有计算机上运行该命令。

如果想为多台物理机器创建一个目录，那么可以发出下面的命令：

代码语言：javascript复制
 rah ")mkdir /tmp/$USER“

数据库备份

要备份分区数据库，您必须要首先在编目分区上调用备份实用程序，然后在其他数据库分区上调用备份实用程序。

代码语言：javascript复制
db2_all "<< 0< db2 BACKUP DB testdb to /home/db2inst1/BACKUPS"
db2_all "|<<-0< db2 BACKUP DB testdb ONLINE to /home/db2inst1/BACKUPS"

其中，“ 0”表示只在 0 号分区上执行，“-0”表示在除了 0 号分区之外的所有分区上执行。

在版本 9.5 之前，您必须一次一个数据库分区地备份分区数据库。一次一个数据库分区地备份多个数据库分区可能会出错并且费时。如果一次一个数据库分区地备份分区数据库，那么您无法在备份映像中包括复原和恢复所需要的日志文件。在版本 9.5 中，您可以通过在 b 编目数据库分区上执行单一系统视图（SSV）备份同时备份多个数据库分区。您从编目数据库分区执行备份操作时，可以使用 ON DBPARTITIONNUMS 选项来指定要在备份中包含哪些分区。它将同时备份指定的分区，并且与指定的分区相关联的备份时间戳记将相同。此外，您还可以在 SSV 备份中包含数据库日志。

代码语言：javascript复制
db2 BACKUP DATABASE testdb ON DBPARTITIONNUMS (1, 2)  
 TABLESPACE (USERSPACE1)  ONLINE USE TSM
Or
db2 backup db testdb on all dbpartitionnums tablespace(T1)

监控数据库备份命令执行情况

代码语言：javascript复制
export DB2NODE=0
db2 terminate
db2 LIST UTILITIES SHOW DETAIL

数据库恢复

要恢复分区数据库，您必须要首先在编目分区上调用恢复实用程序，然后在其他数据库分区上调用恢复实用程序。

代码语言：javascript复制
db2_all "<< 0< db2 RESTORE DATABASE testdb   
 FROM /home/db2inst1/BACKUPS INTO testdb REPLACE EXISTING"
db2_all "<<-0< db2 RESTORE DATABASE testdb   
  FROM /home/db2inst1/BACKUPS INTO testdb REPLACE EXISTING"

前滚恢复(ROLLFORWARD DATABASE)

在分区数据库中，ROLLFORWARD DATABASE 命令只能在编目分区上运行。如果需要前滚恢复数据库或表空间到某一时间点（point in time ）,则该命令会涉及到db2nodes.cfg 文件中定义的所有数据库分区上的数据。如果需要根据整个事务日志(to the end of logs)来前滚恢复数据库或表空间, 则该命令会涉及到所有指定的数据库分区上的数据。如果没有指定数据库分区，该命令会涉及到db2nodes.cfg 文件中定义的所有数据库分区上的数据。如果某一分区不需要执行前滚恢复，则该分区会被忽略掉。

当数据库处于一致状态时（当数据库目录中列示的对象与磁盘中实际存在的对象匹配时），最小恢复时间是前滚期间的最早时间点。手动确定要将数据库前滚至的正确时间点比较困难，尤其是对于分区数据库更是如此。在版本 9.5 中，通过在 ROLLFORWARD DATABASE 命令中使用 TO END OF BACKUP 参数，可以将数据库前滚至由数据库管理器确定的最小恢复时间。

前滚恢复分区 0 及分区 2 上的表空间TBS1：

代码语言：javascript复制
db2 rollforward db testdb to end of logs on dbpartitionnums (0, 2) tablespace(TBS1)

前滚恢复分区 6 上的 6 个小表：

代码语言：javascript复制
db2 rollforward database testdb to end of logs on dbpartitionnum (6)  
           tablespace(tsstore, tssbuyer, tsstime, tsswhse, tsslscat, tssvendor)

前滚至由数据库管理器确定的最小恢复时间：

代码语言：javascript复制
db2 rollforward db testdb to end of backup and complete

EXPORT 命令

export 用于将表中的数据卸载到文件中。

代码语言：javascript复制
db2 "EXPORT TO lineitem.del OF DEL SELECT * FROM db2inst1.lineitem"

在分区数据库环境下，import 或 LOAD 命令不支持 IFX 文件格式。

IMPORT 命令

用于将外部文件中的数据插入到表中。

代码语言：javascript复制
db2 "IMPORT FROM lineitem.tbl OF DEL MODIFIED BY COLDEL|   
       COMMITCOUNT 100000 REPLACE INTO lineitem"

LOAD 命令

在多分区数据库环境中，大量的数据放在多个数据库分区中。分区键用来确定每部分数据所在的数据库分区。必须先分布数据，然后才能将该数据装入到正确的数据库分区中。

在多分区数据库中装入表时，load 实用程序可以：

  • 并行地分布输入数据
  • 同时在各个相应数据库分区中装入数据

将数据装入到多分区数据库中分两阶段完成：第一阶段为设置阶段，在此阶段获取数据库分区资源（如表锁定）；第二阶段为装入阶段，在此阶段将数据装入到数据库分区中。在将数据装入多分区数据库时，可以使用下列其中一种方式：

PARTITION_AND_LOAD

对数据进行分布（有可能以并行方式进行分布），并且同时在各个相应数据库分区上装入数据。

PARTITION_ONLY

对数据进行分布（有可能以并行方式进行分布），并将输出写入每个装入数据库分区上指定位置中的文件。每个文件都包含分区头，该分区头指定数据在数据库分区上的分布方式，并指定可以使用 LOAD_ONLY 方式将该文件装入到数据库中。

LOAD_ONLY

假定数据已分布在数据库分区上；将跳过分布过程，并且在相应的数据库分区上同时装入数据。

ANALYZE

生成最佳分布图（在所有数据库分区之间均匀地分布数据）。

下边是 LOAD 命令的一些示例：

要将 load.del 中的数据装入到所有定义了 TABLE1 的数据库分区中，请发出以下命令：

代码语言：javascript复制
  LOAD FROM LOAD.DEL of DEL REPLACE INTO TABLE1

要在数据分布在数据库分区 3 和数据库分区 4 上的位置执行装入操作，请发出以下命令：

代码语言：javascript复制
LOAD FROM LOAD.DEL of DEL REPLACE INTO TABLE1 PARTITIONED DB 
    CONFIG PARTITIONING_DBPARTNUMS (3,4)

在使用数据库分区 3 和数据库分区 4 的情况下，要将 load.del 分布（而不装入）到所有定义 TABLE1 的数据库分区中，请发出以下命令：

代码语言：javascript复制
LOAD FROM LOAD.DEL of DEL REPLACE INTO TABLE1 PARTITIONED DB
 CONFIG MODE PARTITION_ONLY  
PART_FILE_LOCATION /db2/data PARTITIONING_DBPARTNUMS (3,4)

如果已经以 PARTITION_ONLY 方式执行了装入操作，并且要将每个装入数据库分区的 /db2/data 目录中的分区文件装入到所有定义了 TABLE1 的数据库分区中，请发出以下命令：

代码语言：javascript复制
LOAD FROM LOAD.DEL OF DEL REPLACE INTO TABLE1 PARTITIONED 
    DB CONFIG MODE LOAD_ONLY 
PART_FILE_LOCATION /db2/data

要仅装入到数据库分区 4 中，请发出以下命令：

代码语言：javascript复制
LOAD FROM LOAD.DEL OF DEL REPLACE INTO TABLE1 PARTITIONED 
        DB CONFIG MODE LOAD_ONLY                                               
PART_FILE_LOCATION /db2/data OUTPUT_DBPARTNUMS (4)

REORG 命令

在分区数据库中，同样使用DB2 REORG 命令重组表及索引。

代码语言：javascript复制
db2 "REORG INDEXES ALL FOR TABLE lineitem ALLOW WRITE ACCESS"
db2 "REORG TABLE lineitem INPLACE ALLOW WRITE ACCESS ON ALL DBPARTITIONNUMS"

RUNSTATS 命令

在分区数据库中，同样使用RUNSTATS命令收集统计信息。

代码语言：javascript复制
db2 "RUNSTATS ON TABLE db2inst1.lineitem WITH DISTRIBUTION AND  
          DETAILED INDEXES ALL ALLOW WRITE ACCESS"

在分区数据库中，RUNSTATS 命令运行时所在分区的统计信息才会被收集，再对这些统计信息加以推断（前提是行在各分区上是均匀分布的）以反映整个数据库。这意味着，SYSCAT.TABLES 中的 CARD 列可能不包含该表中确切的行数。我们要尽量让数据均匀地分布在各分区上。

回页首

结论

本文以 Balanced Warehouse E7100 为例，为大家介绍了数据库分区设计、实现及管理的基本方法。希望大家能够对 DB2 数据库分区技术及使用有一个比较全面的了解。另外，关于数据库分区监控及性能调优等方面内容，大家可以参考 DB2 信息中心相关内容。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/134921.html原文链接：https://javaforall.cn

数据库管理 数据库 sql

0 人点赞