pc网站

書(shū)名： IBM DB2 9.7 Advanced Administration Cookbook
作者名： Adrian Neagu Robert Pelletier
本章字?jǐn)?shù)： 1377字
更新時(shí)間： 2021-08-20 15:33:24

Converting a non-partitioned database to a multipartitioned database on MS Windows

The second method, besides creating a new multipartitioned database from scratch, is to convert an existent non-partitioned database to a multipartitioned one. In this recipe, we will convert the NAV database to multipartitioned, using a single server running the Windows 7 SP1 Enterprise Edition operating system, named node1. Also, we will cover some existent particularities of multipartitioned database systems available only on the MS Windows platforms.

Getting ready

In this recipe, we will add two new database partitions. In this way, we will have a total of three database partitions. A new database partition group, NAVDATAGRP, will be created and populated by using a particular method that involves data migration from the default IBMDEFAULTGROUP database partition group to NAVDATAGR database partition group.

How to do it...

In this recipe, we will use Control Center combined with the command line. For this recipe, we will use an instance named DB2.

Create the NAV database:

E:\Program Files\IBM\SQLLIB\BIN>db2 " CREATE DATABASE NAV AUTOMATIC STORAGE YES ON 'E:\' DBPATH ON 'E:\' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 8192" DB20000I The CREATE DATABASE command completed successfully. E:\Program Files\IBM\SQLLIB\BIN>

Create the table spaces NAV_TBLS and NAV_INDX, according to the description found in the Creating and configuring DB2 non-partitioned databases recipe in Chapter 2, Administration and Configuration of the DB2 Non-partitioned Database; choose your own paths for table space containers.
Create buffer pools NAV_BPT8K and NAV_BPI8K, according to the description found in the Creating and configuring DB2 non-partitioned databases recipe in Chapter 2, Administration and Configuration of the DB2 Non-partitioned Database, or by using the provided scripts.
Create tables of the NAV application and load data into them using the provided scripts according to the description found in the Creating and configuring DB2 non-partitioned databases recipe in Chapter 2,Administration and Configuration of the DB2 Non-partitioned Database
Add two new database partitions to the instance DB2. Navigate to the instance DB2, right-click and choose Add Database Partitions to open the Add Database Partitions Launchpad.
The launchpad can be also launched from Control Center | Tools | Wizards | Add Database Partitions Launchpad:
Add database partition 1 with logical port 1, and database partition 2 with logical port 2:
Review the actions and click Finish:

Note

You should note that every database partition on MS Windows has its own service created and associated. These services have the startup mode set to Manual; if you want to start them at system startup, modify their startup mode to Automatic.
After the database partitions are successfully added, we create the NAVDATAGRDP database partition group. Navigate to Database partition groups, right-click, and choose Create….

Create a new table space NAV_PART_TBS, which will be allocated to the NAVDATAGRP database partition group. This partition will contain the NAV application's tables after data move:

E:\Program Files\IBM\SQLLIB\BIN> db2 "CREATE LARGE TABLESPACE NAV_PART_TBS IN DATABASE PARTITION GROUP "NAVDATAGRP" PAGESIZE 8 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL NAV_BPT8K " DB20000I The SQL command completed successfully. E:\Program Files\IBM\SQLLIB\BIN>

Create a new table space NAV_PART_INDX, which will be allocated to the NAVDATAGRP database partition group. This partition will contain the NAV application's indexes:

E:\Program Files\IBM\SQLLIB\BIN> db2 "CREATE LARGE TABLESPACE NAV_PART_INDX IN DATABASE PARTITION GROUP "NAVDATAGRP" PAGESIZE 8 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL NAV_BPI8K " DB20000I The SQL command completed successfully. E:\Program Files\IBM\SQLLIB\BIN>

Create a table named COMMP, with identical definition as table COMM. For data table space, use NAV_PART_TBLS, and for index storage, use NAV_PART_INDX table space:

E:\Program Files\IBM\SQLLIB\BIN>db2 "set current schema nav" DB20000I The SQL command completed successfully. E:\Program Files\IBM\SQLLIB\BIN> E:\Program Files\IBM\SQLLIB\BIN>db2 "create table COMMP ( ckey varchar(10) not null ,f2kaptkey varchar(10) not null ,cnam varchar(30) ,type decimal(4) ,freq1 decimal(8,3) ,freq2 decimal(8,3) ,freq3 decimal(8,3) ,freq4 decimal(8,3) ,freq5 decimal(8,3) ,clat decimal(8,2) ,clong decimal(9,2) ,CONSTRAINT PK_COMMP PRIMARY KEY ( ckey ) ) in nav_part_tbls index in nav_part_indx" DB20000I The SQL command completed successfully E:\Program Files\IBM\SQLLIB\BIN>

Insert data from table COMM into table COMMP:

E:\Program Files\IBM\SQLLIB\BIN>db2 "insert into COMMP select * from COMM" DB20000I The SQL command completed successfully E:\Program Files\IBM\SQLLIB\BIN>db2 commit DB20000I The SQL command completed successfully

Rename table COMM:

E:\Program Files\IBM\SQLLIB\BIN>db2 "RENAME TABLE COMM TO COMM_OLD" DB20000I The SQL command completed successfully E:\Program Files\IBM\SQLLIB\BIN>

