Creating and configuring an instance for multipartitioned environments

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

Getting ready

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.

How to do it...

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

2. 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 ~]# 
   

Set up NFS for sharing the instance home

1. To automatically export /db2partinst on system boot, add your hostnames or the corresponding IP numbers to the /etc/exports file. On nodedb21, 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) 
   

2. To export the partition immediately, execute the following command:

   	[root@nodedb22 ~]# exportfs –ra
   	[root@nodedb22 ~]#
   

3. On nodedb22, as user root, create a directory /db2partinst, used as mount point for /db2partinst, exported from nodedb21:

   	[root@nodedb22 ~]# mkdir /db2partinst
   	[root@nodedb22 ~]#
   

4. In /etc/fstab on nodedb22, to mount /db2partinst on system boot, add the following line:

   	nodedb21:/db2partinst /db2partinst nfs rw,timeo=300,retrans=5,hard,intr,bg,suid
   

5. To mount the partition immediately on nodedb22, issue the following command:

   	[root@nodedb22 ~]# mount nodedb21:/db2partinst /db2partinst
   	[root@nodedb22 ~]#
   

Creating the instance owner and fenced user

1. On nodedb21, create the instance owner db2instp and the fenced user db2fencp. 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 ~]#
   

2. Repeat step 1 on nodedb22 and ignore any warnings.

Set up SSH for client authentication

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.

1. As user db2instp on nodedb21, 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
   

2. As user db2instp on nodedb22, 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]$
   

3. Go back on nodedb21 and 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]$
   

4. Verify that the client authentication is working; on nodedb21, issue ssh 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 ~]$
   

Install DB2 ESE software with a response file option

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.

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

2. At step 5, specify /opt/ibm/db2/V9.7_part for Installation directory.

3. At step 7 (Partitioning option), check Multiple partition instance.

4. Next, for DB2 instance owner, choose db2instp and, for fenced user, choose db2fencp. 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 .rsp extension; you need to copy just db2ese_addpart.rsp to nodedb22 and issue on nodedb22, 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.
   

Configuring communication for inter-partition command execution

1. 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 DB2RSHCMD variable to point to SSH executable DB2RSHCMD=/usr/bin/ssh. If this variable is not set, the rsh method is used by default:

   	[db2instp@nodedb21 ~]$ db2set DB2RSHCMD=/usr/bin/ssh -i
   

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

Configuring the nodes

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

How it works...

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

There's more...

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 partitions

• db2ncrt—to add a database partition server to an instance

• db2ndrop—to drop a database partition server to an instance

• db2nchg—to modify a database partition server configuration

See also

The Converting a non-partitioned database to a multipartitioned database on MS Windows recipe in Chapter 3, DB2 Multipartitioned Databases—Administration and Configuration