Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
pais:db2dpf2nodes [2016/08/04 18:49]
master
pais:db2dpf2nodes [2016/08/05 11:51] (current)
master [I/O Consideration]
Line 1: Line 1:
-===== DB2 DPF with two nodes =====+====== DB2 DPF with two nodes ======
  
 ===== Install Binaries ===== ===== Install Binaries =====
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 60: Line 52:
  
 <​code>​ <​code>​
-''​sudo /​usr/​sbin/​groupadd -g 999 db2iadm1+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 66: 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>​
  
Line 82: Line 73:
  
 <​code>​ <​code>​
-''​/​shared/​database/​ibm/​db2/​V9.7/​adm:​+/​shared/​database/​ibm/​db2/​V9.7/​adm:​
 sudo ./db2licm -l sudo ./db2licm -l
-''​ 
 </​code>​ </​code>​
  
Line 100: Line 90:
  
 <​code>​ <​code>​
-''​ssh-keygen -t rsa+ssh-keygen -t rsa
 cd ~/.ssh cd ~/.ssh
 mv id_rsa identity mv id_rsa identity
Line 110: Line 100:
 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>​
  
Line 116: Line 105:
  
 <​code>​ <​code>​
-''​sudo nano /​etc/​ssh/​sshd_config (adding entry and update entry):+sudo nano /​etc/​ssh/​sshd_config (adding entry and update entry):
  
 # HostbasedAuthentication no # HostbasedAuthentication no
 -> ->
 HostbasedAuthentication yes HostbasedAuthentication yes
-''​ 
 </​code>​ </​code>​
  
Line 131: Line 119:
  
 <​code>​ <​code>​
-''​node40+node40
 node40.clus.cci.emory.edu node40.clus.cci.emory.edu
 node41 node41
Line 139: Line 127:
 pais2 pais2
 pais2.cci.emory.edu pais2.cci.emory.edu
-''​ 
 </​code>​ </​code>​
  
Line 145: Line 132:
  
 <​code>​ <​code>​
-''​sudo ssh-keyscan -t rsa node40 node40.clus.cci.emory.edu,​192.168.1.159>>​ /​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 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>​
  
Line 154: Line 140:
  
 <​code>​ <​code>​
-''​sudo nano /​etc/​ssh/​ssh_config+sudo nano /​etc/​ssh/​ssh_config
 #​HostbasedAuthentication no #​HostbasedAuthentication no
 -> ->
Line 160: Line 146:
 #added: #added:
 EnableSSHKeysign yes EnableSSHKeysign yes
-''​ 
 </​code>​ </​code>​
  
Line 180: Line 165:
  
 <​code>​ <​code>​
-''​/​sqllib/​bin:​+/​sqllib/​bin:​
 sudo ./db2fmcu -d sudo ./db2fmcu -d
  
Line 186: Line 171:
 or db2val -o or db2val -o
 (missing lib) (missing lib)
- 
-''​ 
 </​code>​ </​code>​
  
Line 199: Line 182:
  
 <​code>​ <​code>​
-''​db2set DB2COMM = TCPIP+db2set DB2COMM = TCPIP
 db2set DB2_ENABLE_LDAP=no db2set DB2_ENABLE_LDAP=no
 db2set -all DB2COMM db2set -all DB2COMM
-''​ 
 </​code>​ </​code>​
  
Line 240: Line 222:
  
 <​code>​ <​code>​
-''​cd /​shared/​database/​ibm/​db2/​V9.7/​instance/​+cd /​shared/​database/​ibm/​db2/​V9.7/​instance/​
 sudo ./db2iupdt db2inst1 sudo ./db2iupdt db2inst1
-''​ 
 </​code>​ </​code>​
  
Line 254: Line 235:
  
 <​code>​ <​code>​
-''​db2nodes.cfg:​+db2nodes.cfg:​
 0 node40 0 0 node40 0
 1 node40 1 1 node40 1
Line 285: Line 266:
 28 node41 13 28 node41 13
 29 node41 14 29 node41 14
-''​ 
 </​code>​ </​code>​
  
Line 296: Line 276:
 ''​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 306: 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 316: 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 336: Line 312:
 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:​ 
-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. +      * Set database name: replace TESTDB as the real database name; 
- +      * Set database storage path; 
-  * +      * Set database DBPATH; 
- +      * Set database newlogpath;​ 
-The following information needs to be specified to your real server environment:​ +  * 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 database name: replace TESTDB as the real database name; +      * 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.
-  ​* +
- +
-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. +
-  * 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.
Line 389: 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 ====