suse linux上安装DB2 数据库手记.docx

1、suse linux上安装DB2 数据库手记suse linux上安装DB2 数据库手记因生产需要，在一台IBM X3850服务器上安装DB2数据库，下面是具体过程。软硬件系统配置:操作系统：suse enterprise 10 linux(64bit) sp3硬件配置：32G内存数据库软件：DB2 V9.7(64bit)一、检查所需的环境和软件包在 suse Linux 操作系统上安装DB2数据库之前，应确保系统满足必需的操作系统、硬件、软件和通信要求。DB2数据库产品有若干不同版本。某些DB2数据库产品和功能部件仅在特定操作系统上可用。这个可参阅IBM官方网站来获取每个操作系统上可用的 D

2、B2 数据库产品。先用rpm -qa查找所需的软件包，没有的包可用yast2 -i来安装。linux-uu1q:# rpm -qa|grep libaiolibaio-0.3.104-14.2libaio-devel-0.3.104-14.2linux-uu1q:# rpm -qa|grep libstdclibstdc+-devel-4.1.2_20070115-0.29.6libstdc+-4.1.2_20070115-0.29.6linux-uu1q:# rpm -qa|grep nfs-utilsnfs-utils-1.0.7-36.36.47linux-uu1q:/home/serv

3、er # rpm -qa |grep compat-libstdccompat-libstdc+-33-3.2.3-61（检查是否有libstdc+.so.5linux-uu1q:/usr/lib64 # ls -l libstdc+.so.*lrwxrwxrwx 1 root root 18 Feb 22 03:34 libstdc+.so.5 - libstdc+.so.5.0.7-rwxr-xr-x 1 root root 825400 Apr 9 2007 libstdc+.so.5.0.7lrwxrwxrwx 1 root root 18 Feb 21 21:57 libstdc+.

4、so.6 - libstdc+.so.6.0.8-rwxr-xr-x 1 root root 969048 Sep 5 2009 libstdc+.so.6.0.8）注意这个compat-libstdc+-33-3.2.3-61.x86_64.rpm的包在SUSE LINUX 10的光盘里并没有，需要自己下载并安装，另外如果没有安装这个包，则在安装DB2时会报找不到libstdc+.so.5的告警。（对于 DB2 数据库服务器和客户机，libstdc+.so.5 是必需的。还有如果你在 RHEL 5 或 SLES 10 上使用 g+ 4.1 来编译 C+ 应用程序，那么将生成下列警告：libd

5、b2.so 所需要的 libstdc+.so.5 可能与 libstdc+.so.6 冲突）修改系统核心参数vi /etc/sysctl.confkernel.msgmni = 16384kernel.sem = 250 256000 32 4096#sysctl -p#vi /etc/security/limits.conf* soft nofile 10240二、开始进行安装和配置# tar -xvf DB2v9.7_linux64_server.tar# cd server# ./db2setup (或也可使用db2_install）安装到默认路径/opt/ibm/db2/V9.7下面，

6、安装类型选择ESE最后一步一步安装下去，直至完成。三、添加用户和组#groupadd -g 995 db2grp#useradd -u 1000 -d /home/db2inst1 -g db2grp -m db2inst1#groupadd -g 996 db2fgrp#useradd -u 1001 -d /home/db2fenc1 -g db2fgrp -m db2fenc1#groupadd -g 997 db2asgrp #（安装DB2 administration server的组）#useradd -u 1002 -d /home/dasusr1-g db2asgrp-m da

7、susr1 (建立安装DB2 administration server（DAS）的用户)#passwd db2inst1# passwd db2fenc1# passwd dasusr1四、在安装目录中找到许可证文件：db2ese.lic检查license的内容$db2licm -l执行如下安装授权许可#/opt/ibm/db2/V9.7/adm/db2licm -a /mnt/cdrom/db2/license/db2ese.lic五、创建db2 实例# cd /opt/ibm/db2/v9.7#ls -l#cd instance# ./db2icrt -s ese -u db2fenc1

8、 db2inst1 (建立数据库实例)# ./dascrt -u dasusr1 (建立das)#cd /opt/ibm/db2/V9.7/cfg# ./db2ln （建立DB2文件的链接）六、设定服务端口,配置数据库实例通信以root用户vi /etc/services新增如下行DB2_db2inst1 60000/tcpDB2_db2inst1_1 60001/tcpDB2_db2inst1_2 60002/tcpDB2_db2inst1_END 60003/tcpDB2c_db2inst1 60005/tcp保存退出七、启动实例#su - db2inst1$db2set DB2RSHCM

