多分区数据库基础概念

1.Catalog Partition
when you create a database, several table spaces are created by default.One of them, the catalog table space SYSCATSPACE, contains the DB2 system catalogs. In a partitioned environment, SYSCATSPACE is not partitioned but resides on one partition known as the catalog partition. The partition from which the CREATE DATABASE command is issued becomes the catalog partition for the new database. All access to system tables must go through this catalog partition. Figure 2.11 shows SYSCATSPACE residing on server Linux1, so the CREATE DATABASE command was issued from this server.For an existing database, you can determine which partition is the catalog partition by issuing the command list db directory. The output of this command has the field Catalog data-base partition number for each of the entries, which indicates the catalog partition number for that database.
编目分区：
当创建一个数据库，默认会创建几个表空间。其中编目表空间syscatspace包含DB2系统编目。在分区数据库环境中，syscatspace不会被分区而只存在于其中一个称为编目分区的分区中，这个编目分区也就是创建新数据库发出create database命令的分区。所有访问系统表都必须通过这个编目分区。可以通过命令db2 list db directory命令来查看哪个为编目分区。Catalog database partition number的值则为数据库份额编目分区号。

2.Partition Groups
A partition group is a logical layer that provides for the grouping of one or more database partitions. A database partition can belong to more than one partition group. When a database is created, DB2 creates three default partition groups, and these partition groups cannot be dropped.
• IBMDEFAULTGROUP: This is the default partition group for any table you create. It contains all database partitions defined in the db2nodes.cfg file. This partition group cannot be modified. Table space USERSPACE1 is created in this partition group.
• IBMTEMPGROUP: This partition group is used by all system temporary tables. It contains all database partitions defined in the db2nodes.cfg file. Table space TEMPSPACE1 is created in this partition group.
• IBMCATGROUP: This partition group contains the catalog tables (table space SYSCATSPACE). It only includes the database’s catalog partition. This partition group cannot be modified.
To create new database partition groups, use the CREATE DATABASE PARTITION GROUP statement. This statement creates the database partition group within the database, assigns database partitions that you specified to the partition group, and records the partition group definition in the database system catalog tables.
分区组：
分区组是一个逻辑层，为一个或多个分区提供分组。一个数据库分区可以属于一个或多个分区组。当数据库被创建，DB2创建3个默认的分区组，而这些默认的分区组不能被删除：
ibmdefaultgroup:创建的任何表的默认分区组。包含db2nodes.cfg文件中所有数据库分区。这个分区组可以被修改，增加或删除数据库分区。userspace1表空间创建在这个分区组中。
ibmtempgroup:这个分区组被所有系统临时表使用。它包含db2nodes.cfg文件中所有数据库分区。userspace1表空间创建在这个分区组中。
ibmcatgroup:这个分区组包含编目表，它只包含数据库的编目分区。这个分区组不能被修改。
使用create database partition命令可以创建新的数据库分区组。这个命令在数据库中创建分区组，将指定的分区组成一个组，并且在数据库编目表中记录分区组的定义。
db2 "create database partition group pgrpall on all dbpartitionnums"
db2 "create database partition group pgrp23 on dbpartitionnum(2,3)"
db2 "create database partition group pgrp123 on dbpartitionnum(1 to 3)"
• ALTER DATABASE PARTITION GROUP (增加或删除分区语句)
alter database partition group pgall add dbpartitionnum (3)
alter database partition group pgall drop dbpartitionnum (1)
• DROP DATABASE PARTITION GROUP (增加或删除分区语句)
• LIST DATABASE PARTITION GROUPS  show detail(列出所有分区组，ibmtempgroup不会被列出)

3.Buffer Pools in a DPF Environment
Figure 2.11 shows buffer pools defined across all of the database partitions. Interpreting this figure for buffer pools is different than for the other objects, because the data cached in the buffer pools is not partitioned as the figure implies. Each buffer pool in a DPF environment holds data only from the database partition where the buffer pool is located. You can create a buffer pool in a partition group using the CREATE BUFFERPOOL statement with the DATABASE PARTITION GROUP clause. This means that you have the flexibility to define the buffer pool on the specific partitions defined in the partition group. In addition, the size of the buffer pool on each partition in the partition group can be different. The following statement will create buffer pool bpool_1 in partition group pg234, which consists of partitions 2, 3, and 4.
缓存在缓冲池中的数据不会被分区。在DPF环境中的每个缓冲池只会缓存来自缓冲池所在分区的数据。可以使用create bufferpool和database partition group字句来创建缓冲池。这意味着可以在对分区组的分区指定缓冲池。另外，每个分区组的分区的缓冲池大小可以不同。
CREATE BUFFERPOOL bpool_1 DATABASE PARTITION GROUP pg234 SIZE 10000
EXCEPT ON DBPARTITIONNUM (3 TO 4) SIZE 5000
这样分区组pg234中分区2会有一个大小为10000页的缓冲池bpool_1。分区3和4的缓冲池大小为5000页。

4.Table Spaces in a Partitioned Database Environment
You can create a table space in specific partitions, associating it to a partition group, by using the CREATE TABLESPACE statement with the IN DATABASE PARTITION GROUP clause. This allows users to have flexibility as to which partitions will actually be storing their tables. In a partitioned database environment with three servers, one partition per server, the statement
CREATE TABLESPACE mytbls IN DATABASE PARTITION GROUP pg234 MANAGED BY SYSTEM USING (‘/data’) BUFFERPOOL bpool_1
creates the table space mytbls, which spans partitions 2, 3, and 4 (assuming pg234 is a partition group consisting of these partitions). In addition, the table space is associated with buffer pool bpool_1 defined earlier. Note that creating a table space would fail if you provide conflicting partition information between the table space and the associated buffer pool. For example, if bpool_1 was created for partitions 5 and 6, and table space mytbls was created for partitions 2, 3,and 4, you would get an error message when trying to create this table space.
分区环境中的表空间
可以在创建表空间时指定分区组，在指定数据库分区中创建表空间。使用命令create tablespace字句in database partition group。这允许用户决定哪些分区真正存放表数据。如果表空间和指定的缓冲池有冲突，则创建表空间会失败。例如，bpool_1是为分区5和6创建的，表空间mytbls创建在分区2，3，4中。那么在创建这个表空间时会报错。

