检查系统环境
[root@*****1 ~]# rpm -qa|grep ksh
ksh-20100202-1.el5
[root@*****1 ~]# rpm -qa|grep nfs
system-config-nfs-1.3.23-1.el5
nfs-utils-lib-1.0.8-7.6.el5
nfs-utils-1.0.9-44.el5
[root@*****1 ~]# rpm -qa|grep -i libaio
libaio-0.3.106-5
libaio-0.3.106-5
find / -name libaio.so.1
vi /etc/sysctl.conf
kernel.msgmni = 16384
kernel.sem = 250 256000 32 4096
sysctl -p
[root@*****1 ~]# find / -name libstdc++.so.6
/usr/lib64/libstdc++.so.6
/usr/lib/libstdc++.so.6
[root@*****1 ~]#vi /etc/security/limits.conf
* soft nofile 10240
0. 第一先检查/etc/hosts文件，下面是两台服务器上的/etc/hosts文件配置：
[root@*****1 instance]# more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
10.29.19.40 *****1
10.29.19.41 *****2
[root@*****2 ~]# more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
10.29.19.40 *****1
10.29.19.41 *****2
1. 安装操作系统，两台服务器的hostname分别为*****1, *****2其中在
*****1上面配置/db2home文件系统，在*****2上面去mount /db2home
这个NFS文件系统。
2. NFS配置如下：
在*****1上面做如下动作：
#vi /etc/exports
/db2home 10.29.19.41(rw,sync,no_root_squash)
#chkconfig --level 5 nfs on
#chkconfig --level 3 nfs on
#chkconfig --list|grep portmap
#service nfs start
#showmount -e
#chmod 777 /db2home
在*****2上面做如下动作：
#vi /etc/rc.local
#mount 10.29.19.40:/db2home /db2home
#/etc/rc.local
3. 分别在两台服务器上面安装db2
上传db2 v9.7的安装介质db2_ese_9.7.0.0_linux_x86_64.iso到服务器的/softinstall上面
#mount -t iso9660 -o loop db2_ese_9.7.0.0_linux_x86_64.iso /mnt/cdrom
#cd /mnt/cdrom
#./db2_install
安装到默认路径/opt/ibm/db2/V9.7下面，安装类型选择ESE
给db2打补丁包
上传补丁包v9.7fp3_25384_linuxx64_server.tar.gz
解压并执行
#./installFixPack
需要先将instance shutdown
4. 在两台服务器上面创建相应的用户及用户组
#groupadd -g 999 db2iadm1
#groupadd -g 998 db2fadm1
#groupadd -g 997 dasadm1
#useradd -u 2014 -g db2iadm1 -m -d /db2home/db2inst1 db2inst1
#useradd -u 2013 -g db2fadm1 -m -d /db2home/db2fenc1 db2fenc1
#useradd -u 2012 -g dasadm1 -m -d /home/dasusr1 dasusr1
给三个用户分别设置密码
PS:生产环境上将不创建dasusr1, 因为db2 v9.7官方建议不再使用管理客户端。
#su - db2inst1
$vi .profile
. /db2home/db2inst1/sqllib/db2profile
5. 在*****1上面创建实例db2inst1
#cd /opt/ibm/db2/V9.7/instance/
./db2icrt -s ese -u db2fenc1 db2inst1
6. 配置db2inst1用户下的ssh信任
在两台服务器上编辑/etc/hosts文件以增加两台服务器IP和hostname
#vi /etc/hosts
10.29.19.40 *****1
10.29.19.41 *****2
在*****1上面做如下动作：
#su - db2inst1
$cd $HOME
$ssh-keygen -t rsa
//这里不需要输入密码再次加密
$ssh-keygen -t dsa
//这里不需要输入密码再次加密
$cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
$cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
$ssh *****2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
//这里会提示输入db2inst1在server2上的登录密码
$ssh *****2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
$ssh *****2 date
7. 更改db2nodes.cfg文件，内容如下：
0 *****1 0
1 *****1 1
2 *****2 0
3 *****2 1
8. 配置端口信息
在*****1的/etc/services文件里面新增如下内容：
DB2c_db2inst1 50000/tcp
DB2_db2inst1 60000/tcp
DB2_db2inst1_1 60001/tcp
DB2_db2inst1_2 60002/tcp
DB2_db2inst1_END 60003/tcp
再检查*****2的/etc/services文件的内容，看是否如所示：
DB2_db2inst1 60000/tcp
DB2_db2inst1_1 60001/tcp
DB2_db2inst1_2 60002/tcp
DB2_db2inst1_END 60003/tcp
只在*****1上更改数据库dbm的参数，以及db2set参数
$db2 update dbm cfg using SVCENAME DB2c_db2inst1
$db2set DB2COMM=TCPIP
$db2set DB2RSHCMD=/usr/bin/ssh
这里需要先设置DB2RSHCMD这个变量，再设置DB2COMM等变量
9. 安装license文件
检查license的内容
$db2licm -l
安装license
$db2licm -a db2ese_c.lic
10. 创建sample数据库
这步并非必要，仅仅是创建自带的sample数据库
$db2stop
$db2start
$cd /db2home/db2inst1/sqllib/bin
$./db2sampl
$db2 connect to sample
$db2 "select * from sales"
11. 创建数据库
$db2 "CREATE DATABASE testdb on /db2data/database USING CODESET GBK TERRITORY CN"
12. 创建节点组
首先可以查询一下目前数据库环境中的partition groups有哪些，使用下面命令：
$db2 connect to testdb
$db2 list database partition groups show detail
创建下面的partition group
$db2 "CREATE DATABASE PARTITION GROUP dpgods ON DBPARTITIONNUMS (0)"
$db2 "CREATE DATABASE PARTITION GROUP dpgedw ON DBPARTITIONNUMS (0)"
$db2 "CREATE DATABASE PARTITION GROUP dpgddw ON DBPARTITIONNUMS (2)"
再次查看数据库内所有的partition groups时，如下：
[db2inst1@*****1 ~]$ db2 list database partition groups show detail
DATABASE PARTITION GROUP PMAP_ID DATABASE PARTITION NUMBER IN_USE
-------------------------- ------- ---------------------------- ---------
DPGDDW 5 2 Y
DPGEDW 4 0 Y
DPGODS 3 0 Y
IBMCATGROUP 0 0 Y
IBMDEFAULTGROUP 1 0 Y
IBMDEFAULTGROUP 1 1 Y
IBMDEFAULTGROUP 1 2 Y
IBMDEFAULTGROUP 1 3 Y
13. 创建缓冲池
$db2 connect to testdb
$db2 "ALTER BUFFERPOOL IBMDEFAULTBP SIZE 2000"
$db2 "CREATE BUFFERPOOL bp32k ALL DBPARTITIONNUMS SIZE 983040 PAGESIZE 32K"
14. 创建表空间
$db2 "CREATE LARGE TABLESPACE tbs32kdpgods IN DATABASE PARTITION GROUP dpgods pagesize 32k MANAGED BY DATABASE USING (file '/db2data/dpgtbs32k/dpgtbs32k.data' 500M) ON DBPARTITIONNUM(0) bufferpool bp32k"
$db2 "CREATE LARGE TABLESPACE tbs32kdpgedw IN DATABASE PARTITION GROUP dpgedw pagesize 32k MANAGED BY DATABASE USING (file '/db2data/edw/dpgtbs32k/dpgtbs32k.data' 500M) ON DBPARTITIONNUM(0) bufferpool bp32k"
$db2 "CREATE LARGE TABLESPACE tbs32kdpgddw IN DATABASE PARTITION GROUP dpgddw pagesize 32k MANAGED BY DATABASE USING (file '/db2data/ddw/dpgtbs32k/dpgtbs32k.data' 500M) ON DBPARTITIONNUM(2) bufferpool bp32k"
$db2 "CREATE LARGE TABLESPACE idx32kdpgods IN DATABASE PARTITION GROUP dpgods pagesize 32k MANAGED BY DATABASE USING (file '/db2data/dpgidx32k/dpgidx32k.data' 100M) ON DBPARTITIONNUM(0) bufferpool bp32k"
$db2 "CREATE LARGE TABLESPACE idx32kdpgedw IN DATABASE PARTITION GROUP dpgedw pagesize 32k MANAGED BY DATABASE USING (file '/db2data/edw/dpgidx32k/dpgidx32k.data' 100M) ON DBPARTITIONNUM(0) bufferpool bp32k"
$db2 "CREATE LARGE TABLESPACE idx32kdpgddw IN DATABASE PARTITION GROUP dpgddw pagesize 32k MANAGED BY DATABASE USING (file '/db2data/ddw/dpgidx32k/dpgidx32k.data' 100M) ON DBPARTITIONNUM(2) bufferpool bp32k"
将下面语句复制到文件中，然后db2 -tvf crt_tbs.sql 执行这个脚本
CREATE LARGE TABLESPACE tbs32kods IN DATABASE PARTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASE
USING (file '/db2data/ods/tbs32k/tbs32k_0.data' 1000M) ON DBPARTITIONNUM(0)
USING (file '/db2data/ods/tbs32k/tbs32k_1.data' 1000M) ON DBPARTITIONNUM(1)
USING (file '/db2data/ods/tbs32k/tbs32k_2.data' 1000M) ON DBPARTITIONNUM(2)
USING (file '/db2data/ods/tbs32k/tbs32k_3.data' 1000M) ON DBPARTITIONNUM(3)
autoresize yes bufferpool bp32k;
CREATE LARGE TABLESPACE tbs32kedw IN DATABASE PARTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASE
USING (file '/db2data/edw/tbs32k/tbs32k_0.data' 1000M) ON DBPARTITIONNUM(0)
USING (file '/db2data/edw/tbs32k/tbs32k_1.data' 1000M) ON DBPARTITIONNUM(1)
USING (file '/db2data/edw/tbs32k/tbs32k_2.data' 1000M) ON DBPARTITIONNUM(2)
USING (file '/db2data/edw/tbs32k/tbs32k_3.data' 1000M) ON DBPARTITIONNUM(3)
autoresize yes bufferpool bp32k;
CREATE LARGE TABLESPACE tbs32kddw IN DATABASE PARTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASE
USING (file '/db2data/ddw/tbs32k/tbs32k_0.data' 1000M) ON DBPARTITIONNUM(0)
USING (file '/db2data/ddw/tbs32k/tbs32k_1.data' 1000M) ON DBPARTITIONNUM(1)
USING (file '/db2data/ddw/tbs32k/tbs32k_2.data' 1000M) ON DBPARTITIONNUM(2)
USING (file '/db2data/ddw/tbs32k/tbs32k_3.data' 1000M) ON DBPARTITIONNUM(3)
autoresize yes bufferpool bp32k;
CREATE LARGE TABLESPACE idx32kods IN DATABASE PARTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASE
USING (file '/db2data/ods/idx32k/idx32k_0.data' 100M) ON DBPARTITIONNUM(0)
USING (file '/db2data/ods/idx32k/idx32k_1.data' 100M) ON DBPARTITIONNUM(1)
USING (file '/db2data/ods/idx32k/idx32k_2.data' 100M) ON DBPARTITIONNUM(2)
USING (file '/db2data/ods/idx32k/idx32k_3.data' 100M) ON DBPARTITIONNUM(3)
autoresize yes bufferpool bp32k;
CREATE LARGE TABLESPACE idx32kedw IN DATABASE PARTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASE
USING (file '/db2data/edw/idx32k/idx32k_0.data' 100M) ON DBPARTITIONNUM(0)
USING (file '/db2data/edw/idx32k/idx32k_1.data' 100M) ON DBPARTITIONNUM(1)
USING (file '/db2data/edw/idx32k/idx32k_2.data' 100M) ON DBPARTITIONNUM(2)
USING (file '/db2data/edw/idx32k/idx32k_3.data' 100M) ON DBPARTITIONNUM(3)
autoresize yes bufferpool bp32k;
CREATE LARGE TABLESPACE idx32kddw IN DATABASE PARTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASE
USING (file '/db2data/ddw/idx32k/idx32k_0.data' 100M) ON DBPARTITIONNUM(0)
USING (file '/db2data/ddw/idx32k/idx32k_1.data' 100M) ON DBPARTITIONNUM(1)
USING (file '/db2data/ddw/idx32k/idx32k_2.data' 100M) ON DBPARTITIONNUM(2)
USING (file '/db2data/ddw/idx32k/idx32k_3.data' 100M) ON DBPARTITIONNUM(3)
autoresize yes bufferpool bp32k;
CREATE SYSTEM TEMPORARY TABLESPACE tmp32k
PAGESIZE 32k
MANAGED BY SYSTEM
USING ('/db2data/database/db2inst1/NODE0000/SQL00001/systmptbs_32k') ON DBPARTITIONNUM (0)
USING ('/db2data/database/db2inst1/NODE0001/SQL00001/systmptbs_32k') ON DBPARTITIONNUM (1)
USING ('/db2data/database/db2inst1/NODE0002/SQL00001/systmptbs_32k') ON DBPARTITIONNUM (2)
USING ('/db2data/database/db2inst1/NODE0003/SQL00001/systmptbs_32k') ON DBPARTITIONNUM (3)
BUFFERPOOL bp32k;
CREATE USER TEMPORARY TABLESPACE usrtmp32k
PAGESIZE 32k
MANAGED BY DATABASE
USING (FILE '/db2data/database/db2inst1/NODE0000/SQL00001/usrtmptbs_32k/tbs32k_0.data' 100M) ON DBPARTITIONNUM(0)
USING (FILE '/db2data/database/db2inst1/NODE0001/SQL00001/usrtmptbs_32k/tbs32k_1.data' 100M) ON DBPARTITIONNUM(1)
USING (FILE '/db2data/database/db2inst1/NODE0002/SQL00001/usrtmptbs_32k/tbs32k_2.data' 100M) ON DBPARTITIONNUM(2)
USING (FILE '/db2data/database/db2inst1/NODE0003/SQL00001/usrtmptbs_32k/tbs32k_3.data' 100M) ON DBPARTITIONNUM(3)
bufferpool bp32k;
15. 更改数据库和实例的配置
$db2_all "db2 update db cfg for testdb using LOGPRIMARY 16"
$db2_all "db2 update db cfg for testdb using LOGSECOND 4"
$db2_all "db2 update db cfg for testdb using LOGFILSIZ 130048"
$db2_all "db2 update db cfg for testdb using SORTHEAP 5120"
$db2_all "db2 update db cfg for testdb using LOCKLIST 204800"
$db2_all "db2 update db cfg for testdb using MAXLOCKS 30"
$db2_all "db2 update db cfg for testdb using LOGBUFSZ 20000"
$db2 update dbm cfg using sheapthres 1048576
$db2 update dbm cfg using FCM_NUM_BUFFERS 65536
$db2 update dbm cfg using JAVA_HEAP_SZ 8192
$db2 update dbm cfg using RESTBUFSZ 20000
$db2 update dbm cfg using RESTBUFSZ 15000
$db2 update dbm cfg using AGENT_STACK_SZ 10240
$db2 update dbm cfg using QUERY_HEAP_SZ 51200
$db2 update dbm cfg using ASLHEAPSZ 10240
16. 创建分区表示例：
CREATE TABLE SD_CO_LINE (
CO_NUM VARCHAR(30) NOT NULL ,
LINE_NUM INTEGER NOT NULL WITH DEFAULT 0 ,
ITEM_ID VARCHAR(30) NOT NULL ,
UM_ID VARCHAR(30) WITH DEFAULT '0' ,
QTY_NEED DECIMAL(18,6) WITH DEFAULT 0 ,
QTY_VFY DECIMAL(18,6) WITH DEFAULT 0 ,
QTY_ORD DECIMAL(18,6) WITH DEFAULT 0 ,
PRI DECIMAL(18,6) WITH DEFAULT 0 ,
PRI3 DECIMAL(18,6) WITH DEFAULT 0 ,
AMT DECIMAL(18,6) WITH DEFAULT 0 ,
RET_AMT DECIMAL(18,6) WITH DEFAULT 0 ,
NOTE VARCHAR(100) )
PARTITIONING KEY (CO_NUM) USING HASHING
IN tbs32kods INDEX IN idx32kods;
ALTER TABLE SD_CO_LINE
ADD CONSTRAINT COLINE_PK PRIMARY KEY
(CO_NUM,ITEM_ID);
17. 创建数据库用户，并赋权
...
18. DPF数据库管理
启动实例
#su - db2inst1
$db2start
激活数据库
$db2 activate db testdb //这里会激活数据库的所有节点
查看各分区上已激活的数据库
$db2 list active databases at dbpartitionnum 0
$db2 list active databases at dbpartitionnum 1
$db2 list active databases at dbpartitionnum 2
$db2 list active databases at dbpartitionnum 3
$db2_all "db2pd -d testdb -bufferpools" //数据库需处于激活状态
挂起数据库
$db2 deactivate db testdb //只能在主节点上使用改命令，才能挂起所有节点的数据库，如果数据库节点上面还有连接的话，是无法挂起数据库的
查看各数据库节点上的应用程序连接
$db2 list applications at dbpartitionnum 0
$db2 list applications at dbpartitionnum 1
$db2 list applications at dbpartitionnum 2
$db2 list applications at dbpartitionnum 3
查看各数据库节点的表空间相关信息
$db2_all "db2pd -d testdb -tablespaces"
$db2_all "db2 list tablespaces show detail"
关闭实例
$db2stop force
阅读(3947) | 评论(1) | 转发(0) |
