How to reducing the number of partitions in DB2

Steps to be followed for reducing the number of partitions :

Step 1 ) ALTER DATABASE PARTITION GROUP

When a database partition is dropped from a database partition group, the catalog entry for the database partition (see SYSCAT.DBPARTITIONGROUPDEF) is updated. If there are no tables defined in the table spaces defined in the database partition group, the distribution map is changed immediately to exclude the dropped database partition and the entry for the database partition in the database partition group is dropped. If tables exist, the distribution map is not changed and the indicator (IN_USE) is set to indicate that the database partition is waiting to be dropped. The REDISTRIBUTE DATABASE PARTITION GROUP command must be used to redistribute the data and drop the entry for the database partition from the database partition group.

USAGE:

ALTER DATABASE PARTITION GROUP db-partition-name DROP DBPARTITIONNUMS (9,10,11,12,13,14,15,16);

Step 2 ) REDISTRIBUTE DATABASE PARTITION GROUP

Redistributes data across the database partitions in a database partition group. The current data distribution, whether it is uniform or skewed, can be specified. The redistribution algorithm selects the partitions to be moved based on the current data distribution.

Parameters for redistribute DB partition group command :

DATABASE PARTITION GROUP database partition group

The name of the database partition group. This one-part name identifies a database partition group described in the SYSCAT.DBPARTITIONGROUPS catalog table. The database partition group cannot currently be undergoing redistribution.

Note:

Tables in the IBMCATGROUP and the IBMTEMPGROUP database partition groups cannot be redistributed.

UNIFORM

Specifies that the data is uniformly distributed across hash partitions (that is, every hash partition is assumed to have the same number of rows), but the same number of hash partitions do not map to each database partition. After redistribution, all database partitions in the database partition group have approximately the same number of hash partitions

CONTINUE

Continues a previously failed REDISTRIBUTE DATABASE PARTITION GROUP operation. If none occurred, an error is returned.

ROLLBACK

Rolls back a previously failed REDISTRIBUTE DATABASE PARTITION GROUP operation. If none occurred, an error is returned.

When a redistribution operation is done, a message file is written to:

·         The /sqllib/redist directory on UNIX based systems, using the following format for subdirectories and file name: database-name.database-partition-group-name.timestamp.

This utility performs intermittent COMMITs during processing. This can cause type 2 connections to receive an SQL30090N error.

All packages having a dependency on a table that has undergone redistribution are invalidated. It is recommended to explicitly rebind such packages after the redistribute database partition group operation has completed. Explicit rebinding eliminates the initial delay in the execution of the first SQL request for the invalid package. The redistribute message file contains a list of all the tables that have undergone redistribution.

It is also recommended to update statistics by issuing RUNSTATS after the redistribute database partition group operation has completed.

Before starting a redistribute operation, ensure there are no tables in the Load Pending state. Table states can be checked by using the LOAD QUERY command. If you discover data in the wrong database partition as a result of a redistribute operation, there are two options. You can:

unload the table, drop it and then reload the table, or

use a new target map to redistribute the database partition group again.

USAGE:

REDISTRIBUTE DATABASE PARTITION GROUP UNIFORM;

Step 3) db2 force application all

Step 4) export db2node=<partition number to drop>

Step 5 ) DROP DBPARTITIONNUM VERIFY

Verifies if a database partition exists in the database partition groups of any databases, and if an event monitor is defined on the database partition. This command should be used prior to dropping a database partition from a partitioned database system.

This command only affects the database partition on which it is issued.

If a message is returned, indicating that the database partition is not in use, use the STOP DATABASE MANAGER command with DROP DBPARTITIONNUM to remove the entry for the database partition from the db2nodes.cfg file, which removes the database partition from the database system.

If you recieve RC=SQL6034W (Node not used in any database),

Step 6)  Unset DB2NODE

Step 7 ) STOP DATABASE MANAGER

On partitioned database system, this command stops the current database manager instance on a database partition or on all database partitions. When it stops the database manager on all database partitions, it uses the db2nodes.cfg configuration file to obtain information about each database partition.

This command can also be used to drop a database partition from the db2nodes.cfg file (partitioned database systems only).

By default, and in a partitioned database environment, this command affects all database partitions that are listed in the db2nodes.cfg file.

DROP DBPARTITIONNUM db-partition-number

partitioned database systems only. Specifies the database partition to be dropped from the db2nodes.cfg file.

Before using this parameter, run the DROP DBPARTITIONNUM VERIFY command to ensure that there is no user data on this database partition.

When this option is specified, all database partitions in the db2nodes.cfg file are stopped.

If you recieve RC=SQL6035W (Node in use by database)

1. Redistribute the data again ( Perform Step 2 again)

2. Drop any associated event monitors

3. Repeat the steps to drop the partition  ( Repeat Step 3 to 7 ).