DB2安装部署以及应用部署配置操作

前言：年底了，公司需要部署一套DB2环境+应用系统来对客户进行相关的功能模块演示操作，之前对DB2接触不多，利用这次机会，开启对DB2的探索学习之路。

DB2 数据库部署

硬件环境信息：

机器IP：10.162.64.78

OS版本：RHEL 6.6

内存：256G

1.文件系统划分

查看已经创建的PV，VG

# pvs

  PV         VG         Fmt  Attr PSize   PFree 

  /dev/sda3  VolGroup00 lvm2 a--  557.38g 379.38g

# vgs

  VG         #PV #LV #SN Attr   VSize   VFree 

  VolGroup00   1   2   0 wz--n- 557.38g 379.38g

创建LV

# lvcreate -L 20G -n lvdb2log VolGroup00

# lvcreate -L 30G -n lvdb2arch VolGroup00

# lvcreate -L 150G -n lvdb2data VolGroup00

格式化LV

# mkfs.ext4 /dev/VolGroup00/lvdb2log

# mkfs.ext4 /dev/VolGroup00/lvdb2arch

# mkfs.ext4 /dev/VolGroup00/lvdb2data

创建目录并挂载

# mkdir -p /oradata4

# mkdir -p /db2arch

# mkdir -p /db2log

# mount /dev/VolGroup00/lvdb2log /db2log

# mount /dev/VolGroup00/lvdb2arch /db2arch

# mount /dev/VolGroup00/lvdb2data /oradata4

# tune2fs -c 0 -i 0 /dev/VolGroup00/lvdb2log

# tune2fs -c 0 -i 0 /dev/VolGroup00/lvdb2arch

# tune2fs -c 0 -i 0 /dev/VolGroup00/lvdb2data

# echo "/dev/VolGroup00/lvdb2log /db2log                     ext4    defaults        0 0" >> /etc/fstab

# echo "/dev/VolGroup00/lvdb2arch /db2arch                   ext4    defaults        0 0" >> /etc/fstab

# echo "/dev/VolGroup00/lvdb2data /oradata4                   ext4    defaults        0 0" >> /etc/fstab

2.安装所需软件包

libstdc++.so.6

3.修改系统内核参数

# vi /etc/sysctl.conf

kernel.shmmax = 137438953472    --设置为物理内存的一半或稍大

kernel.shmall = 67108864

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=262144

net.core.rmem_max=262144

net.core.wmem_default=262144

net.core.wmem_max=262144

4.创建用户

# groupadd -g 1011 db2iadm1

# groupadd -g 1012 db2fadm1

# useradd -u 1016 -g db2iadm1 -d /home/db2inst1 -m  db2inst1

# useradd -u 1017 -g db2fadm1 -d  /home/db2fenc1 -m db2fenc1

# echo 'db2inst1' |passwd --stdin db2inst1

# echo 'db2fenc1' |passwd --stdin db2fenc1

# chown db2inst1:db2iadm1 /oradata4

# chown db2inst1:db2iadm1 /db2log

# chown db2inst1:db2iadm1 /db2arch

5.安装数据库

# tar zxvf v10.5fp7_linuxx64_universal_fixpack.tar.gz

# cd universal/

# ./db2_install

DBI1324W  Support of the db2_install command is deprecated.

Default directory for installation of products - /opt/ibm/db2/V10.5

***********************************************************

Install into default directory (/opt/ibm/db2/V10.5) ? [yes/no]

yes

Specify one of the following keywords to install DB2 products.

  SERVER

  CONSV

  EXP

Enter "help" to redisplay product names.

Enter "quit" to exit.

***********************************************************

SERVER

***********************************************************

Do you want to install the DB2 pureScale Feature? [yes/no]

no

DB2 installation is being initialized.

......

The execution completed successfully.

For more information see the DB2 installation log at

"/tmp/db2_install.log.25687".

6.查看并安装license

# find / -name db2licm

/opt/ibm/db2/V10.5/adm/db2licm

# /opt/ibm/db2/V10.5/adm/db2licm -l

Product name:                     "DB2 Enterprise Server Edition"

License type:                     "License not registered"

Expiry date:                      "License not registered"

Product identifier:               "db2ese"

Version information:              "10.5"

# ./db2licm -a /install/isrs.lic

Product name:                     "DB2 Enterprise Server Edition"

License type:                     "CPU Option"

Expiry date:                      "Permanent"

Product identifier:               "db2ese"

Version information:              "10.5"

7.创建实例

修改/etc/hosts如下配置：

127.0.0.1    localhost

10.162.64.78  OSS-ZYGL-294

# /opt/ibm/db2/V10.5/instance/db2icrt -a server -p 9999 -u db2fenc1 db2inst1

DBI1446I  The db2icrt command is running.

