DB2数据库测试环境搭建-今日头条

概述

需要的用户：db2inst1,db2fenc1,whcrm,cognos,use

需要的实例：db2inst1

需要的库：crmdb(gbk),cognos(utf-8),usedb(gbk)

除系统表空间外，均使用自动存储表空间。

三个库放置在一个实例下。

建库位置：/db/crmdb /db/cognos /db/usedb

环境准备

基本要求

1、 CPU 8

2、内存 24

3、切记：主机名和数据库名不能一致

4、 /data 400G

5、 /db 5G 建库

6、 /backup 100G 备用空间

7、 /home/use/file 45G

8、日志目录/db2log：30G

9、 /db2tmp 20G (临时表空间)

组创建

groupadd db2iadm1

groupadd db2fadm1

用户创建

useradd -d "/home/db2inst1" -g db2iadm1 -m -s "/bin/bash" db2inst1

useradd -d "/home/db2fenc1" -g db2fadm1 -m -s "/bin/bash" db2fenc1

useradd -d "/home/whcrm" -g db2iadm1 -m -s "/bin/bash" whcrm

useradd -d "/home/use" -m -s "/bin/bash" use

useradd -d "/home/cognos" -m -s "/bin/bash" cognos

passwd db2inst1

Passw0rdD

passwd db2fenc1

Passw0rdF

passwd whcrm

Passw0rdW

passwd use

Passw0rdU

passwd cognos

Passw0rdC

安装包解压

root用户根目录

/home/v9.7fp9_linuxx64_server.tar.gz

复制到/crmdb目录

tar -xzvf v9.7fp9_linuxx64_server.tar.gz

tar -xzvf v9.7fp9_linuxx64_nlpack.tar.gz

