DB2 DPF安装过程

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:571

说明:
1、需要安装好虚拟机,哪个版本不重要
2、下面用到的命令如果是以#开头的表示是root用户
3、我安装了两个实例,其中普通实例用户为db2inst1 ,普通实例是通过图形化界面安装的,当然你也可以命令行安装,我有另一个文档《centos 6.2下安装db2 V9.7.txt》
节点数据库实例为db2dpf1,是通过命令行安装的。
4、整个过程有参考网上的一些大牛的脚本。

一、配置
1、修改 /etc/hosts文件
192.168.1.181 vmac1
192.168.1.183 vmac2
2、配置NFS服务
2.1 检查NFS服务
#showmount -e
如果出现提示:
clnt_create: RPC: Program not registered
则表示未启动,一般默认是安装了的,如果没有安装则先安装:
#yum install nfs-utils
#yum install portmap
重启:
#/etc/rc.d/init.d/nfs restart
2.2 配置NFS服务器
在vmac1中配置nfs为自启动
#chkconfig --level 35 nfs on
在vmac1中创建/db2home目录
#mkdir /db2home
然后修改/etc/exports文件
添加:
/db2home vmac2(rw,sync,no_root_squash)
#/usr/sbin/exportfs -a
2.3 配置NFS客户机
#mkdir /db2home
#mount vmac1:/db2home /db2home
配置客户机nfs自动mount
[root@vmac2 db2home]# vi /etc/fstab
在文件最后增加 vmac1:/db2home /db2home nfs defaults 0 0

3、创建所需用户和组
在vmac1上执行
#groupadd -g 999 db2iadm1
#groupadd -g 998 db2fadm1
#groupadd -g 997 dasadm1
#useradd -u 506 -g db2iadm1 -m -d /db2home/db2dpf1 db2dpf1
#password db2dpf1
在vmac2上执行
#groupadd -g 999 db2iadm1
#groupadd -g 998 db2fadm1
#groupadd -g 997 dasadm1
#useradd -u 506 -g db2iadm1 -d /db2home/db2dpf1 db2dpf1
#passwd db2dpf1
4、配置OpenSSH
用db2dpf1用户登陆,执行 ssh-keygen -t rsa 注意不要输入密码,直接敲两次回车即可
[db2dpf1@vmac1 ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/db2home/db2dpf1/.ssh/id_rsa):
Created directory '/db2home/db2dpf1/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /db2home/db2dpf1/.ssh/id_rsa.
Your public key has been saved in /db2home/db2dpf1/.ssh/id_rsa.pub.
The key fingerprint is:
89:95:22:b1:5c:25:53:f1:bc:9f:c4:51:35:9f:0e:e2 db2dpf1@vmac1
The key's randomart image is:
+--[ RSA 2048]----+
| . +o+. .o.|
| . + o + . +|
| + . o o... ..|
| . + ..o..o |
| . S .Eo . |
| o . |
| o |
| |
| |
+-----------------+
然后执行下面语句
修改权限
[db2dpf1@vmac1 ~]$ chmod 700 /db2home/db2dpf1/.ssh
[db2dpf1@vmac1 ~]$ cd /db2home/db2dpf1/.ssh
[db2dpf1@vmac1 .ssh]$ mv id_rsa.pub authorized_keys

然后在主机上连接客户机
[db2dpf1@vmac1 .ssh]$ssh vmac2
The authenticity of host 'vmac2 (192.168.1.183)' can't be established.
RSA key fingerprint is 1c:1d:30:fc:a9:57:65:57:3c:d6:b0:b9:05:99:1f:f3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'vmac2,192.168.1.183' (RSA) to the list of known hosts.
这样算成功
5、创建DB2实例 db2dpf1
在主机vmac1上建立实例db2dpf1
5.1 首先要安装数据库
首先将安装文件传上去,安装和开启FTP权限:#yum install vsftpd --安装FTP服务器,有些系统默认是装了的,也可以在安装操作系统的时候装
开启FTP:#service vsftpd start?或 #./etc/init.d/vsftpd start

5.2 上传文件:v9.7_linuxx64_server.tar.gz
解压
[root@vmac1 db2temp]#tar -zxvf v9.7_linuxx64_server.tar.gz
然后安装libstdc++.so.6 这个地方可能需要安装很多包,都可以通过yum安装
[root@vmac2 grid]#yum install libstdc++.so.6

5.3 图形界面安装数据库

5.4 增加认证:
cd /opt/ibm/db2/V9.7/adm
./db2licm -a /db2temp/db2ese_c.lic

