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