|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.