5.5 安装SAMPLE数据库 不是必须,因为等一下要在新建的实例上建样例数据库
$su - db2inst1
$cd /home/db2inst1/sqllib/bin
$./db2sampl

5.6 配置环境变量,在vmac1操作
#su - db2dpf1
$vi .bash_profile
. /db2home/db2dpf1/sqllib/db2profile

5.7 为了使用db2_all命令,需要设置环境变量
#su - db2dpf1
$vi /db2home/db2dpf1/.profile
内容为:
/db2home/db2dpf1/sqllib/db2profile

5.8 创建DPF实例:
su - root
[root@vmac1 ~]# cd /opt/ibm/db2/V9.7/instance/
[root@vmac1 instance]# ./db2icrt -s ese -u db2fenc1 db2dpf1

5.9 配置SSH
su - db2dpf1
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 vmac2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh vmac2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

ssh vmac2 date

5.10 配置节点
cd /db2home/db2dpf1/sqllib
vi db2nodes.cfg
更改db2nodes.cfg文件
0 vmac1 0
1 vmac1 1
2 vmac2 0
3 vmac2 1

5.11 配置端口信息
用root用户在vmac1的/etc/services文件里面新增如下内容:
因为我另外建了一个普通实例,50000端口已经给普通实例用了,所以我的节点实例我用70000和80000端口
# vi + /etc/services

DB2c_db2dpf1 70000/tcp
DB2_db2dpf1 80000/tcp
DB2_db2dpf1_1 80001/tcp
DB2_db2dpf1_2 80002/tcp
DB2_db2dpf1_END 80003/tcp

再检查vmac2的/etc/services文件的内容,看是否如所示:
DB2c_db2dpf1 70000/tcp
DB2_db2dpf1 80000/tcp
DB2_db2dpf1_1 80001/tcp
DB2_db2dpf1_2 80002/tcp
DB2_db2dpf1_END 80003/tcp

只在vmac1上更改数据库dbm的参数,以及db2set参数
$db2 update dbm cfg using SVCENAME DB2c_db2dpf1

$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/db2dpf1/sqllib/bin
$./db2sampl
$db2 connect to sample
$db2 "select * from sales"

11. 创建数据库
[db2dpf1@vmac1 ~]$ mkdir -p /db2home/db2data/database
[db2dpf1@vmac1 ~]$ db2 "CREATE DATABASE testdb on /db2home/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 (2)"

##$db2 "CREATE DATABASE PARTITION GROUP dpgddw ON DBPARTITIONNUMS (2)"

再次查看数据库内所有的partition groups时,如下:
[ db2dpf1@vmac1 ~]$ 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" 默认为8M
$db2 "CREATE BUFFERPOOL bp32k ALL DBPARTITIONNUMS SIZE 20000 PAGESIZE 32K" --625M
--

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 ('/db2home/db2data/database/db2dpf1/NODE0000/SQL00001/systmptbs_32k') ON DBPARTITIONNUM (0)
USING ('/db2home/db2data/database/db2dpf1/NODE0001/SQL00001/systmptbs_32k') ON DBPARTITIONNUM (1)
USING ('/db2home/db2data/database/db2dpf1/NODE0002/SQL00001/systmptbs_32k') ON DBPARTITIONNUM (2)
USING ('/db2home/db2data/database/db2dpf1/NODE0003/SQL00001/systmptbs_32k') ON DBPARTITIONNUM (3)
BUFFERPOOL bp32k;

CREATE USER TEMPORARY TABLESPACE usrtmp32k
PAGESIZE 32k
MANAGED BY DATABASE
USING (FILE '/db2home/db2data/database/db2dpf1/NODE0000/SQL00001/usrtmptbs_32k/tbs32k_0.data' 100M) ON DBPARTITIONNUM(0)
USING (FILE '/db2home/db2data/database/db2dpf1/NODE0001/SQL00001/usrtmptbs_32k/tbs32k_1.data' 100M) ON DBPARTITIONNUM(1)
USING (FILE '/db2home/db2data/database/db2dpf1/NODE0002/SQL00001/usrtmptbs_32k/tbs32k_2.data' 100M) ON DBPARTITIONNUM(2)
USING (FILE '/db2home/db2data/database/db2dpf1/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

rah 命令
如 rah "date"

db2_all

设置协调节点:
db2 set client connect_dbpartitionnum 3

本文标签:
本文标题:DB2 DPF安装过程
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】