DPF (Data Partitioning Feature) is a DB2 feature (licensed) providing ability to partition a database within a single server or across several (cluster of) servers. It allows sharing workload between several machines or between several processes (SQL engines) on a single machine. The purpose is simple : better performance.
DB2 edition
DPF is not available in DB2 Express-C edition but can be installed with DB2 Trial Edition (90 days).
Single server and multi-server installation
It is very easy to set up DPF within a single server. More tricky is installing DPF on several physical servers, each one having its own part of the database on the local datastore. It is described in the InfoCenter but applying this instruction is painstaking job.
Two (or more) machines
In case of multi-server installation one machine ("sun") must be the owner of Catalog Partition which cannot be distributed. All other machines should connect to the Catalog Partition using nfs connection.
Prerequisities
- ksh (Korn shell) installed
- nfs server installed and running (for the owner of Catalog Partition)
- nfs client software for other servers participating in DPF
Assume that we have two machines: sun (the owner of Catalog Paritition) and earth. All machines participating in DPF should have its own copy of DB2 installed. Every installation should be the mirror of sun installation (the same installation path), the same DB2 version and FP level.
Create DB2 instance on sun machine
The simplest way is to install from command line directly.
useradd db2fenc1 useradd db2dpf passwd db2dpf db2icrt -u db2fenc1 db2dpfLog on as db2dpf user
Execute commands:
db2set DB2RSHCMD=ssh db2set DB2COMM=tcpip db2 update database manager configuration using svcename db2c_db2dpf ssh-keygenMake password-less connection to db2pdf
cat .ssh/id_rsa.pub >>.ssh/authorized_keys chmod 600 .ssh/authorized_keyCreate logical partitions on sun server
By default an instance is created with one logical partition (defined in sqllib/db2nodes.cfg) configuration file.
0 sun 0Logical partitions can be added directly by modyfing db2nodes.cfg file
0 sun 0 1 sun 1 2 sun 2or by executing a command
db2start dbpartitionnum 1 add dbpartitionnum hostname sun port 1 db2start dbpartitionnum 2 add dbpartitionnum hostname sun port 2After creating logical partitions stop and start db2 to be sure that instance is working
db2stop db2startThe output should look like:
0/22/2014 06:02:37 1 0 SQL1063N DB2START processing was successful. 10/22/2014 06:02:37 0 0 SQL1063N DB2START processing was successful. 10/22/2014 06:02:38 2 0 SQL1063N DB2START processing was successful.NFS on sun machine
Make sure that nfs is installed and running on sun machine.
[root@sun ~]# service nfs start Starting NFS services: [ OK ] Starting NFS quotas: [ OK ] Starting NFS mountd: [ OK ] Stopping RPC idmapd: [ OK ] Starting RPC idmapd: [ OK ] Starting NFS daemon: [ OK ] [root@sun ~]# service nfs status rpc.svcgssd is stopped rpc.mountd (pid 4573) is running... nfsd (pid 4638 4637 4636 4635 4634 4633 4632 4631) is running... rpc.rquotad (pid 4569) is running...Export /home/db2pdf
Modify /etc/exports file
/home/db2dpf *(insecure,rw,async,no_root_squash)Execute command
export -a showmount -e sun [root@sun ~]# showmount -e sun Export list for sun: /home/db2dpf *Create db2pdf user on earth machine
Create group db2dpf and user db2dpf on earth machine. It is very important that uid and gid should be exactly the same as uid and gid on the sun machine (assuming 522 for uid and gid)
mkdir /home/db2dpf groupadd -g 522 db2dpf useradd -u 522 -g db2dpf -m -d /home/db2dpf db2dpf chown db2dpf /home/db2dpf chgrp db2dpf /home/db2dpf passwd db2dpfNFS client on earth machine
Make sure that nfs client software is installed on earth machine. Check that nfs exported directory from sun machine is visible
[root@earth ~]# showmount -e sun Export list for sun: /home/db2dpf *Verify /etc/services configuration file on earth machine
Entries in /etc/services file on earth machine related to db2dpf instance should be copied from sun host
db2c_db2dpf 50001/tcp DB2_db2dpf 60016/tcp DB2_db2dpf_1 60017/tcp DB2_db2dpf_2 60018/tcp DB2_db2dpf_3 60019/tcp DB2_db2dpf_4 60020/tcp DB2_db2dpf_END 60021/tcp
Mount home directory on earth machine
Add entry to /etc/fstab
sun:/home/db2dpf /home/db2dpf nfs user,rw,timeo=7,hard,intr,bg,suid,lock,execMount and verify
mount /home/db2dpfMake sure that owner and group in /home/db2dpf are db2dpf
[root@earth ~]# ls -l /home/db2dpf razem 12 drwxrwxr-x 8 db2dpf db2dpf 4096 10-22 06:02 db2dpf -rw-r--r-- 1 db2dpf db2dpf 81 10-22 05:45 db2nodes.cfg drwxrwsr-t 25 db2dpf db2dpf 4096 10-22 05:50 sqllibIf it is not the case (nobody owner) execute
/usr/sbin/nfsidmap -cLog on to earth machine as db2dpf
If successful execute commands: db2, db2level
Add partitions controlled by earth machine
Log on to the sun machine and extend sqllib/db2nodes.cfg file or add them from command line
0 sun 0 1 sun 1 2 sun 2 3 earth 0 4 earth 1 5 earth 2Restart db2 on sun machine
db2stop
db2start
[db2dpf@sun ~]$ db2start 10/27/2014 15:51:02 0 0 SQL1063N DB2START processing was successful. 10/27/2014 15:51:03 2 0 SQL1063N DB2START processing was successful. 10/27/2014 15:51:03 1 0 SQL1063N DB2START processing was successful. 10/27/2014 15:51:05 5 0 SQL1063N DB2START processing was successful. 10/27/2014 15:51:06 4 0 SQL1063N DB2START processing was successful. 10/27/2014 15:51:08 3 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.Create SAMPLE database
If multi-partitioned instance is started successfully create SAMPLE database (it will take several minutes)
db2samplConnect to SAMPLE database and verify table distribution
db2 connect to SAMPLE [db2dpf@sun ~]$ db2 "SELECT DBPARTITIONNUM(EMPNO), COUNT( * ) FROM EMPLOYEE GROUP BY DBPARTITIONNUM(EMPNO)" 1 2 ----------- ----------- 0 6 2 6 1 8 3 9 4 5 5 8 6 record(s) selected.EMPLOYEE table is distributed between six partitions.
It is final ?
No. Although SAMPLE database is created in partitioned environment and distributed evenly the container (directory where database is located) is located on the sun machine in db2dpf directory.
[db2dpf@NC9128110007 ~]$ db2 list tablespace containers for 2 Tablespace Containers for Tablespace 2 Container ID = 0 Name = /home/db2dpf/db2dpf/NODE0000/SAMPLE/T0000002/C0000000.LRG Type = File [db2dpf@sun ~]$ ls db2dpf NODE0000 NODE0001 NODE0002 NODE0003 NODE0004 NODE0005Thus the earth server is reading data for 3, 4 and 5 partition from nfs mounted directory which does not make any sense.
Create database distributed between different servers
On sun and earth machine create directory under the same location but outside nfs managed space. The owner of the directory should be db2dpf.
mkdir /db2dpf chown db2dpf /db2dpf chgrp db2dpf /db2dpfThen (from sun machine) create database stored in /db2dpf directory
db2 create database sales on "/db2dpf"Both sun and earth will look for their partitions in /db2pdf container but every time it will be a local directory.
On sun and earth machine
[db2dpf@sun ~]$ ls /db2dpf/db2dpf NODE0000 NODE0001 NODE0002 [db2dpf@earth ~]$ ls /db2dpf/db2dpf NODE0003 NODE0004 NODE0005Conclusion
Creating partitioned database distributed between several servers is painstaking job for the first time. We have to mix some server administration tasks (nfs) and DB2 administrating tasks. But for the second and next time it does not cause many problems.
Brak komentarzy:
Prześlij komentarz