cp -r ./nlpack/* ./server/

安装数据库

安装及创建实例

安装：

cd /db/server

./db2_install

使用默认安装位置/opt/ibm/db2/V9.7

安装类型选ESE

创建实例：

cd /opt/ibm/db2/V9.7/instance

./db2icrt -a SERVER -s ese -u db2fenc1 db2inst1

确认服务端口：

view /etc/services

DB2_db2inst1 60000/tcp

DB2_db2inst1_1 60001/tcp

DB2_db2inst1_2 60002/tcp

DB2_db2inst1_END 60003/tcp

用户配置

su - db2inst1

db2set DB2COMM=tcpip

db2set DB2_PARALLEL_IO=*

db2 update dbm cfg using SVCENAME DB2_db2inst1

cognos whcrm use用户的.profile文件增加以下内容

if [ -f /home/db2inst1/sqllib/db2profile ]; then

. /home/db2inst1/sqllib/db2profile

创建数据库

路径配置

root用户

mkdir /db2tmp/crmdb

mkdir /db2log/crmdb

mkdir /db/crmdb

mkdir /data/crmdb

chmod –R 755 /db2tmp/crmdb

chown -R db2inst1:db2iadm1 /db2tmp/crmdb

chmod –R 755 /db2log/crmdb

chown -R db2inst1:db2iadm1 /db2log/crmdb

chmod –R 755 /db/crmdb

chown -R db2inst1:db2iadm1 /db/crmdb

chmod –R 755 /data/crmdb

chown -R db2inst1:db2iadm1 /data/crmdb

建库

db2inst1实例用户

db2start

db2 "create db crmdb on /db/crmdb using codeset GBK territory CN pagesize 32 K catalog tablespace managed by system using ( '/db/crmdb/tbs/syscat/syscatspace' )"

建缓冲池

db2 activate db crmdb

db2 connect to crmdb

db2 -v "alter bufferpool IBMDEFAULTBP IMMEDIATE SIZE 1000 AUTOMATIC"

db2 -v "CREATE BUFFERPOOL BP_T_32K IMMEDIATE SIZE 65536 AUTOMATIC PAGESIZE 32K "

db2 -v "CREATE BUFFERPOOL BP_I_32K IMMEDIATE SIZE 65536 AUTOMATIC PAGESIZE 32K "

db2 -v "CREATE BUFFERPOOL BP_S_32K IMMEDIATE SIZE 65536 AUTOMATIC PAGESIZE 32K "

db2 -v "CREATE BUFFERPOOL BP_L_32K IMMEDIATE SIZE 163840 AUTOMATIC PAGESIZE 32K NUMBLOCKPAGES 24576 BLOCKSIZE 32"

建表空间

db2 -v " rename tablespace TEMPSPACE1 to S_TMP_32K"

db2 -v " drop tablespace USERSPACE1 to U_TMP_32K"

db2 -v " create SYSTEM temporary tablespace TEMPSPACE1 pagesize 32k managed by system using (' /db2tmp/crmdb/tbs/temptbs/tempspace1_1' ) bufferpool BP_T_32K NO FILE SYSTEM CACHING"

db2 -v " create large tablespace USERSPACE1 pagesize 32k managed by database using ( file '/data/crmdb/tbs/datatbs/userspace1_1' 10240m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool BP_S_32K "

db2 -v " drop tablespace S_TMP_32K "

db2 -v " drop tablespace U_TMP_32K "

---------------

db2 -v " create large tablespace ODM_DATA_TBS pagesize 32k managed by database using ( file '/data/crmdb/tbs/datatbs/ods_data_tbs_1' 40960m, file '/data/crmdb/tbs/datatbs/ods_data_tbs_2' 40960m, file '/data/crmdb/tbs/datatbs/ods_data_tbs_3' 40960m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool BP_L_32K "

db2 -v " create large tablespace ODM_INDEX_TBS pagesize 32k managed by database using ( file '/data/crmdb/tbs/indextbs/ods_index_tbs_1' 5120m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool BP_I_32K "

----------------

db2 -v " create large tablespace FDM_DATA_TBS pagesize 32k managed by database using ( file '/data/crmdb/tbs/datatbs/fds_data_tbs_1' 40960m, file '/data/crmdb/tbs/datatbs/fds_data_tbs_2' 40960m, file '/data/crmdb/tbs/datatbs/fds_data_tbs_3' 40960m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool BP_L_32K "

db2 -v " create large tablespace FDM_INDEX_TBS pagesize 32k managed by database using ( file '/data/crmdb/tbs/indextbs/fds_index_tbs_1' 20480m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool BP_I_32K "

-----------------

db2 -v " create large tablespace MDM_DATA_TBS pagesize 32k managed by database using ( file '/data/crmdb/tbs/datatbs/mds_data_tbs_1' 40960m, file '/data/crmdb/tbs/datatbs/mds_data_tbs_2' 40960m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool BP_S_32K "

db2 -v " create large tablespace MDM_INDEX_TBS pagesize 32k managed by database using ( file '/data/crmdb/tbs/indextbs/mds_index_tbs_1' 10240m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool BP_I_32K "

参数配置

系统级

db2set DB2_HASH_JOIN=YES

db2set DB2AUTOSTART=YES

db2set DB2CODEPAGE=1386

实例级

必需参数

db2 update dbm cfg using FEDERATED YES

优化参数

db2 update dbm cfg using INTRA_PARALLEL YES

db2 update dbm cfg using MAX_QUERYDEGREE 16

数据库级

db2inst1用户

db2 connect to crmdb

db2 grant dbadm on database to whcrm

db2 update db cfg for crmdb using LOGFILSIZ 262144

db2 update db cfg for crmdb using LOGBUFSZ 12800

db2 update db cfg for crmdb using LOGPRIMARY 20

db2 update db cfg for crmdb using LOGSECOND 5

db2 update db cfg for crmdb using DFT_DEGREE 8

db2 update db cfg for crmdb using SHEAPTHRES_SHR 1310720 immediate

db2 update db cfg for crmdb using SORTHEAP 65536 immediate

db2 update db cfg for crmdb using NEWLOGPATH /db2log/crmdb

其他库

root用户

mkdir /db/cognos

mkdir /data/cognos

mkdir /db/usedb

mkdir /data/usedb

chmod -R 777 /data/usedb

chown -R use:users /data/usedb

chmod -R 777 /data/cognos

chown -R use:users /data/cognos

USEDB

db2inst1 用户

db2 "create db usedb on /db/usedb using codeset GBK territory CN pagesize 32 K catalog tablespace managed by system using ( '/db/usedb/tbs/syscat/syscatspace' )"

db2 connect to usedb

db2 drop tablespace USERSPACE1

db2 -v " create large tablespace USERSPACE1 pagesize 32k managed by database using ( file '/data/usedb/tbs/datatbs/userpace1_1' 850m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool IBMDEFAULTBP "

db2 connect to usedb

db2 grant dbadm on database to user use

COGNOS

db2inst1用户

db2 "create db cognos on /db/cognos using codeset utf-8 territory US pagesize 32 K catalog tablespace managed by system using ( '/db/cognos/tbs/syscat/syscatspace' )"

db2 "CHANGE DATABASE COGNOS COMMENT WITH 'IBM COGNOS Content Store'"

db2 CONNECT TO COGNOS

db2 UPDATE DATABASE CONFIGURATION USING APPLHEAPSZ 1024 DEFERRED

db2 UPDATE DATABASE CONFIGURATION USING LOCKTIMEOUT 240 DEFERRED

db2 CONNECT RESET

db2 CONNECT TO COGNOS

db2 CREATE BUFFERPOOL COGNOS_08KBP IMMEDIATE SIZE 1000 PAGESIZE 8K

db2 CREATE BUFFERPOOL COGNOS_32KBP IMMEDIATE SIZE 1000 PAGESIZE 32K

db2 CONNECT RESET

db2 CONNECT TO COGNOS

db2 CREATE SYSTEM TEMPORARY TABLESPACE TSN_SYS_COGNOS IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 32K BUFFERPOOL COGNOS_32KBP

db2 CREATE USER TEMPORARY TABLESPACE TSN_USR_COGNOS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K BUFFERPOOL COGNOS_08KBP

db2 CREATE REGULAR TABLESPACE TSN_REG_COGNOS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K BUFFERPOOL COGNOS_08KBP

db2 CONNECT RESET

db2 CONNECT TO COGNOS

db2 CREATE SCHEMA COGNOS AUTHORIZATION COGNOS

db2 "COMMENT ON SCHEMA COGNOS IS 'IBM COGNOS Content Store'"

db2 GRANT CREATETAB,BINDADD,CONNECT,IMPLICIT_SCHEMA ON DATABASE TO USER COGNOS

db2 GRANT CREATEIN,DROPIN,ALTERIN ON SCHEMA COGNOS TO USER COGNOS WITH GRANT OPTION

db2 GRANT USE OF TABLESPACE TSN_USR_COGNOS TO COGNOS

db2 GRANT USE OF TABLESPACE TSN_REG_COGNOS TO COGNOS

db2 CONNECT RESET

db2 connect to cognos

db2 drop tablespace USERSPACE1

db2 -v " create large tablespace USERSPACE1 pagesize 32k managed by database using ( file '/data/cognos/tbs/datatbs/userpace1_1' 2048m ) EXTENTSIZE 32 PREFETCHSIZE 32 bufferpool IBMDEFAULTBP "

db2 connect to cognos

db2 grant dbadm on database to user cognos

各种组件导入

初始化所需文件

1：测试环境的

cd ./2gen

所有自定义类型 types.sql

db2 -tvf types.sql > ./log/type.log

所有表 tables_whcrm.sql

db2 -tvf tables_whcrm.sql > ./log/tables_whcrm.log

所有视图 views.sql

db2 -tvf views.sql > ./log/views.log

所有SEQUENCE sequences.sql

db2 -tvf sequences.sql > ./log/sequences.log

所有触发器 triggers.sql

db2 -tvf triggers.sql > ./log/triggers.log

alias alias.sql

db2 -tvf alias.sql > ./log/alias.log

use相关 sth_idnotknow.sql

db2 connect to usedb user use using Passw0rdU

db2 -tvf usedb_tb.sql

db2 terminate

db2 connect to crmdb user whcrm using Passw0rdW

db2 -tvf sth_idnotknow.sql > ./log/sth_idnotknow.log

新增的FUNCTION functions_new.sql

db2 -tvf functions_new.sql > ./log/functions_new.log

2：生产环境

cd ../1gen

生产环境FUNCTION functions_for_whcrm_sc.sql

db2 -tvf functions_for_whcrm_sc.sql > ./log/functions_for_whcrm_sc.log

所有存储过程 procs_for_whcrm 文件夹

sh cpl.sh

3：测试环境

cd ../2gen

新增存储过程 procs_new 文件夹

sh cpl.sh

4：测试环境

USE的FUNCTION functions_for_use.sql 暂缓执行（不需执行）

数据装载

处理

cd ./data_init

sh movefix.sh

装载语句

cd ./data

db2move crmdb load -lo REPLACE -u whcrm -p Passw0rdW > /data/init/crmdb/data_init/log/load_log.log

测试环境前台表导出语句

db2move crmdb export -aw -tf TableData.txt -u whcrm -p whcrm

文件TableData.ls为需要导出的表清单。

导入语句

db2move crmdb load -lo REPLACE -u whcrm -p Passw0rdW > /data/init/crmdb/data_init/log/load_crmdata_log.log

DB2数据库测试环境搭建

头条热榜

精彩视频