pg电子中文

書名： IBM DB2 9.7 Advanced Administration Cookbook
作者名： Adrian Neagu Robert Pelletier
本章字數： 403字
更新時間： 2021-08-20 15:33:23

Altering database partition groups— removing partitions from a database partition group

Usually, removing partitions from a database partition group is a seldom operation. It may be performed, for example, when a server containing the partitions is removed and commissioned from the configuration, or a partition is not placed optimally and induces performance problems. This is the first step if we want to drop the database partitions from the database.

Getting ready

In this recipe, we will remove partitions 3 and 4, added before to the NAVDATAGRP database partition group, and redistribute data on the remaining partitions.

How to do it...

It is highly recommended, before you begin, to drop a partition from a database partition group to perform a full database backup.

Using the command line

To remove partitions 3 and 4, execute the following command:

[db2instp@nodedb21 ~]$ db2 "ALTER DATABASE PARTITION GROUP NAVDATAGRP DROP DBPARTITIONNUMS (3 TO 4)" SQL1759W Redistribute database partition group is required to change database partitioning for objects in database partition group "NAVDATAGRP" to include some added database partitions or exclude some dropped database partitions. SQLSTATE=01618 [db2instp@nodedb21 ~]$

Initially, the partitions are just marked as removed; actually, we have to redistribute data from them as the warning message SQL179W instructs us. After the redistribution operation is finished, they are removed completely. To see the status of partition 3 and 4, run the following statement:
```
[db2instp@nodedb21 ~]$db2" select * from sysibm.sysnodegroupdef where ngname='NAVDATAGRP'" NGNAME NODENUM IN_USE NAVDATAGRP 0 Y NAVDATAGRP 1 Y NAVDATAGRP 2 Y NAVDATAGRP 3 T NAVDATAGRP 4 T [db2instp@nodedb21 ~]$ T -means that the distribution map is removed and no longer availaible 
```

Now, redistribute data to the remaining partitions 0, 1, and 2:

[db2instp@nodedb21 ~]$ db2 "REDISTRIBUTE DATABASE PARTITION GROUP NAVDATAGRP UNIFORM" DB20000I The REDISTRIBUTE NODEGROUP command completed successfully.

Now, we can see that the partitions 3 and 4 are completely removed and do not appear in catalog:

[db2instp@nodedb21 ~]$ db2 "select * from sysibm.sysnodegroupdef where ngname='NAVDATAGRP'" NGNAME NODENUM IN_USE NAVDATAGRP 0 Y NAVDATAGRP 1 Y NAVDATAGRP 2 Y [db2instp@nodedb21 ~]$

Note

You can also use the LIST DATABASE PARTITION GROUPS SHOW DETAIL command to list detailed information about partition groups .

How it works...

Removing partitions from a database partition group is an operation similar to adding partitions in terms of internal processing.

There's more...

At this step do not remove database partitions that contain data by editing manually the db2nodes.cfg database partition configuration file.

官术网_书友最值得收藏!

IBM DB2 9.7 Advanced Administration Cookbook