DB2安裝部署以及應(yīng)用部署配置操作

前言：年底了，公司需要部署一套DB2環(huán)境+應(yīng)用系統(tǒng)來對客戶進(jìn)行相關(guān)的功能模塊演示操作，之前對DB2接觸不多，利用這次機(jī)會，開啟對DB2的探索學(xué)習(xí)之路。

DB2 數(shù)據(jù)庫部署

硬件環(huán)境信息：

機(jī)器IP：10.162.64.78

OS版本：RHEL 6.6

內(nèi)存：256G

1.文件系統(tǒng)劃分

查看已經(jīng)創(chuàng)建的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

創(chuàng)建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

創(chuàng)建目錄并掛載

# 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.修改系統(tǒng)內(nèi)核參數(shù)

# vi /etc/sysctl.conf

kernel.shmmax = 137438953472    --設(shè)置為物理內(nèi)存的一半或稍大

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.創(chuàng)建用戶

# 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.安裝數(shù)據(jù)庫

# 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.創(chuàng)建實(shí)例

修改/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

啟動實(shí)例：  --切換到db2inst1用戶

配置環(huán)境變量  --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.創(chuàng)建DB

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

DB20000I  The CREATE DATABASE command completed successfully.

批注：創(chuàng)建數(shù)據(jù)庫時若為指定路徑，將使用默認(rèn)路徑，數(shù)據(jù)庫用戶的家目錄，如下查看：

$ db2 get dbm cfg | grep -i DFTDBPATH

 Default database path                       (DFTDBPATH) = /home/db2inst1

9.參數(shù)調(diào)整

$ db2 connect to resdb

$ db2iauto -on db2inst1   --設(shè)置數(shù)據(jù)庫實(shí)例開機(jī)自啟動

9.1參數(shù)調(diào)整

$ 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.

批注：需要重啟數(shù)據(jù)庫生效

9.2日志調(diào)整

--日志位置和大小調(diào)整

$ 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其他參數(shù)調(diào)整

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

參數(shù)調(diào)整完畢，數(shù)據(jù)庫重啟：

$ 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.

此時需要調(diào)整/etc/security/limits.conf配置文件

db2list1  soft nofile 65536

db2inst1  hard nofile 65536

db2inst1  soft nproc  65536

db2inst1  hard nproc  65536

重新連接數(shù)據(jù)庫：

$ db2 connect to resdb

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

database is in BACKUP PENDING state.  SQLSTATE=57019

批注：若修改數(shù)據(jù)里L(fēng)OGRETAIN參數(shù)，從循環(huán)日志模式改為歸檔模式，則會導(dǎo)致數(shù)據(jù)庫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.

此時無法連接，需要對數(shù)據(jù)庫做離線全備，使?fàn)顟B(tài)恢復(fù)正常，以便能夠訪問。

$ 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'

重啟數(shù)據(jù)庫

$ db2stop force

$ db2start

11.創(chuàng)建數(shù)據(jù)庫對象

$ 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.數(shù)據(jù)庫恢復(fù)

創(chuàng)建所需目錄：

$ mkdir -p /oradata4/db2

首先對備份文件進(jìn)行分析：

$ 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;

批注：遠(yuǎn)端備份的數(shù)據(jù)庫名為RESLT4，遠(yuǎn)端備份路徑為/oradata4/db2,本地數(shù)據(jù)庫名為RESDB，本地需要先創(chuàng)建/oradata4/db2路徑。

執(zhí)行恢復(fù)操作：

$ 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.

應(yīng)用環(huán)境部署

13.WAS應(yīng)用部署連接DB2數(shù)據(jù)庫

之前部署的WAS環(huán)境連接的都是Oracle庫，連接DB2的庫數(shù)據(jù)源配置還是第一次，找了些資料，將與配置Oracle不同的地方記錄如下：

13.1配置DB2環(huán)境變量   --本次WAS部署采用的是單機(jī)環(huán)境

控制臺界面中選擇Environment-->WebSphere-->variables-->作用域（選擇server1）

點(diǎn)擊"new"創(chuàng)建DB2UNIVERSAL_JDBC_DRIVER_PATH變量值為："/was/DB2lib"

批注：部署was應(yīng)用的主機(jī)創(chuàng)建該目錄，并將所需jar包傳入到該路徑下，jar包位置/opt/ibm/db2/V10.5/java

13.2配置JDBC提供程序：

控制臺界面中選擇Resources-->JDBC-->JDBC providers-->選擇"server1"，點(diǎn)擊"New"

名稱：DB2 Universal JDBC Driver Provider

數(shù)據(jù)庫類型選擇"DB2"，提供程序類型選擇"DB2 Universal JDBC Driver Provider"，實(shí)施類型選擇"Connection pool data source"，點(diǎn)擊“Next”

class path設(shè)置：

/was/DB2lib/db2jcc.jar

/was/DB2lib/db2jcc_license_cu.jar

13.3創(chuàng)建J2C用戶

db2inst1/db2inst1

13.4.數(shù)據(jù)源配置

jdbcResTxDataSource          jdbc/ResTxDataSource  

jdbcoracleResTxDataSource      jdbc/oracle/ResTxDataSource

數(shù)據(jù)源連接測試

14.應(yīng)用部署，測試驗(yàn)證