Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
pais:db2dpf2nodes [2016/08/04 18:49]
master created
pais:db2dpf2nodes [2016/08/05 11:51] (current)
master [I/O Consideration]
Line 5: Line 5:
 Download: Download:
  
-  * +  * DB2 InfoSphere Warehouse Edition, 
- +  * DB2 Spatial Extender, 
-DB2 InfoSphere Warehouse Edition, +  * DB2 Universal Fixpack.
- +
-  * +
- +
-DB2 Spatial Extender, +
- +
-  * +
- +
-DB2 Universal Fixpack.+
  
 DB2 Spatial extender has to be the same edition as DB2 InfoSphere, e.g., 9.7.0. Suppose binaries are unzipped as follows: DB2 Spatial extender has to be the same edition as DB2 InfoSphere, e.g., 9.7.0. Suppose binaries are unzipped as follows:
Line 33: Line 25:
 ==== Layout ==== ==== Layout ====
  
-Two nodes: **node40** and **node41**+Two nodes: **node40** ​ and **node41**
  
 Folders: Folders:
  
-node40: ''/​shared'':​ local, also exported as ''/​shared''​ on node41+node40: ''/​shared'':​ local, also exported as ''/​shared'' ​ on node41
  
 node41: ''/​db'':​ local, has access to shared folder ''/​shared''​ node41: ''/​db'':​ local, has access to shared folder ''/​shared''​
Line 47: Line 39:
 DB2 databases will be installed locally: DB2 databases will be installed locally:
  
-node40: ''/​shared/​database''​ (or soft link: ''/​db/​shared/​database''​)+node40: ''/​shared/​database'' ​ (or soft link: ''/​db/​shared/​database''​)
  
 node41: ''/​db/​shared/​database''​ node41: ''/​db/​shared/​database''​
  
-''/​data''​ will be for data+''/​data'' ​ will be for data
  
-''/​log''​ will be for log+''/​log'' ​ will be for log
  
 ===== Create DB2 Users ===== ===== Create DB2 Users =====
Line 59: Line 51:
 Create db2 users on both master and slave nodes, with same login information. The users should be local users and not NIS or NIS+ users. db2admin user(dasuser1) should have local home, and db2inst1 and db2fenc1 should have shared home folders. Create db2 users on both master and slave nodes, with same login information. The users should be local users and not NIS or NIS+ users. db2admin user(dasuser1) should have local home, and db2inst1 and db2fenc1 should have shared home folders.
  
-<​code>​''​sudo /​usr/​sbin/​groupadd -g 999 db2iadm1+<​code>​ 
 +sudo /​usr/​sbin/​groupadd -g 999 db2iadm1
 sudo /​usr/​sbin/​groupadd -g 998 db2fadm1 sudo /​usr/​sbin/​groupadd -g 998 db2fadm1
 sudo /​usr/​sbin/​groupadd -g 997 dasadm1 sudo /​usr/​sbin/​groupadd -g 997 dasadm1
Line 65: Line 58:
 sudo /​usr/​sbin/​useradd -u 1103 -g db2fadm1 -m -d /​shared/​database/​db2fenc1 db2fenc1 sudo /​usr/​sbin/​useradd -u 1103 -g db2fadm1 -m -d /​shared/​database/​db2fenc1 db2fenc1
 sudo /​usr/​sbin/​useradd -u 1102 -g dasadm1 -m -d /​home/​dasusr1 dasusr1 sudo /​usr/​sbin/​useradd -u 1102 -g dasadm1 -m -d /​home/​dasusr1 dasusr1
-''​ 
 </​code>​ </​code>​
  
-Then ''​sudo passwd''​ to change passwds for all users.+Then ''​sudo passwd'' ​ to change passwds for all users.
  
 ===== Setup DB2 ===== ===== Setup DB2 =====
Line 80: Line 72:
 Add license: Add license:
  
-<​code>​''​/​shared/​database/​ibm/​db2/​V9.7/​adm:​+<​code>​ 
 +/​shared/​database/​ibm/​db2/​V9.7/​adm:​
 sudo ./db2licm -l sudo ./db2licm -l
