When adding nodes to a partitioned database that has one or more system temporary table spaces with a page size that is different from the default page size (4 KB), you may encounter the error message: "SQL6073N Add Node operation failed" and an SQLCODE. This occurs because only the IBMDEFAULTBP buffer pool exists with a page size of 4 KB when the node is created.
For example, you can use the db2start command to add a node to the current partitioned database:
DB2START NODENUM 2 ADDNODE HOSTNAME newhost PORT 2
If the partitioned database has system temporary table spaces with the default page size, the following message is returned:
SQL6075W The Start Database Manager operation successfully added the node. The node is not active until all nodes are stopped and started again.
However, if the partitioned database has system temporary table spaces that are not the default page size, the returned message is:
SQL6073N Add Node operation failed. SQLCODE = "<-902>"
In a similar example, you can use the ADD NODE command after manually updating the db2nodes.cfg file with the new node description. After editing the file and running the ADD NODE command with a partitioned database that has system temporary table spaces with the default page size, the following message is returned:
DB20000I The ADD NODE command completed successfully.
However, if the partitioned database has system temporary table spaces that are not the default page size, the returned message is:
SQL6073N Add Node operation failed. SQLCODE = "<-902>"
One way to prevent the problems outlined above is to run:
DB2SET DB2_HIDDENBP=16
before issuing db2start or the ADD NODE command. This registry variable enables DB2 to allocate hidden buffer pools of 16 pages each using a page size different from the default. This enables the ADD NODE operation to complete successfully.
Another way to prevent these problems is to specify the WITHOUT TABLESPACES clause on the ADD NODE or the db2start command. After doing this, you will have to create the buffer pools using the CREATE BUFFERPOOL statement, and associate the system temporary table spaces to the buffer pool using the ALTER TABLESPACE statement.
When adding nodes to an existing nodegroup that has one or more table spaces with a page size that is different from the default page size (4 KB), you may encounter the error message: "SQL0647N Bufferpool "" is currently not active.". This occurs because the non-default page size buffer pools created on the new node have not been activated for the table spaces.
For example, you can use the ALTER NODEGROUP statement to add a node to a nodegroup:
DB2START CONNECT TO mpp1 ALTER NODEGROUP ng1 ADD NODE (2)
If the nodegroup has table spaces with the default page size, the following message is returned:
SQL1759W Redistribute nodegroup is required to change data positioning for objects in nodegroup "<ng1>" to include some added nodes or exclude some drop nodes.
However, if the nodegroup has table spaces that are not the default page size, the returned message is:
SQL0647N Bufferpool "" is currently not active.
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 ALTER NODEGROUP statement:
DB2START CONNECT TO mpp1 CREATE BUFFERPOOL bp1 SIZE 1000 PAGESIZE 8192 CONNECT RESET CONNECT TO mpp1 ALTER NODEGROUP ng1 ADD NODE (2)
A second way to prevent the problem is to run:
DB2SET DB2_HIDDENBP=16
before issuing the db2start command, and the CONNECT and ALTER NODEGROUP statements.
Another problem can occur when the ALTER TABLESPACE statement is used to add a table space to a node. For example:
DB2START
CONNECT TO mpp1
ALTER NODEGROUP ng1 ADD NODE (2) WITHOUT TABLESPACES
ALTER TABLESPACE ts1 ADD ('ts1') ON NODE (2)
This series of commands and statements generates the error message SQL0647N (not the expected message SQL1759W).
To complete this change correctly, you should reconnect to the database after the ALTER NODEGROUP... WITHOUT TABLESPACES statement.
DB2START
CONNECT TO mpp1
ALTER NODEGROUP ng1 ADD NODE (2) WITHOUT TABLESPACES
CONNECT RESET
CONNECT TO mpp1
ALTER TABLESPACE ts1 ADD ('ts1') ON NODE (2)
Another way to prevent the problem is to run:
DB2SET DB2_HIDDENBP=16
before issuing the db2start command, and the CONNECT, ALTER NODEGROUP, and ALTER TABLESPACE statements.