Error recovery when adding database partitions

Adding database partitions does not fail as a result of nonexistent buffer pools, because the database manager creates system buffer pools to provide default automatic support for all buffer pool page sizes.

However, if one of these system buffer pools is used, performance might be seriously affected, because these buffer pools are very small. If a system buffer pool is used, a message is written to the administration notification log. System buffer pools are used in database partition addition scenarios in the following circumstances:
  • You add database partitions to a partitioned database environment that has one or more system temporary table spaces with a page size that is different from the default of 4 KB. When a database partition is created, only the IBMDEFAULTDP buffer pool exists, and this buffer pool has a page size of 4 KB.
    Consider the following examples:
    1. You use the START DBM command to add a database partition to the current multi-partition database:
         START DBM DBPARTITIONNUM 2 ADD DBPARTITIONNUM HOSTNAME newhost PORT 2	
    2. You use the ADD DBPARTITIONNUM command after you manually update the db2nodes.cfg file with the new database partition description.
    One way to prevent these problems is to specify the WITHOUT TABLESPACES clause on the ADD DBPARTITIONNUM or the START DBM commands. After doing this, use the CREATE BUFFERPOOL statement to create the buffer pools using the appropriate SIZE and PAGESIZE values, and associate the system temporary table spaces to the buffer pool using the ALTER TABLESPACE statement.
  • You add database partitions to an existing database partition group that has one or more table spaces with a page size that is different from the default page size, which is 4 KB. This occurs because the non-default page-size buffer pools created on the new database partition have not been activated for the table spaces.
    Note: In previous versions, this command used the NODEGROUP keyword instead of the DATABASE PARTITION GROUP keywords.
    Consider the following example:
    • You use the ALTER DATABASE PARTITION GROUP statement to add a database partition to a database partition group, as follows:
         START DBM
         CONNECT TO mpp1
         ALTER DATABASE PARTITION GROUP ng1 ADD DBPARTITIONNUM (2)	
      One way to prevent this problem is to create buffer pools for each page size and then to reconnect to the database before issuing the following ALTER DATABASE PARTITION GROUP statement:
         START DBM
         CONNECT TO mpp1
         CREATE BUFFERPOOL bp1 SIZE 1000 PAGESIZE 8192
         CONNECT RESET
         CONNECT TO mpp1
         ALTER DATABASE PARTITION GROUP ng1 ADD DBPARTITIONNUM (2)	 
Note: If the database partition group has table spaces with the default page size, message SQL1759W is returned.