9、D=/usr/bin/ssh这里需要先设置DB2RSHCMD这个变量，再设置DB2COMM等变量$db2set DB2COMM=tcpip (设置DB2的通信方式为tcpip)$db2 update dbm cfg using SVCENAME db2c_db2inst1(设置dbm参数SVCENAME为db2c_db2inst1)$db2set DB2AUTOSTART=YES(设置数据库自动启动)然后通过DB2START命令启动当前实例$ db2start02/22/2013 01:40:54 0 0 SQL1063N DB2START processing was successful.

10、SQL1063N DB2START processing was successful.（创建数据库之前需要指定数据库存放目录并配置相应的权限 #mkdir -p /db2data/database # chown -R db2inst1:db2grp /db2data/database）创建数据库$db2 CREATE DATABASE testdb on /db2data/database USING CODESET GBK TERRITORY CN$db2 connect to testdb注意在连接数据库时，如果报下述错误SQL0332N Character conversion fr

11、om the source code page 1386 to the targetcode page 819 is not supported. SQLSTATE=57017上边的错误则说明server端的字符集是1386的，而客户端的字符集是819（cn）的，db2不能server端1386的字符集转化为client端819的字符集。解决这个问题的思想是将client端的字符集改为与server端兼容的那么需要设置这个参数db2inst1linux-uu1q: db2set db2codepage=1386 然后退出当前db2inst1用户db2inst1linux-uu1q: exitl

12、ogout再切换回db2inst1用户，参数生效后就可以正常登录进来了。linux-uu1q:/ # su - db2inst1db2inst1linux-uu1q: db2 connect to testdbDatabase Connection Information Database server = DB2/LINUXX8664 9.7.6 SQL authorization ID = DB2INST1 Local database alias = TESTDB为方便客户端连接数据库，还需设置$ db2 update dbm cfg using svcename 60005 (如果ne

13、tstat还看不到这个端口，需重启数据库)$ db2 catalog tcpip node node_8 remote 192.168.0.8 server 60005(编目节点)$ db2 catalog db testdb as testdb1 at node node_8（编目数据库在节点上）（Db2 connect to dbname user username using passwd# 数据库链接 Db2 uncatalog db dbname;# 反编目数据库 Db2 uncatalog node nodename #反编目节点）创建节点组首先可以查询一下目前数据库环境中的par

14、tition groups有哪些，使用下面命令：$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 (0) 创建缓冲池$db2

15、connect to testdb$db2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE 2000$db2 CREATE BUFFERPOOL bp32k ALL DBPARTITIONNUMS SIZE 983040 PAGESIZE 32K创建表空间$db2 CREATE LARGE TABLESPACE tbs32kdpgods IN DATABASE PARTITION GROUP dpgods pagesize 32k MANAGED BY DATABASE USING (file /db2data/dpgtbs32k/dpgtbs32k.data 500M)

16、 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 PARTI

17、TION 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

18、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

19、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

20、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)autoresi

21、ze yes bufferpool bp32k;CREATE LARGE TABLESPACE tbs32kedw IN DATABASE PARTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASEUSING (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

22、/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 DATABASEUSING (file /db2data/ddw/tbs32k/

23、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 L

24、ARGE TABLESPACE idx32kods IN DATABASE PARTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASEUSING (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

25、 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 DATABASEUSING (file /db2data/edw/idx32k/idx32k_0.data 100M) ON DBPARTITIONNU

26、M(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 PA

27、RTITION GROUP IBMDEFAULTGROUP pagesize 32k MANAGED BY DATABASEUSING (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/d

28、dw/idx32k/idx32k_3.data 100M) ON DBPARTITIONNUM(3)autoresize yes bufferpool bp32k;CREATE SYSTEM TEMPORARY TABLESPACE tmp32kPAGESIZE 32kMANAGED BY SYSTEMUSING (/db2data/database/db2inst1/NODE0000/SQL00001/systmptbs_32k) ON DBPARTITIONNUM (0)USING (/db2data/database/db2inst1/NODE0001/SQL00001/systmptb

29、s_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 usrtmp32kPAGESIZE 32kMANAGED BY DATABASEUSING

30、(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;九、配置数据库和实例的相关参数：#su - db2inst1$db2_all db2 update db cfg for testdb using LOGPRIMARY 16$db2