-''​ 
 </​code>​ </​code>​
  
Line 91: Line 83:
 ===== Setup Public Key Authentication (Avoid Login between Nodes) ===== ===== Setup Public Key Authentication (Avoid Login between Nodes) =====
  
-Run as //​db2instance//​ user:+Run as //​db2instance// ​ user:
  
 ''​su -l db2inst1''​ ''​su -l db2inst1''​
Line 97: Line 89:
 Do this on both master/​slave nodes: Do this on both master/​slave nodes:
  
-<​code>​''​ssh-keygen -t rsa+<​code>​ 
 +ssh-keygen -t rsa
 cd ~/.ssh cd ~/.ssh
 mv id_rsa identity mv id_rsa identity
 chmod 600 identity chmod 600 identity
-cat id_rsa.pub >> authorized_keys+cat id_rsa.pub>>​ authorized_keys
 chmod 644 authorized_keys chmod 644 authorized_keys
 rm id_rsa.pub rm id_rsa.pub
  
-ssh-keyscan -t rsa node40 node40.clus.cci.emory.edu,​192.168.1.159 >> ~/​.ssh/​known_hosts +ssh-keyscan -t rsa node40 node40.clus.cci.emory.edu,​192.168.1.159>>​ ~/​.ssh/​known_hosts 
-ssh-keyscan -t rsa node41 node41.clus.cci.emory.edu,​192.168.1.160 >> ~/​.ssh/​known_hosts +ssh-keyscan -t rsa node41 node41.clus.cci.emory.edu,​192.168.1.160>>​ ~/​.ssh/​known_hosts
-''​+
 </​code>​ </​code>​
  
 Do this on master node: Do this on master node:
  
-<​code>​''​sudo nano /​etc/​ssh/​sshd_config (adding entry and update entry):+<​code>​ 
 +sudo nano /​etc/​ssh/​sshd_config (adding entry and update entry):
  
 # HostbasedAuthentication no # HostbasedAuthentication no
 -> ->
 HostbasedAuthentication yes HostbasedAuthentication yes
-''​ 
 </​code>​ </​code>​
  
Line 126: Line 118:
 Add following: Add following:
  
-<​code>​''​node40+<​code>​ 
 +node40
 node40.clus.cci.emory.edu node40.clus.cci.emory.edu
 node41 node41
Line 134: Line 127:
 pais2 pais2
 pais2.cci.emory.edu pais2.cci.emory.edu
-''​ 
 </​code>​ </​code>​
  
 Go to: ''/​etc/​ssh'':​ Go to: ''/​etc/​ssh'':​
  
-<​code>​''​sudo ssh-keyscan -t rsa node40 node40.clus.cci.emory.edu,​192.168.1.159 >> /​tmp/​ssh_known_hosts +<​code>​ 
-sudo ssh-keyscan -t rsa node41 node41.clus.cci.emory.edu,​192.168.1.160 >> /​tmp/​ssh_known_hosts+sudo ssh-keyscan -t rsa node40 node40.clus.cci.emory.edu,​192.168.1.159>>​ /​tmp/​ssh_known_hosts 
 +sudo ssh-keyscan -t rsa node41 node41.clus.cci.emory.edu,​192.168.1.160>>​ /​tmp/​ssh_known_hosts
 sudo cp /​tmp/​ssh_known_hosts . sudo cp /​tmp/​ssh_known_hosts .
-''​ 
 </​code>​ </​code>​
  
 On slave nodes (node41): On slave nodes (node41):
  
-<​code>​''​sudo nano /​etc/​ssh/​ssh_config+<​code>​ 
 +sudo nano /​etc/​ssh/​ssh_config
 #​HostbasedAuthentication no #​HostbasedAuthentication no
 -> ->
Line 153: Line 146:
 #added: #added:
 EnableSSHKeysign yes EnableSSHKeysign yes
-''​ 
 </​code>​ </​code>​
  
Line 172: Line 164:
 Stop fault monitor daemon (db2fmcd): Stop fault monitor daemon (db2fmcd):
  