DB2 installation is being initialized.

......

The execution completed successfully.

For more information see the DB2 installation log at "/tmp/db2icrt.log.9284".

DBI1070I  Program db2icrt completed successfully.

# /opt/ibm/db2/V10.5/instance/db2ilist

db2inst1

启动实例：  --切换到db2inst1用户

配置环境变量  --root用户和db2inst1用户

# vi ~/.bash_profile

$ vi ~/.bash_profile

PATH=$PATH:$HOME/bin:/opt/ibm/db2/V10.5/bin

# source ~/.bash_profile

$ source ~/.bash_profile

$ db2start

01/23/2017 13:36:37     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

8.创建DB

$ db2 "create db RESDB on /oradata4 using codeset UTF-8 territory cn"

DB20000I  The CREATE DATABASE command completed successfully.

批注：创建数据库时若为指定路径，将使用默认路径，数据库用户的家目录，如下查看：

$ db2 get dbm cfg | grep -i DFTDBPATH

 Default database path                       (DFTDBPATH) = /home/db2inst1

9.参数调整

$ db2 connect to resdb

$ db2iauto -on db2inst1   --设置数据库实例开机自启动

9.1参数调整

$ db2 update dbm cfg using SVCENAME 9999 DFT_MON_BUFPOOL on DFT_MON_UOW on NUM_POOLAGENTS 4100

DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.

SQL1362W  One or more of the parameters submitted for immediate modification

were not changed dynamically. Client changes will not be effective until the

next time the application is started or the TERMINATE command has been issued.

Server changes will not be effective until the next DB2START command.

批注：需要重启数据库生效

9.2日志调整

--日志位置和大小调整

$ db2 update db cfg for RESDB using NEWLOGPATH /db2log

$ db2 update db cfg for RESDB using LOGARCHMETH1 disk:/db2arch

$ db2 update db cfg for RESDB using LOGFILSIZ 25600 LOGPRIMARY 13 LOGSECOND 12  CATALOGCACHE_SZ 2048 LOGBUFSZ 4096 UTIL_HEAP_SZ 10240 DB_MEM_THRESH 100 DLCHKTIME 5000 LOCKTIMEOUT 15

9.3其他参数调整

db2set DB2_PARALLEL_IO=*

db2set DB2_SKIPINSERTED=YES

db2set DB2_USE_ALTERNATE_PAGE_CLEANING=YES

db2set DB2_EVALUNCOMMITTED=YES

db2set DB2_ANTIJOIN=EXTEND

db2set DB2_SKIPDELETED=YES

db2set DB2CODEPAGE=1208

参数调整完毕，数据库重启：

$ db2stop

01/23/2017 13:48:24     0   0   SQL1025N  The database manager was not stopped because databases are still active.

SQL1025N  The database manager was not stopped because databases are still active.

$ db2 force application all

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

$ db2stop

01/23/2017 13:49:39     0   0   SQL1064N  DB2STOP processing was successful.

SQL1064N  DB2STOP processing was successful.

$ db2start

01/23/2017 13:50:13     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

此时需要调整/etc/security/limits.conf配置文件

db2list1  soft nofile 65536

db2inst1  hard nofile 65536

db2inst1  soft nproc  65536

db2inst1  hard nproc  65536

重新连接数据库：

$ db2 connect to resdb

SQL1116N  A connection to or activation of database "RESDB" failed because the

database is in BACKUP PENDING state.  SQLSTATE=57019

批注：若修改数据里LOGRETAIN参数，从循环日志模式改为归档模式，则会导致数据库BACKUP PENDING，如下处理

$ db2 update db cfg for resdb using LOGRETAIN RECOVERY   

SQL1597N  Configuring the DB2 environment failed because the specified DB2

configuration parameter is discontinued.

此时无法连接，需要对数据库做离线全备，使状态恢复正常，以便能够访问。

$ db2 backup db resdb to /dev/null

Backup successful. The timestamp for this backup p_w_picpath is : 20170123135258

$ db2 connect to resdb

10.打开ORACLE兼容模式

$ db2set DB2_COMPATIBILITY_VECTOR=ORA

$ db2set -all |grep 'DB2_COMPATIBILITY_VECTOR'

重启数据库

$ db2stop force

$ db2start

11.创建数据库对象

$ db2 "create bufferpool BP_DAT_32K size 16000  pagesize  32k"

$ db2 "create bufferpool BP_IDX_32K size 16000  pagesize  32k"

$ db2 "create bufferpool BP_TMP_32K size 16000  pagesize  32k"

$ db2 "CREATE LARGE TABLESPACE TBS_DAT_32k PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE  EXTENTSIZE 4 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_DAT_32K AUTORESIZE YES INCREASESIZE 1G  NO FILE SYSTEM CACHING"

