前言：年底了，公司需要部署一套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 image 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 image database, and the alias name "RESDB" of the existing database
does not match the alias name "RESLT4" of the backup image, and the database
name "RESDB" of the existing database does not match the database name
"RESLT4" of the backup image. 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
数据源连接测试