-<​code>​''​/​sqllib/​bin:​+<​code>​ 
 +/​sqllib/​bin:​
 sudo ./db2fmcu -d sudo ./db2fmcu -d
  
Line 178: Line 171:
 or db2val -o or db2val -o
 (missing lib) (missing lib)
- 
-''​ 
 </​code>​ </​code>​
  
Line 190: Line 181:
 Find error, fix it: Find error, fix it:
  
-<​code>​''​db2set DB2COMM = TCPIP+<​code>​ 
 +db2set DB2COMM = TCPIP
 db2set DB2_ENABLE_LDAP=no db2set DB2_ENABLE_LDAP=no
 db2set -all DB2COMM db2set -all DB2COMM
-''​ 
 </​code>​ </​code>​
  
Line 202: Line 193:
 ===== Install DB2 Spatial Extender on Master Node ===== ===== Install DB2 Spatial Extender on Master Node =====
  
-''​db2stop''​ first.+''​db2stop'' ​ first.
  
 Setup DB2 Spatial Extender on master node Setup DB2 Spatial Extender on master node
Line 230: Line 221:
 db2 instance update (master node): db2 instance update (master node):
  
-<​code>​''​cd /​shared/​database/​ibm/​db2/​V9.7/​instance/​+<​code>​ 
 +cd /​shared/​database/​ibm/​db2/​V9.7/​instance/​
 sudo ./db2iupdt db2inst1 sudo ./db2iupdt db2inst1
-''​ 
 </​code>​ </​code>​
  
Line 241: Line 232:
 16 cores, 15 partition per core 16 cores, 15 partition per core
  
-Create a file ''​db2nodes.cfg''​ in ''/​home/​db2inst1/​sqllib'',​ with following entries:+Create a file ''​db2nodes.cfg'' ​ in ''/​home/​db2inst1/​sqllib'',​ with following entries:
  
-<​code>​''​db2nodes.cfg:​+<​code>​ 
 +db2nodes.cfg:​
 0 node40 0 0 node40 0
 1 node40 1 1 node40 1
Line 274: Line 266:
 28 node41 13 28 node41 13
 29 node41 14 29 node41 14
-''​ 
 </​code>​ </​code>​
  
Line 283: Line 274:
 Look at system resources: Look at system resources:
  
-''​numactl ​--hardware''​+''​numactl ​hardware''​
  
-===== IO Consideration =====+===== I/O Consideration =====
  
 DB2 doc: "​Optimizing table space performance when data is on RAID devices"​ DB2 doc: "​Optimizing table space performance when data is on RAID devices"​
Line 295: Line 286:
 Chunk size is also "​segment size of disk". Chunk size is also "​segment size of disk".
  
-RAID5 chunk size: 64K +  * RAID5 chunk size: 64K 
- +  ​* ​Disk segment size: 64K 
-Disk segment size: 64K +  ​* ​RAID5 Strip size: 320K
- +
-RAID5 Strip size: 320K+
  
 The DB2 extent size for a tablespace is the amount of data that the database manager writes to a container before writing to the next container. The DB2 extent size for a tablespace is the amount of data that the database manager writes to a container before writing to the next container.
Line 305: Line 294:
 The extent size should be a multiple of the underlying segment size of the disks. Also a multiply of the page size. The extent size should be a multiple of the underlying segment size of the disks. Also a multiply of the page size.
  
-Page size (large table): 32KB +  * Page size (large table): 32KB 
- +  ​* ​Extent size = 320K x N 
-Extent size = 320K x N +  ​* ​Prefetch size = 320K x N
- +
-Prefetch size = 320K x N+
  
 Check raid level: Check raid level:
Line 323: Line 310:
 After DB2 is created, a PAIS database needs to be created. This includes enabling spatial database, generating tables, creating user-defined functions, and assign users with proper privileges, which are shown below. A set of SQL scripts are created to facilitate this. After DB2 is created, a PAIS database needs to be created. This includes enabling spatial database, generating tables, creating user-defined functions, and assign users with proper privileges, which are shown below. A set of SQL scripts are created to facilitate this.
  