5.Coordinator Partition
In simple terms, the coordinator partition is the partition where the application connects to.In general, each database connection has a corresponding DB2 agent handling the application connection. An agent can be thought of as a process (Linux or UNIX) or thread (Windows) that performs DB2 work on behalf of the application. There are different types of agents. One of them, the coordinator agent, communicates with the application, receiving requests and sending replies. It can either satisfy the request itself or delegate the work to multiple subagents to work on the request.
简单说，协调分区就是应用连接到的分区。DB2代理有多种，其中一个协调代理，与应用通信，接受应用的请求和发送得到的应答。它既可以独立完成也可以协调多个子代理共同完成应用端来的请求。

6.Issuing Commands and SQL Statements in a DPF Environment
Use the db2_all command when you want to execute a command or SQL statement against all database partitions. For example, to change the db cfg parameter LOGFILSIZ for the database sample in all partitions, you would use
db2_all ";db2 UPDATE DB CFG FOR sample USING LOGFILSIZ 500"
When the semicolon (;) character is placed before the command or statement, the request runs in parallel on all partitions.
当需要在所有数据库分区上执行命令或SQL语句，可以使用db2_all命令。例如可以修改所有分区db cfg参数。db2_all ";db2 UPDATE DB CFG FOR sample USING LOGFILSIZ 500"
命令或语句的前分号(;)表示这个请求在所有分区并行执行。
In partitioned environments, the operating system command rah performs commands on all servers simultaneously. The rah command works per server, while the db2_all command works per database partition. The rah and db2_all commands use the same characters. For more information about the rah command, refer to your operating system manuals.In partitioned environments, pureXML support is not yet available;therefore, this section does not mention XQuery statements.
在分区环境中，操作系统命令rah在所有server中同时执行命令。rah命令在每个server中执行，db2_all在每个分区执行。rah和db2_all使用相同的字符。在分区环境中，还不支持XQuery语句。

7.The DB2NODE Environment Variable
The DB2 Environment, we talked about the DB2INSTANCE environment vari-
able used to switch between instances in your database system. The DB2NODE environment variable is used in a similar way, but to switch between partitions on your DPF system. By default, the active partition is the one defined with the logical port number of zero (0) in the db2nodes.cfg file for a server. To switch the active partition, change the value of the DB2NODE variable using the SET command on Windows and the export command on Linux or UNIX. Be sure to issue a terminate command for all connections from any partition to your database after changing this variable, or the change will not take effect.
在DPF环境中，DB2NODE(一定要大写)环境变量用来切换活动分区。默认地，活动分区是db2nodes.cfg文件中逻辑分区号为0的分区。通过SET(windows)或EXPORT(linux/unix)修改db2node变量的值，来切换活动分区。并确保执行terminate命令，即便没有应用连接到数据库，否则变更不会生效。
DB2NODE=0 ; export DB2NODE ; db2 terminate
To determine which is your active partition, you can issue this statement after connecting to a database: db2 "values (current dbpartitionnum)"

8.Distribution Maps and Distribution Keys
A distribution map is an internally generated array containing 4,096 entries for multipartition database partition groups or a single entry for single-partition database partition groups. The partition numbers of the database partition group are specified in a round-robin fashion in the array.
A distribution key is a column (or group of columns) that determines the partition on which a particular row of data is physically stored. You define a distribution key explicitly using the CREATE TABLE statement with the DISTRIBUTE BY clause.
分布映射表：是一个内部生成的数组，多分区数据库分区组的映射表有4096条记录。单分区数据库分区组只有一条记录。数据库分区组的分区号码以循环方式定义在数组中。(0,1,2,3,0,1,2,3,...0,1,2,3,0...)
分布键：决定特定行的数据物理存储在哪个分区上的一列(或多列)。可以通过显式使用create table语句distribute by字句来定义分布键。
db2 "create table table1 (col1 int, col2 int, col3 char(10)) in mytbls1 distribute by(col1, col2)"

9.partition expression
在创建表空间的时候，可以通过ON DBPARTITIONNUM 参数指定容器名字：
CREATE TABLESPACE ts2 MANAGED BY DATABASE
              USING (FILE ‘/dbfiles/ts2c1p0’ 2GB) ON DBPARTITIONNUM (0)
              USING (FILE ‘/dbfiles/ts2c1p1’ 2GB) ON DBPARTITIONNUM (1)
              USING (FILE ‘/dbfiles/ts2c1p2’ 2GB) ON DBPARTITIONNUM (2)
              USING (FILE ‘/dbfiles/ts2c1p3’ 2GB) ON DBPARTITIONNUM (3)
也可以通过分区表达式自动将分区号码添加大容器名字上，以此区别数据库的容器。分区表达式可以用在容器名的任何位置，并且分区表达式前必须有一个空格。如：
CREATE TABLESPACE ts2 MANAGED BY DATABASE
              USING (FILE ‘/dbfiles/ts2c1p $N’ 2GB)
CREATE TABLESPACE ts2 MANAGED BY DATABASE
              USING (FILE ‘/dbfiles/ts2c1p $N’ 2GB,
                           FILE ‘/dbfiles/ts2c2p $N’ 2GB)