The IBM DB2 database multipartitioned feature offers the ability to distribute a large database onto different physical servers or the same SMP server, balancing the workload onto multiple databases that are working as one, offering a very scalable way of data processing. We may have all the database partitions reside on the same server, this method of database partitioning is called logical partitioning. There is another scenario when the database partitions are spanned on different physical servers; this partitioning method is called physical partitioning.
An instance in a multipartitioned configuration is not very different by a non-partitioned instance, if it is running on a logical partitioning scheme. To use only physical partitioning, or physical partitioning combined with logical partitioning, an instance must be configured as shared across all the database partitions. In this recipe, we will use the last scenario.
The instance is created once on one node; on the other participant nodes, you have to create just the instance owner user with the same user ID (UID) and GIDs and the same home directory as on the instance owner node. In the following recipe, we will configure
servers for the purpose of multipartitioning and will create a new instance named db2instp.
Notice that in this recipe we will use node and partition termsinterchangeably
To install a multipartitioned instance, we need to prepare a suitable environment. For this recipe, we will use the two Linux servers named nodedb21 and nodedb22, mentioned before. nodedb21 will contain the instance home and will export it through NFS to the nodedb22 system. We will also use a new disk partition, defined on nodedb21, for instance home /db2partinst, which, in our case, is a Linux LVM partition. We will create users on both servers with the same UID, and will install IBM DB2 ESE in a new location or DB2HOME—/opt/ibm/db2/V9.7_part on nodedb21 with the create a response file option. On nodedb22, we'll also install IBM DB2 ESE, in the location /opt/ibm/db2/V9.7_part, using the response file created during installation on nodedb21.
Because this is not a Linux book, we do not cover how to install NFS or how to create a new Linux partition. As a preliminary task, you should check if you have NFS and portmap installed and running on both servers.
As user
root, execute the following commands on both servers:To check if we have NFS and portmap on
nodedb21:[root@nodedb21 ~]# rpm -qa | grep nfs nfs-utils-lib-1.0.8-7.6.el5 nfs-utils-1.0.9-44.el5 [root@nodedb21 ~]# rpm -qa | grep portmap portmap-4.0-65.2.2.1 [root@nodedb21 ~]#
To check their current status on
nodedb21:[root@nodedb21 ~]# service nfs status rpc.mountd (pid 3667) is running... nfsd (pid 3664 3663 3662 3661 3660 3659 3658 3657) is running... rpc.rquotad (pid 3635) is running... [root@nodedb21 ~]# [root@nodedb21 ~]# service portmap status portmap (pid 3428) is running... [root@nodedb21 ~]#
To automatically export
/db2partinston system boot, add your hostnames or the corresponding IP numbers to the/etc/exportsfile. Onnodedb21, add the following line in/etc/exports:/db2partinst 10.231.56.117(rw,no_root_squash,sync) 10.231.56.118(rw,no_root_squash,sync)To export the partition immediately, execute the following command:
[root@nodedb22 ~]# exportfs –ra [root@nodedb22 ~]#
On
nodedb22, as userroot, create a directory/db2partinst, used as mount point for/db2partinst, exported fromnodedb21:[root@nodedb22 ~]# mkdir /db2partinst [root@nodedb22 ~]#
In
/etc/fstabonnodedb22, to mount/db2partinston system boot, add the following line:nodedb21:/db2partinst /db2partinst nfs rw,timeo=300,retrans=5,hard,intr,bg,suidTo mount the partition immediately on
nodedb22, issue the following command:[root@nodedb22 ~]# mount nodedb21:/db2partinst /db2partinst [root@nodedb22 ~]#
On
nodedb21, create the instance ownerdb2instpand the fenced userdb2fencp. Instance home will be located in/db2partinst/db2instp:[root@nodedb22 ~]# useradd -u 1316 -g db2iadm1 -m -d /db2partinst/db2instp db2instp [root@nodedb22 ~]# useradd -u 1315 -g db2fadm1 -m -d /db2partinst/db2fencp db2fencp [root@nodedb22 ~]# passwd db2instp Changing password for user db2instp. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@nodedb21 ~]# passwd db2fencp Changing password for user db2fencp. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@nodedb21 ~]#
Repeat step 1 on
nodedb22and ignore any warnings.
In a physical multipartitioned environment, any instance owner user has to be able to execute commands on any participant node. To ensure this, we need to establish user equivalence or host equivalence between nodes. Actually, we have two methods: one is with RSH, which is less secure and the other is using SSH, which is secure. With SSH, there are two methods: one is host-based authentication and the other is client-based authentication. Next, we will implement client-based authentication; this method fits better with a small number of partitions, as in our example.
As user
db2instponnodedb21, execute the following commands:[db2instp@nodedb21 ~]$ cd ~ [db2instp@nodedb21 ~]$ mkdir .ssh [db2instp@nodedb21 ~]$ chmod 700 .ssh [db2instp@nodedb21 ~]$ cd .ssh [db2instp@nodedb21 .ssh]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/db2partinst/db2instp/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /db2partinst/db2instp/.ssh/id_rsa. Your public key has been saved in /db2partinst/db2instp/.ssh/id_rsa.pub. The key fingerprint is: 2b:90:ee:3b:e6:28:11:b1:63:93:ba:88:d7:d5:b1:14 db2instp@nodedb21 [db2instp@nodedb21 .ssh]$ cat id_rsa.pub >> authorized_keys [db2instp@nodedb21 .ssh]$ chmod 640 authorized_keys
As user
db2instponnodedb22, execute the following commands:[db2instp@nodedb22 .ssh]$ cd ~/.ssh [db2instp@nodedb22 .ssh]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/db2partinst/db2instp/.ssh/id_rsa): /db2partinst/db2instp/.ssh/id_rsa already exists. Overwrite (y/n)? y Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /db2partinst/db2instp/.ssh/id_rsa. Your public key has been saved in /db2partinst/db2instp/.ssh/id_rsa.pub. The key fingerprint is: 87:36:b4:47:5a:5c:e5:3e:4e:e9:ce:5b:47:2c:ce:6b db2instp@nodedb22 [db2instp@nodedb22 .ssh]$ cat id_rsa.pub >> authorized_keys [db2instp@nodedb22 .ssh]$
Go back on
nodedb21and issue the following commands to set up a host trust relationship:[db2instp@nodedb21 ~]$ cd ~/.ssh [db2instp@nodedb21 .ssh]$ ssh-keyscan -t rsa nodedb21,10.231.56.117 >> known_hosts # nodedb21 SSH-2.0-OpenSSH_4.3 [db2instp@nodedb21 .ssh]$ ssh-keyscan -t rsa nodedb22,10.231.56.118 >> known_hosts # nodedb22 SSH-2.0-OpenSSH_4.3 [db2instp@nodedb21 .ssh]$
Verify that the client authentication is working; on
nodedb21, issuessh nodedb22 date(do it the other way around—now it should work without asking for a password):[db2instp@nodedb21 .ssh]$ ssh nodedb22 date Thu Jun 9 16:42:33 EEST 2011 [db2instp@nodedb21 .ssh]$ ssh nodedb22 [db2instp@nodedb22 ~]$ ssh nodedb21 date Thu Jun 9 16:42:48 EEST 2011 [db2instp@nodedb22 ~]$ ssh nodedb22 date Thu Jun 9 16:42:55 EEST 2011 [db2instp@nodedb22 ~]$ ssh nodedb21 [db2instp@nodedb21 ~]$ ssh nodedb21 date Thu Jun 9 16:43:07 EEST 2011 [db2instp@nodedb21 ~]$
A response file is a text file containing installation and configuration information such as paths, installation options etc. It can be created and recorded using interactive installation and replayed by other installations to perform the same steps.
Launch
db2setup, and, at step 4 of the installation wizard (Install action), check the Install DB2 Enterprise Server Edition on this computer and save my setting in a response file option. Provide the complete path to the response file.At step 5, specify
/opt/ibm/db2/V9.7_partfor Installation directory.At step 7 (Partitioning option), check Multiple partition instance.
Next, for DB2 instance owner, choose
db2instpand, for fenced user, choosedb2fencp. On the next screen, choose Do not create tools catalog. At the end of installation, we will find (in the directory chosen at step 4 of installation wizard) two files with.rspextension; you need to copy justdb2ese_addpart.rsptonodedb22and issue onnodedb22, from the installation directory:./db2setup -r <your path>db2ese_addpart.rsp DBI1191I db2setup is installing and configuring DB2 according to the response file provided. Please wait.
The communication method of inter-partition command execution is controlled by DB2RSCHCM registry variable. Because our choice is SSH for inter-partition command execution, you must next set the
DB2RSHCMDvariable to point to SSH executableDB2RSHCMD=/usr/bin/ssh. If this variable is not set, thershmethod is used by default:[db2instp@nodedb21 ~]$ db2set DB2RSHCMD=/usr/bin/ssh -iTo verify the current DB2 registry variables, issue the following command:
[db2instp@nodedb21 ~]$ db2set -all [i] DB2RSHCMD=/usr/bin/ssh [i] DB2COMM=tcpip [i] DB2AUTOSTART=YES [g] DB2FCMCOMM=TCPIP4 [g] DB2SYSTEM=nodedb21 [g] DB2INSTDEF=db2instp
In the db2nodes.cfg file, database partition configuration file, located in $INSTANCEHOME/sqllib, set the participant nodes. Define three nodes—two on nodedb21, partion number 0 with logical port 0 and partition number 2 with logical port 1 and one on nodedb22, partition 1 with logical port 0. After adding the nodes we should have the following structure:
0 nodedb21 0 1 nodedb22 0 2 nodedb21 1
Instance db2instp knows about the current nodes by reading their definition from db2nodes.cfg database partition configuration file. The logical ports and number of maximum partitions per server are limited by the range defined within /etc/services file as follows:
DB2_db2inst1 60000/tcp DB2_db2inst1_1 60001/tcp DB2_db2inst1_2 60002/tcp DB2_db2inst1_END 60003/tcp
The structure of db2nodes.cfg, in some cases, can be further elaborated with optional information such as resourcenames or netnames; in our case being a simple setup used for demonstration purpose we have defined only the nodes, hostnames, and the logical ports.
Under Unix and Linux, db2nodes has the following complete format:
dbpartitionnum hostname logicalport netname resourcesetname
Under MS Windows, db2nodes has the following complete format:
dbpartitionnum hostname computername logicalport netname resourcesetname
DB2 has two utilities to verify that communication between nodes is working: db2_all and rah. You can also issue practically any administrative command (backup, restore, setting parameters, and so on) across the database partitions with these utilities.
An example of using db2_all for verification:
[db2instp@nodedb21 ~]$ db2_all uptime 11:54:02 up 17:11, 1 user, load average: 0.07, 0.03, 0.00 nodedb21: uptime completed ok 11:54:03 up 17:11, 0 users, load average: 0.10, 0.03, 0.01 nodedb22: uptime completed ok 11:54:03 up 17:11, 1 user, load average: 0.07, 0.03, 0.00 nodedb21: uptime completed ok
The same using rah:
[db2instp@nodedb21 ~]$ rah uptime 14:56:19 up 35 days, 18:09, 1 user, load average: 0.08, 0.02, 0.01 nodedb21: uptime completed ok 14:56:20 up 35 days, 18:09, 0 users, load average: 0.00, 0.00, 0.00 nodedb22: uptime completed ok 14:56:20 up 35 days, 18:09, 1 user, load average: 0.08, 0.02, 0.01 nodedb21: uptime completed ok
Obviously, there is also a possibility of using a shared disk, formatted with a concurrent file system, such as, IBM's GPFS or Red Hat GFS, for instance home, and used for sharing across the nodes instead of using NFS exports.
On Windows, it is not recommended to edit the db2nodes.cfg file manually; use the
The following commands instead:
db2nlist—to list database partitionsdb2ncrt—to add a database partition server to an instancedb2ndrop—to drop a database partition server to an instancedb2nchg—to modify a database partition server configuration
The Converting a non-partitioned database to a multipartitioned database on MS Windows recipe in Chapter 3, DB2 Multipartitioned Databases—Administration and Configuration