Rename table COMMP to COMM:

E:\Program Files\IBM\SQLLIB\BIN>db2 "RENAME TABLE COMMP TO COMM" DB20000I The SQL command completed successfully E:\Program Files\IBM\SQLLIB\BIN>

Repeat steps 10, 11, 12, and 13. Create all the remaining tables with the NAV_PART_TBS table space for data and the NAV_PART_INDX table space for indexes.
To find the distribution on partitions, by row numbers, for the table COMM, issue (for better visibility run this statement in Command Center | Tools | Command Editor):

select dbpartitionnum(CKEY) as PARTITION_NUMBER, count(CKEY) as NUMBER_OF_ROWS from comm group by dbpartitionnum(CKEY) order by dbpartitionnum(CKEY)

How it works...

When you add one or more partitions for an existent instance, one or more additional Windows services are created and assigned to every partition. All databases under the instance being partitioned are affected. Partition groups IBMDEFAULTGROUP, IBMCATGROUP, and IBMTEMPGROUP are created, and all table spaces are assigned to them, depending on their role. Catalog table space is assigned by default to IBMCATGROUP, temporary table spaces are assigned to IBMTEMPGROUP, and all other table spaces that contain data are allocated to IBMDEFAULTGROUP. The db2nodes.cfg file is modified, depending on the number of partitions allocated and on the corresponding host and port.

There's more...

Under MS Windows platforms, there are some command-line utilities that help us to add, drop, list, and modify database partitions, instead of modifying the db2nodes.cfg file manually, which is not really recommended anyway. These were summarized, also, in previous recipes. Here, we will give some example about how to use them. All these command-line utilities are making modifications in the db2nodes.cfg file.

To list current database partitions, issue the following command:

E:\ Program Files\IBM\SQLLIB\BIN >db2nlist List of nodes for instance "DB2" is as follows: Node: "0" Host: "node1" Machine: "NODE1" Port: "0" Node: "1" Host: "node1" Machine: "node1" Port: "1" Node: "2" Host: "node1" Machine: "node1" Port: "2" E:\ Program Files\IBM\SQLLIB\BIN >db2nlist

To create database partition 3, with logical port 3, issue the following command:

E:\Program Files\IBM\SQLLIB\BIN>db2ncrt /n:3 /u:node1\db2admin,test123 /p:3 DBI1937W The db2ncrt command successfully added the node. The node is not active until all nodes are stopped and started again. Explanation: The db2nodes.cfg file is not updated to include the new node until all nodes are simultaneously stopped by the STOP DATABASE MANAGER (db2stop) command. Until the file is updated, the existing nodes cannot communicate with the new node. User response: Issue db2stop to stop all the nodes. When all nodes are successfully stopped, issue db2start to start all the nodes, including the new node. E:\Program Files\IBM\SQLLIB\BIN> E:\Program Files\IBM\SQLLIB\BIN>db2stop force 12/20/2011 00:13:05 1 0 SQL1032N No start database manager command was issued. 12/20/2011 00:13:05 2 0 SQL1032N No start database manager command was issued. 12/20/2011 00:13:06 0 0 SQL1064N DB2STOP processing was successful. SQL6033W Stop command processing was attempted on "3" node(s). "1" node(s) wer e successfully stopped. "2" node(s) were already stopped. "0" node(s) could no t be stopped. E:\Program Files\IBM\SQLLIB\BIN>db2start 12/20/2011 00:16:15 1 0 SQL1063N DB2START processing was successful. 12/20/2011 00:16:15 3 0 SQL1063N DB2START processing was successful. 12/20/2011 00:16:16 0 0 SQL1063N DB2START processing was successful. 12/20/2011 00:16:16 2 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. E:\Program Files\IBM\SQLLIB\BIN>

List partitions; actually, we have partition 3 added:

E:\Program Files\IBM\SQLLIB\BIN>db2nlist List of nodes for instance "DB2" is as follows: Node: "0" Host: "node1" Machine: "NODE1" Port: "0" Node: "1" Host: "node1" Machine: "node1" Port: "1" Node: "2" Host: "node1" Machine: "node1" Port: "2" Node: "3" Host: "node1" Machine: "NODE1" Port: "3"

To drop database partition 3, issue the following command:

E:\Program Files\IBM\SQLLIB\BIN> E:\Program Files\IBM\SQLLIB\BIN>db2stop dbpartitionnum 3 12/20/2011 00:17:13 3 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. E:\Program Files\IBM\SQLLIB\BIN>db2ndrop /n:3 SQL2808W Node "3" for instance "DB2" has been deleted.

List partitions again; in this listing, we can see that partition 3 has disappeared:

E:\Program Files\IBM\SQLLIB\BIN>db2nlist List of nodes for instance "DB2" is as follows: Node: "0" Host: "node1" Machine: "NODE1" Port: "0" Node: "1" Host: "node1" Machine: "node1" Port: "1" Node: "2" Host: "node1" Machine: "node1" Port: "2"

官术网_书友最值得收藏!

IBM DB2 9.7 Advanced Administration Cookbook

Converting a non-partitioned database to a multipartitioned database on MS Windows

Getting ready

How to do it...

Note

How it works...

There's more...