$ db2 "CREATE LARGE TABLESPACE TBS_IDX_32k PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE  EXTENTSIZE 4 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_IDX_32K AUTORESIZE YES  INCREASESIZE 1G  NO FILE SYSTEM CACHING"

$ db2 "CREATE system TEMPORARY TABLESPACE TBS_SYSTMP_32K PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE  BUFFERPOOL BP_TMP_32K  NO FILE SYSTEM CACHING"

12.数据库恢复

创建所需目录：

$ mkdir -p /oradata4/db2

首先对备份文件进行分析：

$ ls -al | grep -i RESLT4

-rw-r--r-- 1 db2inst1 db2iadm1 13223706624 Jan 19 12:42 RESLT4.0.db2inst1.DBPART000.20170118202238.001

生成restore.clp文件

$ db2 restore db RESLT4 taken at 20170118202238 redirect generate script restore.clp

DB20000I  The RESTORE DATABASE command completed successfully.

修改restore.clp文件：

RESTORE DATABASE RESLT4

-- USER  <username>

-- USING '<password>'

FROM '/oradata4/db2'

TAKEN AT 20170118202238

-- ON '/oradata4/db2'

-- DBPATH ON '<target-directory>'

INTO RESDB

-- NEWLOGPATH '/oradata4/db2/db2inst1/NODE0000/SQL00004/LOGSTREAM0000/'

-- WITH <num-buff> BUFFERS

-- BUFFER <buffer-size>

-- REPLACE HISTORY FILE

-- REPLACE EXISTING

REDIRECT

-- PARALLELISM <n>

WITHOUT ROLLING FORWARD

-- WITHOUT PROMPTING

;

-- SET STOGROUP PATHS FOR IBMSTOGROUP

-- ON '/oradata4/db2'

-- ;

RESTORE DATABASE RESLT4 CONTINUE;

批注：远端备份的数据库名为RESLT4，远端备份路径为/oradata4/db2,本地数据库名为RESDB，本地需要先创建/oradata4/db2路径。

执行恢复操作：

$ db2 -tvf restore.clp

UPDATE COMMAND OPTIONS USING S ON Z ON RESLT4_NODE0000.out V ON

DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

SET CLIENT ATTACH_MEMBER  0

DB20000I  The SET CLIENT command completed successfully.

SET CLIENT CONNECT_MEMBER 0

DB20000I  The SET CLIENT command completed successfully.

RESTORE DATABASE RESLT4 FROM '/oradata4/db2' TAKEN AT 20170118202238 INTO RESDB REDIRECT WITHOUT ROLLING FORWARD

SQL2529W  Warning!  Restoring to an existing database that is different from

the backup p_w_picpath database, and the alias name "RESDB" of the existing database

does not match the alias name "RESLT4" of the backup p_w_picpath, and the database

name "RESDB" of the existing database does not match the database name

"RESLT4" of the backup p_w_picpath. The target database will be overwritten by the

backup version. The Roll-forward recovery logs associated with the target

database will be deleted.

Do you want to continue ? (y/n)y

SQL1277W  A redirected restore operation is being performed. During a table

space restore, only table spaces being restored can have their paths

reconfigured. During a database restore, storage group storage paths and DMS

table space containers can be reconfigured.

DB20000I  The RESTORE DATABASE command completed successfully.

RESTORE DATABASE RESLT4 CONTINUE

DB20000I  The RESTORE DATABASE command completed successfully.

应用环境部署

13.WAS应用部署连接DB2数据库

之前部署的WAS环境连接的都是Oracle库，连接DB2的库数据源配置还是第一次，找了些资料，将与配置Oracle不同的地方记录如下：

13.1配置DB2环境变量   --本次WAS部署采用的是单机环境

控制台界面中选择Environment-->WebSphere-->variables-->作用域（选择server1）

点击"new"创建DB2UNIVERSAL_JDBC_DRIVER_PATH变量值为："/was/DB2lib"

批注：部署was应用的主机创建该目录，并将所需jar包传入到该路径下，jar包位置/opt/ibm/db2/V10.5/java

13.2配置JDBC提供程序：

控制台界面中选择Resources-->JDBC-->JDBC providers-->选择"server1"，点击"New"

名称：DB2 Universal JDBC Driver Provider

数据库类型选择"DB2"，提供程序类型选择"DB2 Universal JDBC Driver Provider"，实施类型选择"Connection pool data source"，点击“Next”

class path设置：

/was/DB2lib/db2jcc.jar

/was/DB2lib/db2jcc_license_cu.jar

13.3创建J2C用户

db2inst1/db2inst1

13.4.数据源配置

jdbcResTxDataSource          jdbc/ResTxDataSource  

jdbcoracleResTxDataSource      jdbc/oracle/ResTxDataSource

数据源连接测试

14.应用部署，测试验证