-Create table spaces by run the SQL script ''​createdb_dpf.sql''​ (download here: [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​createdb_dpf.sql|createdb_dpf.sql]]) +Create table spaces by run the SQL script ''​createdb_dpf.sql'' ​ (download here: [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​createdb_dpf.sql|createdb_dpf.sql]])
- +
-  * +
- +
-This script is used to create the database, alter configurations for the database, create buffer pools, and create tables spaces to be used by the database. +
- +
-  * +
- +
-The following information needs to be specified to your real server environment:​ +
- +
-      * +
- +
-Set database name: replace TESTDB as the real database name; +
- +
-      * +
- +
-Set database storage path; +
- +
-      * +
- +
-Set database DBPATH; +
- +
-      * +
- +
-Set database newlogpath;​ +
- +
-  * +
- +
-Database parameters may also need to be adjusted based on server resources. +
- +
-  * +
- +
-The script needs to be executed by DB2 instance user (e.g., ''​db2inst1''​). +
- +
-  * +
- +
-Run the script as ''​db2 -tf createdb_dpf.sql''​. +
- +
-  * +
- +
-Note:+
  
 +  * This script is used to create the database, alter configurations for the database, create buffer pools, and create tables spaces to be used by the database.
 +  * The following information needs to be specified to your real server environment:​
 +      * Set database name: replace TESTDB as the real database name;
 +      * Set database storage path;
 +      * Set database DBPATH;
 +      * Set database newlogpath;
 +  * Database parameters may also need to be adjusted based on server resources.
 +  * The script needs to be executed by DB2 instance user (e.g., ''​db2inst1''​).
 +  * Run the script as ''​db2 -tf createdb_dpf.sql''​.
 +  * Note:
       * Set the proper permission to this current DB2 instance user and make all paths you specified accessible to him.       * Set the proper permission to this current DB2 instance user and make all paths you specified accessible to him.
-      * Be patient and wait for a long time to build up the database ​(^_^).+      * Be patient and wait for a long time to build up the database.
  
 Enable the Spatial Extender to the database. Enable the Spatial Extender to the database.
  
-Use the command ''​db2se enable_db TESTDB''​ (''​TESTDB''​ is your database'​s name).+Use the command ''​db2se enable_db TESTDB'' ​ (''​TESTDB'' ​ is your database'​s name).
  
 ===== Create Schema ===== ===== Create Schema =====
Line 378: Line 335:
 Create Tables by running these SQL scripts below (notice: change to your own database name in those SQL scripts): Create Tables by running these SQL scripts below (notice: change to your own database name in those SQL scripts):
  
-  * +  * ''​db2 -tf create_srs_utm16n.sql''​ 
- +  * ''​db2 -tf create_tables_dpf_withCentroid.sql'' ​ Scripts can be downloaded here: [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​create_srs_utm16n.sql|create_srs_utm16n.sql]],​ [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​create_tables_dpf_withCentroid.sql|create_tables_dpf_withCentroid.sql]]
-''​db2 -tf create_srs_utm16n.sql''​ +
-  * +
- +
-''​db2 -tf create_tables_dpf_withCentroid.sql''​ +
-Scripts can be downloaded here: [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​create_srs_utm16n.sql|create_srs_utm16n.sql]],​ [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​create_tables_dpf_withCentroid.sql|create_tables_dpf_withCentroid.sql]] +
 ==== Create Keys and Indexes ==== ==== Create Keys and Indexes ====
  
 Create keys and Indexes by running these SQL scripts below: Create keys and Indexes by running these SQL scripts below:
  
-  * +  * ''​db2 -tf keys.sql''​ 
- +  * ''​db2 -tf index.sql'' ​ Scripts can be downloaded here: [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​keys.sql|keys.sql]],​ [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​index.sql|index.sql]]
-''​db2 -tf keys.sql''​ +
-  * +
- +
-''​db2 -tf index.sql''​ +
-Scripts can be downloaded here: [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​keys.sql|keys.sql]],​ [[https://​github.com/​EmoryUniversity/​pais/​blob/​master/​setup/​dpf/​index.sql|index.sql]] +
 ==== Materialize (Replicate) SRS table ==== ==== Materialize (Replicate) SRS table ====