suse linux中导入数据库,suse linux上安装DB2 数据库手记

四、在安装目录中找到许可证文件：db2ese.lic 检查license的内容

$db2licm -l

执行如下安装授权许可

#/opt/ibm/db2/V9.7/adm/db2licm  -a  /mnt/cdrom/db2/license/db2aese.lic

注：默认是三个月试用trial，授权许可后永久permanent

五、创建db2实例

# cd /opt/ibm/db2/v9.7

# ls -l

#cd instance

# ./db2icrt -s ese -u db2fenc1 db2inst1 (建立数据库实例)

# ./dascrt -u dasusr1 (建立das)

注：以上在安装过程中已经设置完成了，只需下面一步

#cd /opt/ibm/db2/V9.7/cfg

# ./db2ln (建立DB2文件的链接)

六、设定服务端口,配置数据库实例通信

以root用户

vi /etc/services

新增如下行

DB2_db2inst1    60000/tcp

DB2_db2inst1_1  60001/tcp

DB2_db2inst1_2  60002/tcp

DB2_db2inst1_END  60003/tcp

db2c_db2inst1   50001/tcp

保存退出

(这部分安装后可自动生成,也无需配置)

七、启动实例#su - db2inst1

$db2set DB2RSHCMD=/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命令启动当前实例

$ db2start

02/22/2013 01:40:54     0   0   SQL1063N  DB2START processing was successful.

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手工建库

$db2 "create database testdb AUTOMATIC STORAGE YES ON /home/db2inst1  DBPATH ON /home/db2inst1 USING CODESET UTF-8 TERRITORY CN  COLLATE USING SYSTEM")

$db2 connect to testdb

注意在连接数据库时，如果报下述错误

SQL0332N  Character conversion from the source code page "1386" to the target

code page "819" is not supported.  SQLSTATE=57017

上边的错误则说明server端的字符集是1386的，而客户端的字符集是819(cn)的，db2不能server端1386的字符集转化为client端819的字符集。解决这个问题的思想是将client端的字符集改为与server端兼容的

那么需要设置这个参数

db2inst1@linux-uu1q:~> db2set db2codepage=1386

然后退出当前db2inst1用户

db2inst1@linux-uu1q:~> exit

logout

再切换回db2inst1用户，参数生效后就可以正常登录进来了。

linux-uu1q:/ # su - db2inst1

db2inst1@linux-uu1q:~> db2 connect to testdb

Database Connection Information

Database server        = DB2/LINUXX8664 9.7.6

SQL authorization ID   = DB2INST1

Local database alias   = TESTDB

为方便客户端连接数据库，还需设置

$ db2 update dbm cfg using svcename 60005  (如果netstat还看不到这个端口，需重启数据库)

$ 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 #反编目节点)

创建节点组

首先可以查询一下目前数据库环境中的partition 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 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) 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;

九、配置数据库和实例的相关参数：

#su - db2inst1

$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 ASLHEAPSZ 10240

$db2 update dbm cfg using MON_HEAP_SZ 1024

$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

$db2 force applications all

$db2 terminate

$db2stop force

$db2start

(备注：ctrl+z的相反动作是ctrl+y,还有的软件是ctrl+alt+z)

补充：

DB2数据库补丁包的安装：

1．以用户root登录linux系统

2．得到DB2数据库补丁包文件(db2v9.7fp3_25384_linuxx64_server.tar.gz)；

3．解压该文件 tar  -zvxf  文件名

4．停掉所有的实例和DAS，命令如下：

db2admin  stop   #以用户dasusr1身份执行

db2stop          #以用户db2inst1身份执行

5．到解压产生的补丁包目录下开始安装补丁：

执行 #./installFixPack  -y

6．升级数据库(#以用户root身份执行)

/opt/IBM/db2/V9.7/instance/db2iupdt db2inst1 #升级实例

/opt/IBM/db2/V9.7//instance/dasupdt dasusr1  #升级DB2管理服务

/opt/IBM/db2/V9.7/instance/ db2iupt -a server -u db2inst1数据库名

7．安装完毕后，启动实例和DAS：

db2admin  start   #以用户dasusr1身份执行

db2start          #以用户db2inst1身份执行

8．完成安装